Dynamic Legend in #Power BI Visual, Line Chart

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 🙂

#Notetoself #PowerBI #PowerBIWorkarounds