cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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?

1 ACCEPTED SOLUTION
Super User

@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], ", " )
)```

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

8 REPLIES 8
Regular Visitor

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?

Super User

@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], ", " )
)```

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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!

Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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!

Super User

@twintrbl change following line and that will do it.

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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!

Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors