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

@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. 

Post Prodigy

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?

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!