Visual level filters for slicers

Visual level filters for slicers

Since the latest Power BI release (June 2019) we are now able to filter a slicer based on a measure!
And I think we’ve all been waiting for this feature for quite a while.

One of the top reason we needed this feature was to have the ability to sync slicers. (Well this was already achievable with the bidirectionnel filter which is a really bad practice…)
So as we can see on the example below the customer Alma has bought only three products but the whole list of products is still showing up in the slicer Product.

So how can sync the slicers and make sure only the sold products apear in the slicer?
This is pretty simple we only need to have a measure and not a column and drag this measure into the visual filter area of the slicer.

Once we have dragged the measure into the visual filter pane the slicer will only show the sold products.

And if we want to filter a slicer based on more than one measure we can simply create a measure that will check the not null condition on multiple measures and returns 1 if at least one the measure is not null. And then drag this filterMeasure into the visual filter area to sync our slicers.

filterMeasure= 
if(not(ISBLANK([Order Qty])) || not(ISBLANK([Sales Amount])) ,1,0)

What if we want to filter a slicer based on any measure?
In other words reproduce the same behavior as the bidirectional filter.
Simply use the below measure and drag into the visual filter and set it to greater than 0 but bear in mind that this will strongly affect the performance depending on your model size.

filterMeasure= 
countrows(your fact table)

Leave a Reply

Your email address will not be published. Required fields are marked *