Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Slicer option multiple conditions

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.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

3 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    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
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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