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.