A few months ago, a very interesting question was posted on the Power BI community forum and I had the opportunity to work on it. Finally, I have the time to write a blog post about this so that we can all learn from it!

Before we start, here is the question that was asked:

“I’ve seen several posts on how to display the values chosen in a slicer… but what about displaying the values that weren’t chosen (de-selected) – i.e. “All Choices Except _____”  My problem is that I have some slicers with 20+ choices in them, and they need to be able to multi-select. I could create a (verrrrry loooong) title that lists everything that was chosen, but even if you make the display big enough to see all of them, the really crucial piece of information is still obscured – namely, what was intentionally left out.

Example – you have a chart that displays the age demographics of people who eat fruit. There are only 5 age groups displayed in the graph, but there are potentially 25 different types of fruits. The fruits are in a slicer. Lets say you want to see the graph with all fruits except Grapefruits. So you go to your slicer select all choices and then de-select Grapefruit.

The normal dynamic slicer using CONCATENATEX is going to display the 24 fruits chosen, but unless you knew all 25 choices off the top of your head, you won’t notice that the one that’s missing is Grapefruit. So I’d like a measure capable of showing both the normal list of what was chosen in the slicer, BUT… if the number of choices selected is (TotalChoices – 1), then show “All Except ” and the one choice that was excluded.  Even better, if I could allow 2 choices for the exclusion logic (“All Except Grapefruit, Raspberry”).

Is there a way to identify choices that have been de-selected? Any alternative ideas for ways to approach this?”

The sample data is just a list of Fruits and Vendor:

The request in the original post is to show list of not selected fruits (All except) if the total selected fruits are less than 1 or 2 of total available fruits. In other words, if 10 fruit values are available in the slicer, and let’s say the user selects 8 fruits then you want to show “All Except” – otherwise you want to show all the selected fruits. I made this option a bit more dynamic using a what-if parameter with the range from 0% – 100%, where you can set after what % you show not selected fruits (All except) – otherwise “selected” fruits are shown. Taking the previous example, if we have 10 fruit values available in the slicer, and the what-if parameter value is set to 90% this means that after 9 fruits are selected we show “All Except”. In case the what-if value is 80%, we show “All Except” after 8 values are selected and so on…

Here is a measure to achieve all this:

Slicer Values Selected =
VAR __selectedValues = VALUES ( Fruits[Fruit] )
VAR __totalValues = CALCULATETABLE ( VALUES ( Fruits[Fruit] ), ALL ( Fruits[Fruit] ) )
VAR __totalRows = COUNTROWS ( __totalValues )
VAR __selectedRows = COUNTROWS ( __selectedValues )
VAR __differenceRows = __totalRows - __selectedRows
VAR __perecentageSelected = DIVIDE ( __selectedRows, __totalRows )
RETURN
SWITCH( TRUE(),
    __totalRows = __selectedRows || __selectedRows == BLANK(), "All values selected",
    __perecentageSelected <= [Maximum Selected Value], CONCATENATEX( __selectedValues, Fruits[Fruit], ", " ),
    "Selected except " & CONCATENATEX( EXCEPT ( __totalValues, __selectedValues ), Fruits[Fruit], ", " )
)

And here is the breakup of the above measure:

__selectedValues = Create a table of selected fruits
__totalValues = Create a table of all the possible fruits available
__totalRows = Count of total fruits table
__selectedRows = Count of total fruits selected
__differeceRows = total rows – selected rows
__percentageSelected = What is the % of selected fruits after which we want to show “All except”

In the final return (switch) statement we are checking :

• If the total and selected rows are equal, or if no rows are selected then “All values are selected.”
• If the percentage of selected values are less than or equal to the what-if parameter percentage values, then we show all selected values.
• Otherwise, just show “All Except.” In other words, don’t show the selected values.

Hope you find this post useful and can use this technique to provide valuable information to the report users.

Useful Links

Learn more about CONCATENATEX function here.