Slicer option multiple conditions

[Anonymous]

Hi.

I have a custom slicer with several options to choose from. I want to create a measure that when I click one (or more) options from the slicer, a map shows the values (count of something for example) of the options I chose. Problem is, that would require a lot of IF statements, correct? 

So, IF I chose OPTION 1 (using the CONTAINS function), the COUNT of something value is showed in the map, else IF I chose OPTION 2 display COUNT of something else, etc etc. ... else IF I chose OPTION 1 and OPTION 2, display the COUNT of something and something else, etc etc.  This will take a lot, and if we add more options to the slicers, it will only get larger.

How would I solve this? Is there a shorter method to that?

Thanks.

[selimovd]

Hey @Anonymous ,

there is a shorter way. The combination of SWITCH with TRUE gives you a more clear solution.

Like this you can always put the if condition per line and then the result. For example:

Switch Measure =
VAR vSelectedSlicer =
    SELECTEDVALUE( mySlicerTable[mySlicer] )
RETURN
    SWITCH(
        TRUE(),
        vSelectedSlicer = "Option 1", COUNT( myTable[column 1] ),
        vSelectedSlicer = "Option 2", COUNT( anotherTable[column 2] ),
        vSelectedSlicer = "Option 3", COUNT( totallyDifferentTable[column 3] ),
        vSelectedSlicer = "Option 4", COUNT( myTable[column 4] )
    )

Read the following article about that:

DAX - The Diabolical Genius of “SWITCH TRUE” | P3 Adaptive

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Thank you for quick reply!

What if I selected two or three options from the slicer? I would need multiple switch conditions right? Does the SELECTEDVALUE return a list when I click on multiple options?

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a measure as below and check whether it can get your expected result:

Measure =
VAR _sellist =
    ALLSELECTED ( 'SlicerTable'[colname] )
RETURN
    IF ( "OPTION 1" IN _sellist, COUNT ( 'Table'[A] ), 0 )
        + IF ( "OPTION 2" IN _sellist, COUNT ( 'Table'[B] ), 0 )
        + IF ( "OPTION 3" IN _sellist, COUNT ( 'Table'[C] ), 0 )

In addition, you can refer the content in the following blogs. Hope they can give you some clue or insights...

Dynamic Measure Selection in Power BI

Power BI DAX Dynamic Measures

Change Measures Using a Slicer in Power BI

If the above ones can't help you find the solution, please share some sample data with Text format and your expected result with backend logic and special examples in order to provide you a suitable solution. Thank  you.

Best Regards