Interactive filters across multiple variables

[bookhouseboy]

Hi,

I apologize if this turns out be super banal; I'm still fairly new to Power BI. I tried to google this, but it's hard to put the problem into a pithy phrase.

I want to create an interactive report on the ratings of individual scenes of a TV show based on which character is in a scene. So my source data looks like this:

It's easy enough to slice the data for, say, the character of Clara alone. But I want to give people the option of clicking on "Clara" and "Bob" and then only be shown scenes Clara shared with Bob (i.e. scenes 1 and 4).

There are 28 characters, by the way, so solutions including 28 individual slicer visuals (if they exist) would not be practical.

How do I do this? Is there a way? 

Thanks!

[v-yalanwu-msft · Accepted solution]

Hi, @bookhouseboy ;

You could unpivot columns expect [SceneNo] column. 

1.unpivot it 

2.create a slicer table.

slicer = SUMMARIZE('Table',[Attribute])

3.create a flag measure.

flag = 
var _sli=SUMMARIZE('slicer',[Attribute])
var _tab=SUMMARIZE(FILTER(ALL('Table'),[Attribute] in _sli&& [Value] ="NO"),[SceneNo])
return IF(ISFILTERED(slicer[Attribute]),IF(MAX([SceneNo]) in _tab,0,1),1)

4.then creata a matrix then apply flag is "1" into visual.

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@bookhouseboy , I think it better to unpivot all these columns as row values and then create a slicer (multiple value slicer )

https://radacad.com/pivot-and-unpivot-with-power-bi