Slicer / Filter Against Multiple Columns

[smillward]

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="")

[v-shex-msft · Accepted solution]

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]

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.

[rvlalai]

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!!