I had a requirement where I had a related table (or dimension table) in which was used as a slicer. But when it was changed to a slicer it was showing all the values in the related table, even if there was no data.

So below I show and explain how to overcome this and show how to filter a slicer dynamically.

Example of current slicer

Below I will show what the default slicer looks like when you create it in your Power BI report.

As you can see above on the left hand side is my table, in which I have the Value and then the Fiscal Year.

And currently my Fiscal Year Slicer has Fiscal Years from 2011 – 2031.

And in my dataset we have data until 2024

How to create a slicer with a calculated table

Below I am going to show how to create a calculated table, which will only have the values where there is data.

  • The first thing you have to ensure before you create your calculated table is that you have created your relationships between your tables.
    • NOTE: This is very important for the calculated table to work below.
    • As with my example you can see below that there is a relationship between the Budget data without new Year and the Date table.
  • In your Power BI Desktop click on the Modeling tab and then select New Table
  • I then put in the following DAX Syntax below, with the explanation afterwards of how it works.

    Fiscal Year – Slicer =

    CALCULATETABLE (

    ADDCOLUMNS (

    SUMMARIZE ( ‘Budget Data without New Year’, ‘Date'[Fiscal Year] ,‘Date'[Calendar Date]),

    “Not Used”, 1

    )

    )

    • As you can see above I started with my CALCULATETABLE, which creates the calculated table.
    • Next is where I have the ADDCOLUMNS, which will be which columns I want to add.
    • And then finally the SUMMARIZE, and this is where the DAX pattern comes to life.
      • The first selection in the SUMMARIZE is the table as shown with the Intellisense from Power BI Desktop.
        • So with my example I put the table as ‘Budget Data without New Year’ highlighted in Blue.
      • Now where it asks for the GroupBy_ColumnName1 this is where I actually selected the column name from my Date table.
        • And with my example I selected the ‘Date'[Fiscal Year] column highlighted in Green.
        • This is because the Slicer I wanted to use was the Fiscal Year.
      • Then the final column was the ‘Date'[Calendar Date] this is so that later I can create the relationship between the two tables.
        • NOTE: You can add additional columns if required.
      • And then finally because I used the SUMMARIZE I need to create an Expression as part of the requirement.
        • So for that I created the “Not Used”, 1 highlighted in Orange.
        • And what I did here was to give it a column name of Not Used and a Value of 1, so this satisfies the SUMMARIZE syntax.
      • What the above is actually doing from my understanding is that it is using the existing relationship between the Budget data without new Year and the Date table and only returning the Fiscal Years where there is data.
  • I then pressed Enter to create the table.
    • What I then did was to Hide the Not Used Column, so that this did not confuse the people using the report.
  • So as a quick test I then put in my Fiscal Year and changed it to a Slicer and I expected to only see Fiscal Years until 2024 because that is when I have data.
  • A final thing to note is that by using the Calculated Table it is compressed, fast as well as always being updated on each refresh.

Creating a relationship from my Calculated table to my data (Fact Table)

The final step I did was to create a relationship from my Calculated table to my Data or Fact Table (Budget data without new)

  • The reason for doing this is so that when select an item on the Slicer it will filter due to the relationships being in place.
  • So I went into the Relationships view and created the following relationship as shown below.
    • As you can see above I created the Relationship between the Budget data without new and my new calculated table Fiscal Year – Slicer and on the Calendar Date columns.
    • NOTE: As you can see from the image above it does appear that the Fiscal Year – Slicer table does not have any data, it does and I think that this is possibly a small bug when creating a calculated table.

So now when I have my report and I have my Fiscal Year slicer I only see the Fiscal Years where I have data.

And if I click on a particular Fiscal Year it filters as expected.

Conclusion

So in conclusion this is a great way to create slicers in which it will only have a slicer value where you actually have data. And this works really well when you have data that has a lot of historical values that are not used in the current context.

As with past examples you can download the Power BI Desktop File here: UnPivot Other Columns.pbix