As of this month Power BI finally supports filtering slicers down to only show rows that have fact data. Before the only thing you could do to achieve this was some workaround like: I described here where you filter down the dimension using a calc table. The other approach was to use Bi Directional cross filtering which would filter down the dimension table appropriately. This leads to performance issues though.
Now you can use a measure to filter down the slicer. If you take a scenario like below where we have sales that can be driving by promotions.
In reality most of the promotions are no longer used in the timeframe we are reporting on but are still part of the database so the slicer will show all promotions. This creates a very annoying experience for your business users as they might select promotions and get this:
With the new filtering of slicers we can stop this behavior and filter the promotions to only those that have sales. To determine this that I am just using a simple COUNTROWS(SALES) measure. I add it to the slicer like this:
This will now determine for each slicer value if it has more or equal to 1 sales row. Resulting in less and most importantly only relevant promotions:
The other cool thing here is that because it is a measure it gets recalculated for other filters. So lets say you want to change the reporting period and your period doesn’t contain certain promotions the list will only show those with sales.
More tricks 🙂
That bring us to some other cool things you can now start doing. For example you can make slicers dependent on each other and show content based on selection. Imagine you want your users to choose either the current week or select one of the previous weeks. You could use a measure to only show previous weeks when this is selected
Or only the current week when that is selected:
The trick here is really simple. I created two measures. One to determine the current week:CurrentWeek =
WEEKNUM ( NOW () )
and one that I will use as filter and responds to the slicer selection:PrevWeekSelection =
SELECTEDVALUE ( WeekSelection[Column1] ) = “Current Week”,
IF ( SELECTEDVALUE ( DimDate[WeekNumberOfYear] ) = [CurrentWeek], 1 ),
IF ( SELECTEDVALUE ( DimDate[WeekNumberOfYear] ) < [CurrentWeek], 1 )
Now I use this measure as filter in the slicers filter pane:
and voila that’s it. Pretty cool right, using these filters will allow you to flip slicer values on the run. This will also be very interesting to combine with calculation groups that will allow you to change the outcome of measure based on slicer values.