Forum Discussion

bergen288's avatar
bergen288
Icon for Helper IV rankHelper IV
3 years ago
Solved

how to create a slicer with values from multiple columns?

Below is an example of my table visual with Name1, Name2, Name3 columns.  I would like to create a slicer with values from all 3 columns so that I can select particular name such as "C" no matter which column has it.  That is, the target slicer value "C" should select 1,3,4,7,8 rows.  How to do it?

 

Need Help

2 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago

    you need to create a new table

  • bergen288's avatar
    bergen288
    Icon for Helper IV rankHelper IV
    3 years ago

    Here is how to make it work, partially.  Below is my DAX to create new slicertable: 

    SlicerTable =
    DISTINCT (
        UNION (
            VALUES ( Table1[Name1] ),
            VALUES ( Table1[Name2] ),
            VALUES ( Table1[Name3] )
        )
    )
    Next is a measure:
    Name =
    IF (
        SELECTEDVALUE( 'Table1'[Name1] ) IN VALUES ( SlicerTable[Key] )
            || SELECTEDVALUE( 'Table1'[Name2] ) IN VALUES ( SlicerTable[Key] )
            || SELECTEDVALUE( 'Table1'[Name3] ) IN VALUES ( SlicerTable[Key] ),
        SELECTEDVALUE(SlicerTable[Key]),
        BLANK ()
    )
     The last step is to create slicer by slicertable[Name], and add 'Name' mesure into target table visual.  It gives me correct answer when "C" is selected.

    However, it gives me nothing when "All" is selected or nothing is selected.  My 'Name' measure is copied and modified from someone.  I believe the BLANK function is the root cause.  How to fix it?