Lineage between your DAX measures

[stevedep]

In this post, I will show how to parse your measures and display the interrelationship like below. 

The steps are as follows

- Use DAX studio to extract the definition of your measures. 

- Load the measure definition in Power BI. 

- Run some Power M code.

- Display the inter-relationship between measures using a network chart.  

1) Using DAX studio extract the definition of your measures. 

 select MEASURE_CAPTION, EXPRESSION from $SYSTEM.MDSCHEMA_MEASURES

select ExplicitName, Expression from $SYSTEM.TMSCHEMA_COLUMNS where [Type] = 2

2) Load the measure definition in Power BI & Run some Power M code

Create a blank query in Power Query and add the below code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\myname\measures.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([EXPRESSION] <> 1)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"MEASURE_CAPTION", type text}, {"EXPRESSION", type any}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([EXPRESSION] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Check", (recordvar) => List.Select( List.Transform(List.Zip({#"Filtered Rows1"[EXPRESSION],#"Filtered Rows1"[MEASURE_CAPTION]}) , each if Text.Contains(recordvar[EXPRESSION], "[" & _{1} & "]" ) then _{1} else 0 ), each _ <> 0)),
    #"Expanded Check" = Table.ExpandListColumn(#"Added Custom", "Check"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Check",{{"Check", "ConsistsOf"}})
in
    #"Renamed Columns"

3) Display the inter-relationship between measures using a network chart.  

Please send me a PM to receive a copy of the Power BI file.

Enjoy!

Kind regards, Steve. 

[DataZoe]

@stevedep This is also awesome! Super useful when optimizing or making changes to a model that has a hierarchy to measures. That way you can figure out your impact. 

[gvg]

Hi,

Great stuff. How do you display measure titles, though? They do not show for me. Maybe because I have too many measures (300+)?

Also, how would you display measures from one measure table if I have few?

[AD_SNOW]

Hey @stevedep , this is brilliant and super helpful. Thanks for the solution. However, I can only see the direct connections. Can we also have direct and indirect if I search for one measure.

Example: 

You current approach - If I have a MeasureA = MeasureB/MeasureC

and MeasureB = MeasureD+MeasureE

and MeasureC=MeasureX-MeasureY

if I search for MeasureA, I'm only seeing MeasureB and MeasureC beacause those are directly used in MeasureA.

However, my requirement is if I search for MeasureA, I want to see MeasureD and E (linked under MeasureB), Measure X and Y (lined under MeasureC).

Can you please help me with the same?

Thanks!