Updated: 2 days ago
When I'm developing reports, I'm asked multiple times to "hide" the slicers/filters from the report page. Usually this is to make space in the page for other visuals and because customers don't want to use the filter pane for some reason.
This happened so many times, and only in the last couple of months I decided to try some things out and get a bit creative with Power BI.
I came up with two solutions, which I think work great in these scenarios.
- This might not working very well if you have loads of filters
- This might not work very well if users select 100 options in a slicer (exaggeration, but you get the point)
So without further ado, let's jump to the solutions!
1st Solution - Using a text box to show filter selections
As you can see from the video above, in this solution we can do the selections we want in our filters, and they will be reflected in a text box below the title, which in my opinion looks pretty neat!
With this solution you can make even the most picky users happy, they will never get lost on what's filtered again!
And if you're asking yourselves "she did that with slicers, does it work with the filter pane too?!"
And my answer to you is YES, it does work with slicers and the filter pane!
So, what's the trick to make this dynamic text box work?
The trick is using a DAX function called CONCATENEX.
1) Decide which field selection you want to show in your dynamic text box and create a new concatenex measure for each of those fields.
In my case I chose my 3 slicers:
- Discount Band
As I mentioned before, to make the text show dynamically based on the filter selections, you need to create a measure that will retrieve the selected values:
concatenatex_country = calculate( CONCATENATEX( DISTINCT(financials[Country]), financials[Country], ", " ) )
The concatenex function is perfect for this scenario as it will retrieve a concatenated string with all my filter selections (with a delimiter that I chose, in this case it was a comma ",").
A small note: I had to use the DISTINCT function because here my country column was in my sales table. If this was a DIM table (basically a table just for the geo info where the countries were all distinct values) you wouldn't need to use the DISTINCT function in this measure.