Understanding Functions for Parent-Child Hierarchies
Collapse the table of content
Expand the table of content

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.

EmployeeKeyParentEmployeeKey
112
14112
314
113
133
1623
117162
221162
81162

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 (DAX) - Returns a delimited text with the identifiers of all the parents to the current row, starting with the oldest or top most until current.

EmployeeKeyParentEmployeeKeyPath
112112
14112112|14
314112|14|3
113112|14|3|11
133112|14|3|13
1623112|14|3|162
117162112|14|3|162|117
221162112|14|3|162|221
81162112|14|3|162|81

PATHLENGTH Function (DAX) - 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.

EmployeeKeyParentEmployeeKeyPathPathLength
1121121
14112112|142
314112|14|33
113112|14|3|114
133112|14|3|134
1623112|14|3|1624
117162112|14|3|162|1175
221162112|14|3|162|2215
81162112|14|3|162|815

PATHITEM Function (DAX) - 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.

EmployeeKeyParentEmployeeKeyPathPathItem - 4th from left
112112
14112112|14
314112|14|3
113112|14|3|1111
133112|14|3|1313
1623112|14|3|162162
117162112|14|3|162|117162
221162112|14|3|162|221162
81162112|14|3|162|81162

PATHITEMREVERSE Function (DAX) - 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.

EmployeeKeyParentEmployeeKeyPathPathItemReverse - 3rd from right
112112
14112112|14
314112|14|3112
113112|14|3|1114
133112|14|3|1314
1623112|14|3|16214
117162112|14|3|162|1173
221162112|14|3|162|2213
81162112|14|3|162|813

PATHCONTAINS Function (DAX) - 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.

EmployeeKeyParentEmployeeKeyPathPathContains - employee 162
112112FALSE
14112112|14FALSE
314112|14|3FALSE
113112|14|3|11FALSE
133112|14|3|13FALSE
1623112|14|3|162TRUE
117162112|14|3|162|117TRUE
System_CAPS_ICON_warning.jpg Warning


In Entity with relative path '../Token/ssSQL11_md.md' can not be found, for source topic '{"project_id":"2c70878e-0e37-40e8-a86a-f8a09fa56109","entity_id":"b11f0cff-cee4-4ae7-a5b3-ebe288fc42d3","entity_type":"Article","locale":"en-US"}'. 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.

Community Additions

ADD
Show:
© 2016 Microsoft