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 this 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.
EmployeeKey |
ParentEmployeeKey |
112 |
|
14 |
112 |
3 |
14 |
11 |
3 |
13 |
3 |
162 |
3 |
117 |
162 |
221 |
162 |
81 |
162 |
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.
Function |
Description / Example |
||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Returns a delimited text with the identifiers of all the parents to the current row, starting with the oldest or top most until current. In the following example column ‘Path’ is defined as '=PATH(EmployeeKey, ParentEmployeeKey)'
|
|||||||||||||||||||||||||||||||||||||||||
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.
|
|||||||||||||||||||||||||||||||||||||||||
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.
|
|||||||||||||||||||||||||||||||||||||||||
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.
|
|||||||||||||||||||||||||||||||||||||||||
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.
|
Warning
In SQL Server 2012 Analysis Services, the xVelocity in-memory analytics engine (VertiPaq) does not support the definition of parent-child hierarchies; however, the DAX language provides a set of functions that allows users to explore parent-child hierarchies and to use these hierarchies in formulas.