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.
E.g.
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
Tenho um tabela fato que contemplam todas as variáveis, onde preciso inserir segmentações de dados, onde ao clicar em uma determinada segmentação de dados, todas as outras segmentações de dados precisam ser filtradas.
Se usar a teoria da tabela dimensão, como faço para ocorrer isto acima mencionado ?
This can be done. Read the comment below from Daniil.
What would be most helpful would be a variable that could be referenced that would show the combined filters being utilized on any given page. Other reporting tools provide such and it is very helpful.
This is a great technique. It’s too bad REMOVEFILTERS() hasn’t made it’s way to the report server version of Power BI yet, though. Any thoughts about how to do this with more “traditional” DAX syntax.
That was yesterday – today it is available ? https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-september-2019-feature-summary/
Regardless, just replace REMOVEFILTERS() with ALL(). It is the same thing.
That’ll come in handy! Thanks Matt
Good stuff. Note that the filter icon in the visual header sometimes won’t tell you about some filters, like filters that use measures. For a more comprehensive approach, you can use DAX Studio to write a measure that lists every single filter applied: https://www.sqlbi.com/articles/displaying-filter-context-in-power-bi-tooltips/
I should actually correct myself — I meant filters that come from axis, etc., not measure filters 🙂
Simple, yet brilliant! 🙂
Thanks Matt!