top of page
Millington Offices (10).png

Creative ways to show filter selections on your Power BI reports

Updated: Dec 1, 2022


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!


38,251 views11 comments

11 則留言


訪客
2023年9月06日

If I have a 'select all' option on my slicer then when I deselect everything it still shows as all items being selected. How to fix this?

按讚
訪客
2023年11月15日
回覆

Hi, try this. concatenatex_discount band = if(isfiltered(financials[Discount Band]) ,calculate(CONCATENATEX(DISTINCT(financials[Discount Band]), financials[Discount Band],", ")),"ALL") btw, thanks for the useful tips, Mara!

按讚

訪客
2023年4月11日

Super good ideas, thanks a lot!


按讚

訪客
2023年1月26日

Thank you for this neat solution! Loving it! As of now, in case that there is no filter selected, it will display all possible filter options. Is there a way to display e.g. "Select Filter" instead of all options?

按讚
訪客
2023年3月16日
回覆

Try with IF( ISFILTERED(column), function above, "Select Filter")

按讚

訪客
2022年10月20日

Very neat. Thank you for sharing!

按讚

訪客
2022年8月05日

This is superb. Thanks for sharing!

按讚
bottom of page