Dynamic Legend in #Power BI Visual, Line Chart

by Prathy Kamasani

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 🙂

You may also like

4 comments

Anusha 19 November 2019 - 12:58 am

But your colored dots below, the legends havent disappeared based on your selection.

Reply
MJ 26 June 2019 - 1:38 pm

I was able to use this to get around the issue of not having multiple fields in the legend. Thank you so much!

Reply
Mary 7 May 2019 - 5:44 pm

This has been so helpful. Thank you!

Reply
Sharan Timbadia 26 February 2019 - 11:57 am

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

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More