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 =IF (
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.
Nice! Because of this lack of slicer filtering (prior to this change) I’ve defaulted to using bar charts as the main slicing UI thanks to cross-filtering. But sometimes you do want to use a slicer, for instance for space-saving considerations, typically when you want to be able to search among many values. (Could the visual header of all visuals have a search box? One can dream!)
If you reverse the filtering logic, this can give you items that do *not* have associated data, thus mimicking the behavior of Excel slicers or what Qlik describes as “the grey” and often touts as one of their main differentiators. You could build something close to this (not my site): http://qlikshow.com/grey-so-what/
Though to do something that behaves exactly like Qlik, we would need expression-based formatting for the background and/or font color of slicer items.
Awesome! Next would be to sort a slicer by a certain measure!
Kasper,
Isn’t it faster to use INT ( NOT ISEMPTY ( FACT ))
Also, do you anticipate being given this capability on off page filters?
Also, it’s needed on Rows of Matrices as a visual filter but also as a short circuit to not calculate column measures when the row has no fact data.
I don’t think it matters much. Countrows is super fast as we keep some statistics and we don’t really have to count it all.
No comment on the rest of my comments?
Great tip and takes away a lot of inconvenience from end users. I am curious on applying this on page level filters (or all page filters) as it will save time if we have multiple slicers on the page. There is no point repeating this to each individual slicer.
This is a very clever trick! Thanks for sharing!! I usually default to a bi-directional relationship for these situations but I can always here Alberto Ferrari’s voice in my head screaming at me “NEVER use a bi-directional cross filter, or you will go to hell!!”
I was trying to implement this logic in one of my requirements wherein the slicer should only show those YYYYMM’s for which data is loaded. Currently, the CALENDAR function is showing all the dates which is causing confusion to the end users.
However, once I click on my slicer, I am not able to drag the COUNTROWS measure to Visual level filters. Is it a Power BI version related issue? I am currently using March 2019. Please advise.
I’ve had this same problems. The measure works in lots of places, but can’t be used as a filter on some visuals. For some others, I can place it on there, but it then won’t let me set the condition by editing the filter in the filter pane.
I’m sure there’s a good reason, but I haven’t figured it out yet.
Thank you! This saved my life 😀
I completely forgot about this one! Thanks Kasper!
This will help TODAY with an issue we have been having. 🙂
Hi,
This works fine for slicers but in my reports i am using filters on all pages where measure can’t be used. I there any workaround in this case?
Thanks
Hello
Super smart solution! Thanks a lot for this!
Thanks! for sharing this trick.