Friday, August 16, 2019

How to use the Parent Child Hierarchy Functions in Power BI DAX

How to use Path(), PathLength(), PathItem() and PathItemReverse() Functions in Power BI
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])

Result:
PATHITEM() function:
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 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.