Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

stevedep

Lineage between your DAX measures

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

stevedep_0-1616158065746.png

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.  

stevedep_1-1616158349069.png

 

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

 

Enjoy!

 

Kind regards, Steve. 

Comments