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          50            Halo

Ernie        Hank         32            Geometry Wars

George    Hank         51            Halo

 

My ideal situation is to have a slicer, with each of the players names (Bert, Ernie, George and Hank) that will allow me to see, in this example the total viewers of game this player has played,

 

So Bert: 50, Ernie: 82, George: 51, Hank: 83

 

I also would like if two names are selected, to display the total viewers of the two player combined,

 

so selecting George + Ernie: 133.

 

 

So far I have been unable to come up with a solution, so any advice or suggestion would be welcome, and I am able to restructure the data in order to get to the desired result.

 

As a flag, this is to be a page level filter/slicer, and it is not just a question of reaching the number by calculate(sum(), player="")

  • 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

3 Replies

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