Path function is a Power BI Parent and Child Functions in DAX, which returns a delimited text string with the identifiers of all the parents of the current identifier, starting with the oldest and continuing until current.
SYNTAX
PATH(<ID_columnName>, <parent_columnName>)
ID_columnName
Is the name of an existing column which contains the unique identifier for rows in the table. It cannot be an expression.
The data type of ID_columnName must be text or integer, and must also be the same data type as the column referenced in parent_columnName.
parent_columnName
Is the name of an existing column which contains the unique identifier for the parent of the current row. It cannot be an expression.
The data type of parent_columnName must be text or integer, and must be the same data type as the value in ID_columnName.
Some key points on PATH function as follows:
- The values in ID_columnName and parent_columnName must have the same data type, either text or integer.
- Values in parent_columnName must be exist in ID_columnName.
- The delimiter used to separate the ascendants is the vertical bar, ‘|’.
- PATH function returns BLANK for BLANK ID_columnName.
- PATH function returns an error, if ID_columnName contains a vertical bar ‘|’.
- If ID_columnName has duplicates and parent_columnName is the same for those duplicates then PATH function returns the common parent_columnName value. However, PATH function returns an error, if parent_columnName value is different for those duplicates.
Lets see the PATH function in action, here we have a sample data named CompanyHierarchy, which contains employee and whom they have to report or you can say their reporting manager.
| EmpId | ReportingToID | Employee | ReportingTo |
| 1 | 1 | CEO | CEO |
| 2 | 1 | VP | CEO |
| 3 | 2 | AVP | VP |
| 4 | 3 | BD1 | AVP |
| 5 | 3 | BD2 | AVP |
| 6 | 4 | SM1 | BD1 |
| 7 | 5 | SM2 | BD2 |
| 8 | 6 | TL1 | SM1 |
| 9 | 6 | TL2 | SM1 |
| 10 | 7 | TL3 | SM2 |
| 11 | 8 | SE1 | TL1 |
| 12 | 8 | SE2 | TL1 |
| 13 | 9 | SE3 | TL2 |
| 14 | 9 | SE4 | TL2 |
| 15 | 9 | SE5 | TL2 |
| 16 | 11 | DEV1 | SE1 |
| 17 | 12 | DEV2 | SE2 |
Lets create a calculated column that will show the employee hierarchy means all the managers for each employee.
Go to Fields pane, right click on table CompanyHierarchy and select New column.
Now write following DAX, which uses path function which takes two parameters, for first parameter you need to provide the child column that will be Employee and in second parameter you need to provide a parent column that would be a reporting manager of employee’s that is ReportingTo.
Path = PATH ( CompanyHierarchy[Employee], CompanyHierarchy[ReportingTo] )
Lets commit the Dax, you will see that a new column is added to table.
Lets see the output of Path function, drag the Path column on table visual as shown below.
You can see, Path function creates a hierarchy of employees, that is a lists of all the managers for each employee.
Also Read..
5,620 total views, 2 views today