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 whi...
Need Help
  • Ahmedx's avatar
    Ahmedx
    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?