Forum Discussion
Interactive filters across multiple variables
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!
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.
2 Replies
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
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.
