Power BI DAX provides the Path(), PathLength(), PathItem() and PathItemReverse() Functions to help users manage data that is presented as a Parent-Child Hierarchy in their models.
With these functions a user can obtain the below information :
–the entire lineage of parents a row has,
–how many levels has the lineage to the top parent,
–who is the parent n-levels above the current row,
–who is the n-descendant from the top of the current row hierarchy and is certain parent a parent in the current row hierarchy or not.
Now we will discuss about each function in detail based on the following data modal.
Let us suppose we have a Parent-Child Hierarchy as follows..
From the above Parent-Child hierarchy, we observe the following things.
The Child Id “111” does not have a Parent Id.
The Child Id “112” have a Parent Id as “112”.
The Child Ids “114,115,116” have the Parent Id as “113”.
The Child Ids “117,118” have the Parent Id as “116”..etc.
PATH() function :
The Path() function returns a delimited text with the identifiers of all the Parents to the current row, starting with the oldest or top most to until current.
Syntax :
cPath = PATH(‘ParentChild'[Child_Id],’ParentChild'[Parent_Id])
Result:
PATHLENGTH() function :
The PathLength() function returns the number of levels in a given PATH(), starting at current level until the oldest or top most parent level.
In the following example column PathLength is defined as ‘=PATHLENGTH([cPath])’; the example includes all data from the Path() example to help understand how this function works.
Syntax :
cPathLen = PATHLENGTH(‘ParentChild'[cPath])
The PathItem() function returns the item at the specified position from a PATH() like result, counting from left to right.
In the following example column PathItem – 4th from left is defined as ‘=PATHITEM([cPath], 4)’; this example returns the Child_Id at fourth position in the Path string from the left, using the same sample data from the Path() example.
Syntax:
cPathItem = PATHITEM(‘ParentChild'[cPath],4,INTEGER)
Result:
PATHITEMREVERSE() function:
The PathItemReverse function returns the item at position from a PATH() like function result, counting backwards from right to left.
In the following example column PathItemReverse – 3rd from right is defined as ‘=PATHITEMREVERSE(c[Path], 3)’; this example returns the Id at third position in the Path string from the right, using the same sample data from the Path() example.
Syntax:
cPathItemRev = PATHITEMREVERSE(‘ParentChild'[cPath],3,INTEGER)
Output:
PATHCONTAINS() function:
The PathContains function returns TRUE if the specified item exists within the specified path. In the following example column PathContains – Id 123 is defined as ‘=PATHCONTAINS([cPath], “123”)’; this example returns TRUE if the given path contains Id 123. This example uses the results from the Path() example above.
Syntax:
cPathContains = PATHCONTAINS(ParentChild[cPath],”123″)
Output:
Note :
The above functions are Column functions, so that I used the suffix “c” for each column that defined like “cPath”.
——————————————————————————————————–
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
——————————————————————————————————–
Be the first to comment