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.
Remarks:
- 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:
- Country
- Discount Band
- Segment
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.
If you try using a column from a fact table (column doesn't have distinct values) then you will see the selection values replicated too, something like this:
Not what we want right?
So we use the distinct so we can have our country show up only once, and not hundreds of times!
We do the same measure for the Discount Band and Segment fields:
concatenatex_discount band = calculate(
CONCATENATEX(
DISTINCT(financials[Discount Band]),
financials[Discount Band], ", "
)
)concatenatex_segment = calculate(
CONCATENATEX(
DISTINCT(financials[Segment]),
financials[Segment], ", "
)
)Now that we have our base measures, we can crack on with our visualisation tricks.
2) Adding our measures to our text box.
This trick is actually very easy, as long as you have the correct measures set up, this part should be very smooth.
The complete process is on the video below, just follow the steps and you should be good to go with your new dynamic text box!
Because this is a text box, you can get really really creative with this solution!
2nd Solution - Using a matrix to show filter selections
Because using a text box can be a bit tricky in terms of formatting and aligning text, I also came up with this second option where you can have the same solution but with a matrix, so it's easier to get perfectly aligned text if you want to.
For this one I used the same metrics we created in the previous solution, only the visual changed.
Below is a little tutorial on how to achieve the same kind of solution using a matrix visual:
Thank you for reading/watching and happy creative days with Power BI!!
Interested in learning more about Power BI Report Design? Then the Power BI Report Design Bootcamp is for you!
