Dynamic Title/Measure based on what is *NOT* chosen in a slicer

[twintrbl]

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?

Thanks in advance!

[lauramirlyn1]

I used this for a slicer of Fiscal Year, but then fiscal years are not in order, is there a way I can add a sorting on this measure?

[parry2k · Accepted solution]

@twintrbl here is something you can use and tweak as per your need. I have 5 values in this example

if no value selected or all values selected then show All selected

if 3 or less values selected, concatenate selected values

if 4 values selected, concatenate not selected values and show except message

Selected Message = 
VAR __selectedValues = VALUES( Slicer[X Axis] )
VAR __totalValues = CALCULATETABLE( VALUES( Slicer[X Axis] ), ALL() )
VAR __totalRows = COUNTROWS( __totalValues )
VAR __selectedRows = COUNTROWS( __selectedValues )
VAR __differenceRows = __totalRows - __selectedRows
RETURN 
SWITCH( TRUE(),
    __totalRows = __selectedRows || __selectedRows == BLANK(), "All selected",
    __differenceRows > 1, CONCATENATEX( __selectedValues, [X Axis], ", " ),
    "Selected except " & CONCATENATEX( EXCEPT( __totalValues, __selectedValues ), [X Axis], ", " )
)

[twintrbl]

That is an elegant thing of beauty! Thank you so much! I honestly expected it to be one of those 75-lines-of-DAX measures, if it was possible at all.  Sweet!

[parry2k]

@twintrbl  ha ha, not at all, depends , not bad to have 75 lines of DAX code if requird but not for this case.

[twintrbl]

I hate to do this, but I just discovered a quirk in this simple measure - which means I will probably need to add to the size of that formula. You've already solved the main issue, but if you have another simple answer for how to handle this quirk, I would really owe you a beer (or your alternate beverage of choice)...

To follow my example, let's say there is a filter both for fruit and for state. When you select a specific state, the filter for fruit is now restricted to only the fruits that are in that state. So instead of the filter offering you choices for all 25 fruits, now it only has 15 choices. Even if you don't touch the filter for Fruit, instead of showing "All Fruit" like it should (since nothing has been filtered for or filtered out), this measure thinks that fruits have now been filtered, so it now displays the 15 choices that are now effectively chosen, because the selected rows are now effectively less than the total rows.  

So I need a way to have the __totalValues variable recalculate and adjust for the reduced choices that are created by the action of the other filters. Maybe some kind of handler with "ISCROSSFILTERED" that can count the new __totalRows based on the effect of the other filters, so when you do filter the fruit, it is still capable of doing the "All Except " instead of displaying everything.

Or am I out of luck now?  Sorry for the extra ask!

[parry2k]

@twintrbl change following line and that will do it.

VAR __totalValues = CALCULATETABLE( VALUES( Slicers[Fruit] ),ALL( Slicers[Fruit]  ) )

[twintrbl]

Once again, you surprise me with the simplicity! Obviously, I need to go back and study the CALCULATETABLE and related filter functions a lot closer. Thank you so much for taking the time to save my bacon again! I truly appreciate it!

[parry2k]

@twintrbl bring it on, indeed just go thru each function and it is super simple (IMHO). Cheers!! Glad to help.