Is my Power BI Report Filtered? - Excelerator BI

Is my Power BI Report Filtered?

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.

Power BI Online Training

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
No Filters

9 thoughts on “Is my Power BI Report Filtered?”

  1. 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 ?

  2. 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.

  3. 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.

Leave a Comment

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

Scroll to Top