Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
twintrbl
Advocate I
Advocate I

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?

 

Thanks in advance!

1 ACCEPTED SOLUTION
parry2k
Super User
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.

View solution in original post

8 REPLIES 8
lauramirlyn1
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?

parry2k
Super User
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!

@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!

@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!

@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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.