by Howdy All,
Today we will see simple and interesting use case in power bi slicer. Power bi slicer is used to slice the data as we want. The basic and default filter condition in power bi slicer even custom visual slicer has AND logic. AND logic is we all know, it is nothing but it should pass the all condition we given.
For Example,
Lets took the sample data in excel workbook.
Now they want to filter the Exclude Ship column to ‘Excluded’ state AND Exclude Sold column to ‘Excluded’ State. well that is quite easy one, but they also want to see the ‘Not Excluded’ state in either one of the column values.
You can see, If we exclude Ship & Sold column as ‘Excluded’ State we get only single row. because here the logic is AND condition. Exclude Ship & Sold column should pass the given filter condition. In this case we don’t able to see the ‘Not Excluded’ records and their SKU’s. But that was the request from end user. They want to see the other SKU’s as well when they apply ‘Excluded’ filter to both the columns.
Same filter condition in Power BI slicer also reacts the same as Excel filter.
If the end user requested as OR logic and see the metric values then we have lot of workarounds in Youtube or PBI community or available in web. But this was not the metric. It was just a text field that they want to see as OR logic.
Lets see how to use DAX to achieve the OR logic in the slicer:
Lets create disconnected slicer calculated table by using DAX
Create separate measure for Material, Ship, Sold columns
Material:
Ship:
Sold:
Now create a combined filter which will combine all the above three measures into a single measure, we can also write as a single measure using VAR for better understanding I created for separate measures.
Now the final visual measure need to create and apply this filter into the matrix visual level filter:
Create the slicer using the Disconnected slicer calculated field as above ss.
Now filter the Ship & Sold column as ‘Excluded’ state and check for the result
That it!
Earlier we see only one SKU when we apply Excluded filter to both Ship & Sold Column,
Now using the above DAX we can see either one column should ‘Excluded’ state and show the results.
This is one of the approach for OR condition in the power bi slicer for text fields to filter.
