I was delivering Power BI training in Melbourne last week and one of my students asked if it was possible to show on a report if any filters have been applied. It is a very good question because it is not always immediately obvious if filters have been applied or not. Filters impacting a report page can come from many places including the obvious ones such as:
- The side filter panel
- Slicers and cross filtering on a report page
But there are some less obvious places that filters can come from including the following:
- Hidden slicers on a report page that are synced with a visible slicer on a different report page.
- From a drill through of a visual (drilling into visuals will filter your reports by default.
The visual header has a “filter” icon as shown below. In the image below, I have selected Bikes (1 below) and that has filtered the tree map (2 below). The tree map visual has a “filter” icon (3 below) and that icon is telling me that the tree map is being filtered by “bikes” (4 below).
This filter icon can help, but it can’t easily tell you that there are no filters anywhere on this report page.
Is My Power BI Report Filtered?
There are many ways to solve most problems in DAX. One of my students (Mark) suggested an approach that I really like. It is actually as simple as comparing a suitable “current” measure result with a known result when there no filters.
Total rows in the sales table when no filters are applied = 60,398
Current rows in sales table = some value
If some value = 60,398 then there are no filters applied
In order to write a “correct” measure for your own data, it is essential to understand the relationships in the data model. In fact this is essential when writing any and all measures in DAX.
Here is my data model
All relationships are 1 to many and all have cross filtering set to single direction. It therefore follows that if any table in this model is filtered, then the sales table must also be filtered.
My DAX Formula
So here is my DAX measure formula
Are there any filters? = IF(COUNTROWS(Sales) = CALCULATE(COUNTROWS(Sales), REMOVEFILTERS()), "No Filters", "Some filters" )
Note I have used the new REMOVEFILTERS() function here – I will probably blog about that at some time in the future.
And here is the measure in a card on my report