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

  • rvlalai's avatar
    rvlalai
    Frequent Visitor
    9 years ago

    I have created a selector table from my end, however i noticed that the slicer doesn't work. Do we require to link this newly created selector table to link this again or something?

     

    I've been struggling for a while today with this, hope you guys know the answer!!

  • 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.