Note to self :)

Dynamic Legend in #Power BI Visual, Line Chart

by Prathy Kamasani 11 December 2018

written 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 🙂

11 December 2018 4 comments

DAX Power BI

Dynamic Period variance with DAX in #PowerBI

by Prathy Kamasani 16 July 2018

written by Prathy Kamasani

One of those days where I spent a reasonable amount of time trying to solve a tricky DAX expression and once I got it; I came to know it was not at all tricky. In fact, it was far too simple to admit the time spent 🙂

When I showed PARALLELPERIOD and SAMEPERIODLASTYEAR measures to my business user, he asked me if he can select random quarters and measure should always get the variance from the earlier period based on the slicer choice than Time intelligence.

This is how my data looked like:

To get the Selected Previous Period value, I first created a variable to get the Current period value

VAR CurrentPeriod = MAX(Table1[Period])

then I created another table variable to get the selected values from the slicer. I used CalculatedTable with ALLSelected(), so my context works.

VAR Selectedvalues = CALCULATETABLE(VALUES ( Table1[Period] ),ALLSELECTED(Table1))

Then I created another variable to get the earlier period value based on Filter.

VAR PP=CALCULATE(MAX(Table1[Period]),FILTER(Selectedvalues,Table1[Period]<CurrentPeriod))

And finally, in Return statement, I used the Value column with SUM and filter. Here is the full DAX expression of the measure

Previous Period =

VAR CurrentPeriod = MAX(Table1[Period])

VAR Selectedvalues = CALCULATETABLE(VALUES ( Table1[Period] ),ALLSELECTED(Table1))

VAR PP=CALCULATE(MAX(Table1[Period]),FILTER(Selectedvalues,Table1[Period]<CurrentPeriod))

Return

CALCULATE(SUM(Table1[Val]),Table1[Period]=PP)

If you want to try yourself, here is the M Script for Query Editor.

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjRU0lEyNFCK1QGyjUBsQwjbGMQ2grBNQGxjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Period = _t, Val = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}, {"Val", Int64.Type}})

in

#"Changed Type"

It was a good exercise, I certainly learned how to not to think when working with DAX! Hope it helps someone out there.

URL to download the PBIX – https://1drv.ms/u/s!Avm7gbgZtlMlwFB99Ti5W2_eYsjU

Keep Smiling,

Prathy 🙂

16 July 2018 4 comments

Power BI Quick Tip

How to add extra columns to an existing Power BI file which has CSV as data source

by Prathy Kamasani 4 August 2016

written by Prathy Kamasani

Quick Power BI Tip:

Case: How to add extra / new columns to an existing Power BI file which has CSV as data source?

Solution:

When I was working with Power BI today, first I created a model with my CSV files, while I was doing the report; that is when I realised I need one more column from the data source. After I had received the new file, I thought I would just refresh the data source and power BI automatically refreshes my query. In fact, Power BI does automatically update my query but what I noticed was, it excluded the last column. When I looked in the advanced editor query looked like my example here

let
    Source = Csv.Document(File.Contents("\\Mac\Home\Desktop\Blog\2.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source)
in
    #"Promoted Headers"

2016-08-04_06-08-57

I can see, it is strictly bringing two columns only. So the tip is to simply get all the columns into the existing Power BI file, just change the number of columns to the new number of columns in the CSV file, which is 3 in my case.

Updated Query:

2016-08-04_06-21-21

let
    Source = Csv.Document(File.Contents("\\Mac\Home\Desktop\Blog\3.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source)
in
    #"Promoted Headers"

2016-08-04_06-21-49

That’s all for now 🙂

Prathy

4 August 2016 2 comments