Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to create a slicer based on multiple columns

Hello Guys,

 

How do I create one slicer based on multiple columns that can help me filter rows?

 

I have a toggle table but I dont know how to write the DAX.

 

Take below picture as an example, I want to have a slicer that have "All" from column A and "Yes" from column B. When I select "All" in the slicer, it will show me only rows with column A filtered, and when I select "Yes", it will only show column B filtered.

 

 

 

Best,

Syrus

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Anonymous ,

     

    Just based on your sample data from your original post. First create a slicer table:

     

    Table 2 = UNION(ROW("type","ColumnA","value","ALL"),ROW("type","ColumnB","value","Yes"))

     

     

     

    Then create a measure for table level filter:

     

     

    Measure =
VAR a =
    CALCULATE (
        VALUES ( 'Table 2'[value] ),
        FILTER ( 'Table 2', 'Table 2'[type] = SELECTEDVALUE ( 'Table 2'[type] ) )
    )
RETURN
    IF (
        HASONEVALUE ( 'Table 2'[type] ),
        SWITCH (
            SELECTEDVALUE ( 'Table 2'[type] ),
            "ColumnA", IF ( MAX ( 'Table'[ColumnA] ) = a, 1, 0 ),
            "ColumnB", IF ( MAX ( 'Table'[ColumnB] ) = a, 1, 0 )
        ),
        1
    )

     

    Add it to visual level filter：

     

    For more details, please refer to the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Edl-6WBAjwRPg_isp-6-B0YBU4_kCgMRg4qeekxAbrbrWQ?e=u2I9Th

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

     

5 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    6 years ago

    Hi Anonymous ,

     

    In a standard slicer, you can stack up your dimensions in the field box and it will automatically create a declining slicer for you:

     

    If your slicer is not giving you this functionality, then you need to update Power BI Desktop to the most recent version.

     

    Alternatively, you could merge/concatenate [column a] and [column b] either in Power Query or DAX and then use this new column for your slicer.

    To do this in Power Query, you would select [column a] and [column b] together using Ctrl+click, then go to Merge Column on the ribbon.

    To do this in DAX, you would create a calculated column in your table, then write the following DAX:

    combinedColumns = [column a] & " " & [column b]

     

    Pete

  • Anand24's avatar
    Anand24
    Super User
    6 years ago

    Hi Anonymous,

     

    You can use the below:

    1. Hierarchical Slicer (Separator between columns will be plus(+))

     

    2. From Power Query Editor: Merged Columns (You can have your custom seperator between columns here)

    Select required columns together -> Right Click -> Merge Columns -> Select/Type Seperator -> OK

    Take this merged column in slicer.

     

    3. Concatenated Calculated column (DAX with custom seperator)

    DAX: Concatenated_Column = [Column A] & "Seperator" & [Column B]

    Take this calculated concatenated column in slicer.

     

    Examples:

    1. Space as separator: Concatenated_Column = [Column A] & " " & [Column B]

    2. Dash/Hyphen as separator: Concatenated_Column = [Column A] & "-" & [Column B]

    3. 'and' word as separator: Concatenated_Column = [Column A] & " and " & [Column B]

     

    Thanks !

     

    If this helped you, don't forget to give a Kudos and accept this as solution !!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Guys,

       

      Thanks for the prompt reply! I really appreciate it. I think I might need to explain it more.

       

      Column A and Column B are created columns. I want to have a slicer that only have "All" from column A and "Yes" from column B

      to select. So in the slicer, there will be only 2 options. Like below picture, all the three options are from different columns. I use UNION and VALUES to create a slicer table. If I select "Denials", it will only show rows from column A that has a value "Denials. If I select "Non-Std Pmt Adj.", it will only show rows from column B that has a value "Non-Std Pmt Adj."


       

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Just based on your sample data from your original post. First create a slicer table:

         

        Table 2 = UNION(ROW("type","ColumnA","value","ALL"),ROW("type","ColumnB","value","Yes"))

         

         

         

        Then create a measure for table level filter:

         

         

        Measure =
VAR a =
    CALCULATE (
        VALUES ( 'Table 2'[value] ),
        FILTER ( 'Table 2', 'Table 2'[type] = SELECTEDVALUE ( 'Table 2'[type] ) )
    )
RETURN
    IF (
        HASONEVALUE ( 'Table 2'[type] ),
        SWITCH (
            SELECTEDVALUE ( 'Table 2'[type] ),
            "ColumnA", IF ( MAX ( 'Table'[ColumnA] ) = a, 1, 0 ),
            "ColumnB", IF ( MAX ( 'Table'[ColumnB] ) = a, 1, 0 )
        ),
        1
    )

         

        Add it to visual level filter：

         

        For more details, please refer to the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Edl-6WBAjwRPg_isp-6-B0YBU4_kCgMRg4qeekxAbrbrWQ?e=u2I9Th

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

        Best Regards,

        Dedmon Dai