PATHLENGTH function is a Power BI parent and child function in DAX, which returns the number of parents to the specified item in a given PATH result, including the specified item.
SYNTAX
PATHLENGTH(<path>)
Path
Is a text expression resulting from evaluation of a PATH function.
Lets see the PATHLENGTH 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 |
PATHLENGTH function requires a specified PATH which is evaluated by PATH Function as an input then only it returns the number of parents to the specified item in a given PATH result.
Lets get the hierarchy of employees using PATH Function, for this first we create a calculated column that will return 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, and 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.
So far, we have created a hierarchy of employees that is returned by calculated column PATH.
Now we will use a PATHLENGHT function that will return the number of parents to the specified item in a given PATH result, including the specified item.
Lets create a calculated column named PathLength as shown below.
Following DAX measure, Uses a calculated column PATH which returns the result evaluated by PATH Function, that is taken by PATHLENGTH function as an input.
PathLength = PATHLENGTH(CompanyHierarchy[Path])
Lets commit the DAX, and drag the PathLength column measure into table visual just next to PATH column as shown below.
As you can see, it returns the number of parents to the specified item in a given PATH result, including specified item.
Also Read..
