Understanding functions for parent-child hierarchies in DAX

DAX provides five 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 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?

Parent-child functions in DAX

The following table contains a Parent-Child hierarchy on the columns: EmployeeKey and ParentEmployeeKey that is used in all the functions examples.

In the above table you can see that employee 112 has no parent defined, employee 14 has employee 112 as manager (ParentEmployeeKey), employee 3 has employee 14 as manager and employees 11, 13, and 162 have employee 3 as manager. The above helps to understand that employee 112 has no manager above her/him and she/he is the top manager for all employees shown here; also, employee 3 reports to employee 14 and employees 11, 13, 162 report to 3.

The following table presents the available functions, a brief description of the function and an example of the function over the same data shown above.

PATH function - Returns a delimited text with the identifiers of all the parents to the current row, starting with the oldest or top most until current.

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([Path])'; the example includes all data from the Path() example to help understand how this function works.

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([Path], 4)'; this example returns the EmployeKey at fourth position in the Path string from the left, using the same sample data from the Path() example.

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([Path], 3)'; this example returns the EmployeKey at third position in the Path string from the right, using the same sample data from the Path() example.

PATHCONTAINS function - Returns TRUE if the specified item exists within the specified path. In the following example column PathContains - employee 162 is defined as '= PATHCONTAINS([Path], "162")'; this example returns TRUE if the given path contains employee 162. This example uses the results from the Path() example above.