Forum Discussion

smillward's avatar
smillward
Frequent Visitor
9 years ago
Solved

Slicer / Filter Against Multiple Columns

I want to be able Filter or slicer a set of data, based on values within two seperate columns.   Currently it is stored like This   Player 1   Player 2   Viewers   Game Bert         Ernie       ...
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi smillward,

     

    You can try to use contains function to achieve your requirement.

     

    Sample:

    1. Use"player 1" and "player 2" to create a selector table.

    Select = UNION(VALUES(Game[Player 1]),VALUES(Game[Player 2]))

     

    2. Write a measure to calculate selected value.

    Total = CALCULATE(SUM(Game[Viewers]),FILTER(ALL(Game),CONTAINS(ALLSELECTED('Select'[Player 1]),'Select'[Player 1],[Player 1])||CONTAINS(ALLSELECTED('Select'[Player 1]),'Select'[Player 1],[Player 2])))

     

    3. Use "Select" table as the source of slicer, then drag total measure to card visual.

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi smillward,

 

You can try to use contains function to achieve your requirement.

 

Sample:

1. Use"player 1" and "player 2" to create a selector table.

Select = UNION(VALUES(Game[Player 1]),VALUES(Game[Player 2]))

 

2. Write a measure to calculate selected value.

Total = CALCULATE(SUM(Game[Viewers]),FILTER(ALL(Game),CONTAINS(ALLSELECTED('Select'[Player 1]),'Select'[Player 1],[Player 1])||CONTAINS(ALLSELECTED('Select'[Player 1]),'Select'[Player 1],[Player 2])))

 

3. Use "Select" table as the source of slicer, then drag total measure to card visual.

 

Regards,

Xiaoxin Sheng

cbritr's avatar
cbritr
Regular Visitor
9 years ago

Trying to do the same, but this DAX doesn't work for me. 

 

Combined Budget = CALCULATE(SUM('Sheet1 (2)'[budget]),FILTER(ALL(Sheet1[Merged]),CONTAINS(ALLSELECTED('Selector Table'[owner]),'Selector Table'[owner],[Budget Sum])||CONTAINS(ALLSELECTED('Selector Table'[owner]),'Selector Table'[owner],[Budget Sum])))

 

The Card visual with this measure gives me the dreaded "Can't display visual", and the slicer does not apply to other visuals.