It has been a while since I written a blog post, even my ten years old daughter started teasing saying “Motionless blog”, so decided to get back to blogging with a short and simple blog post based on a question I received about one of my Power BI portfolio report Global Landslides Data. Few people asked me how I created the slicer in this report,
So how I created the Dynamic Legend in Power BI Visual, Line Chart :
I was using slicer as a button to dynamically choose the legend I wanted to show in the line Chart. In fact, it was relatively straightforward:
First I created an aggregated table like below
Table = UNION( //Incidents SUMMARIZE('Global Landslide Data' ,'Global Landslide Data'[ID] ,'Global Landslide Data'[Date] ,"Slicer","Incidents" ,"Incidents",'Global Landslide Data'[Incidents] ,"Distance",BLANK() ,"Injuries",BLANK() ,"Fatalities",BLANK() ) ,//Distance SUMMARIZE('Global Landslide Data' ,'Global Landslide Data'[ID] ,'Global Landslide Data'[Date] ,"Slicer","Distance" ,"Incidents",BLANK() ,"Distance",CALCULATE(SUM('Global Landslide Data'[Distance])) ,"Injuries",BLANK() ,"Fatalities",BLANK() ) ,//Injuries SUMMARIZE('Global Landslide Data' ,'Global Landslide Data'[ID] ,'Global Landslide Data'[Date] ,"Slicer","Injuries" ,"Incidents",BLANK() ,"Distance",BLANK() ,"Injuries",CALCULATE(SUM('Global Landslide Data'[Injuries])) ,"Fatalities",BLANK() ) ,//Fatalities SUMMARIZE('Global Landslide Data' ,'Global Landslide Data'[ID] ,'Global Landslide Data'[Date] ,"Slicer","Fatalities" ,"Incidents",BLANK() ,"Distance",BLANK() ,"Injuries",BLANK() ,"Fatalities",CALCULATE(SUM('Global Landslide Data'[Fatalities])) ) )
The logic is to create a table with the DAX function UNION. Each Table expression in UNION function represents a value of slicer. Apart from that slicer related value, all the rest of the values are blanks. It is key to have them as blanks than zero’s, we don’t see any data.
Then I chose the slicer column as the value in my Slicer visual and allowed slicer to have multiple selections. Then in the visual, I have used all measures as values, so based on the slicer selection it shows the trend line.
You can see it in the report here – https://app.powerbi.com/view?r=eyJrIjoiN2I4YWI4MjMtYmIyYy00ZjRkLWFjYTktZjM1ZjIwODk0ZjkzIiwidCI6IjM4YmU3MjU5LTMyMGQtNDZkYy04YzNiLWNkZDA5OTM1NGExMyIsImMiOjh9
Till next time,
Prathy 🙂
4 comments
But your colored dots below, the legends havent disappeared based on your selection.
I was able to use this to get around the issue of not having multiple fields in the legend. Thank you so much!
This has been so helpful. Thank you!
Hey Prathy! Good blog post and quite informative 🙂
Can you share the page dimensions of the PowerBI report..? I really like the way you’ve made use of the entire breadth of the screen – almost giving it a webpage feel. It took me a second to figure out that this is in fact a regular report that I’m viewing in a frame!
Thanks,
Shaz