DAX provides five functions (PATH,PATHLENGTH,PATHITEM,PATHITEMREVERSE,PATHCONTAINS) to help users manage data that is presented as a parent-child hierarchy in their models.
The most important of these functions is the PATH function which allows users to obtain the entire lineage of parents for each row.
Other 4 functions are then applied on the result of PATH function to obtain a browsable hierarchy in the data model
Check out the documentation on
https://docs.microsoft.com/en-us/dax/understanding-functions-for-parent-child-hierarchies-in-dax
https://www.daxpatterns.com/parent-child-hierarchies/
In this POST, I will cover
1) How to Use “M” to replicate PATH function
2) WHY USE “M” when we already have a DAX function
We will use a small dataset as follows for this post
1) How to Use “M” to replicate PATH function
We can use the Power of recursion to get the complete list of grand parents for each Child. Note that immediate parent is available for each Child.
So for example, look at the last row of the table in the picture above. “Reconciliation Expert” reports to “Manager IFRS”.
To get the first grand parent, we will have to filter column “Child Account” by “Manager IFRS” to get its parent “GM Account” and so on until grandest parent account is reached.
The picture below illustrates this
Following is the M code to achieve this requirement. Notice the use of myfunction. It recurses through the entire table for each Child Account to find all grandparents.
Since the PATH is presented from Grandest parent to Child, we use List.reverse to reverse the PATH we received from applying the function
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZLNboMwEIRfBXHOARyMzRFVSVVVqGloe4lysIwVWaWm8k/UvH0NUWIWyAWE9pvZnV0Oh/hrF22lYoqLeBU/
bd78M00pTfz7s46Pq4GoWStMxFQTVUx/CyvVKdBJQdOefr3RO901jlvZqZFlsi76L6ZYwwbwuRo1BlPQgqyDoedKzjunrJmChOZz0w8tmHH6MnNFlADXIdSVWs5HEpSHNXjFuPhQRdMMA1XZnIW20ogfcc/
woGGWIyB9d6yV9hKVxicKqwILJhjh2RIAMVVQgmAbX+VOD/Mt4ZhksMH1pv1ZcZKM/pTKIyehb/fyBXi8ZTqcCNIZXqJftvt6jgLj2v0KfZamu9MTMQL4XvBOcdlK1geONn9ebRdEYZzjPw==", BinaryEncoding.Base64),
Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Child Account" = _t, #"Parent Account" = _t, Salary = _t, Nationality = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Child Account", type text}, {"Parent Account", type text}, {"Salary", Int64.Type}, {"Nationality", type text}}),
myfunction=(ChildCol,ParentCol,CurrentParent)=>
let
mylist=Table.Column(Table.SelectRows(ChangedType,each Record.Field(_,ChildCol)=CurrentParent),ParentCol),
result=Text.Combine(mylist)
in
Text.TrimEnd(
if result ="" then "" else @ result & "|" & @ myfunction(ChildCol,ParentCol,result),
"|"),
Path=Table.AddColumn(ChangedType,
"Path", each
Text.Trim(
Text.Combine(
List.Reverse(
List.RemoveItems(
Text.Split(myfunction("Child Account","Parent Account",[Parent Account]),"|"),{""}
)
)
&{[Parent Account],[Child Account]}
,
"|"),"|"))
in
PathHow could you use this code for your situation.
Easy. Just copy the myfunction step as above. In the Path step, just change the column names to the relevant column names in your table
Click here to download the pbix file
2) WHY USE “M” when we already have a DAX function
Will be posted soon