Most PowerBi reports will use slicers to allow the user to filter and interact with the visuals they have.
But in practice, users may forget what slicers have been applied. It’s inconvenient to have to look through each slicer, potentially across multiple pages, to see what filters are being used on the report.
Therefore, it is not user friendly. We need to show every filter that has been applied in one convenient place.
Thankfully, because I like you so much, we’ll go through how to create a table to show what filters are currently being used. It’s a lot easier than you think!
In its most basic form, the table will look something like this:
Creating the Measure for our Filter Table visual in Power Bi
First, we will want to open up Dax Studio in the external tools tab in our ribbon.
From here, you can right click on any table and then click “Define filter Dump Measure (All tables)”.
Once you’ve clicked that, remove the “Define Measure” text from the top of the query. Then copy the rest of the query into a New Measure in your PowerBi report.
The measure we’ve copied checks every single column in every table to see if a filter has been applied.
If we set a slicer that filters the Region column to “West”. The measure would return ‘TRUE’ for the Region’s ‘IF’ statement portion.
Every ‘IF’ statement in the measure checks a new column. Correspondingly, you can easily see which column is being checked for filtering by looking at the logical test section of the ‘IF’ statement i.e. the ‘ISFILTERED’ function at the top.
Since each column has been split up into its own portion, we can remove any other columns that we don’t need to be checked. We can do this by simply deleting any other columns ‘IF’ statements.
Using our Filter Table visual
Now we can bring on some slicer visuals and slap in that Filter measure we just wrote as a Table visual,
Like so:
Next, we apply some filters and…
Our applied filters are now showing in the table!
You can even have multiple selections and they will still show!
However there’s one problem, “Customer[CustomerName]”?, “Product[ProductName]”?
Evidently, these are not very user friendly names.
We can fix this by going into our measure and replacing the highlighted text with what we’d like the name to be.
You can do this for all columns.
Let’s see what the table looks like now we’ve changed the names…
Now that’s better!
We can also customize your filter table’s visuals to make it look however we’d like but I’ll leave that up to you 😉.
You now have a working visual that shows the filters applied to your report.
I hope this a good addition to your toolbelt and remember, anything is possible in Power Bi !
