Power BI – PATH function for Parent-child Hierarchies in DAX

PATH function returns a delimited text which contains all the parents to the current row, starting with the oldest or topmost until current. In other words, the PATH function is used to return the items that are related to the current row value. In our example, on a table that contains employees, the managers of employees, and the managers of the managers, you can return the path that connects an employee to his or her manager.

Syntax

[dax]

PATH(Child_columnName, Parent_columnName)

[/dax]

Child_columnName: The name of an existing column containing the unique identifier for rows in the table.

• Child_columnName cannot be an expression.
• The data type must be text or integer
• Child_columnName must also be the same data type as the column referenced in Parent_columnName.

Parent_columnName: The name of an existing column containing the unique identifier for the parent of the current row.

• Parent_columnName cannot be an expression.
• The data type must be text or integer
• Parent_columnName must also be the same data type as the value in Child_columnName.

Return value

• A delimited text string containing the identifiers of all the parents to the current identifier.

Some interesting facts about PATH function are below

• • The delimiter used to separate the ascendants is the vertical bar, ‘|’.
• • The path is not tied to a single level of parent-child relationships. It can return related rows that are several levels up from the specified starting row.
• • The values in Child_columnName and Parent_columnName must have the same data type, text or integer.
• • Values in Parent_columnName must be present in Child_columnName. That means you cannot look up a parent if there is no value at the child level.
• • If Parent_columnName is BLANK then PATH() returns Child_columnName value. For example, if you look for the manager of an employee but the Parent_columnName column has no data, the PATH function returns just the employee.
• • If Child_columnName has duplicates and Parent_columnName is the same for those duplicates then PATH() returns the common Parent_columnName value; however, if Parent_columnName value is different for those duplicates then PATH() returns an error. For example, if you have two rows for the same employee and they have the same manager, the PATH function returns that manager. However, if two identical employees have different managers, then the PATH function returns an error.
• • If Child_columnName is BLANK then PATH() returns BLANK.
• • If Child_columnName contains a vertical bar ‘|’ then PATH() returns an error.

PATH function is not supported for use in DirectQuery mode. You can get more information about limitations in DirectQuery models here.

Let us look at an example of using the PATH function. Consider the Employee table in Adventureworks Database where data organized in a parent-child relationship logically by using fields EmployeeKey and Parent EmployeeKey.

Let us add a Calculated column to this table which uses the PATH function to calculate the Hierarchy.

[dax]

Hierarchy = PATH ( EmployeeHierarchy[Name], EmployeeHierarchy[Parent])

[/dax]

As you can see, the PATH function returns a delimited string that contains all the parents to the current row.