PATHITEM function is a Power BI parent and child function in DAX which returns the item at the specified position from a string resulting from evaluation of a PATH function. Positions are counted from left to right.
SYNTAX
PATHITEM(<path>, <position>[, <type>])
path
is a text string in the form of the results of a PATH function.
position
is an integer which denotes the position of the item to be returned. Positions are counted from left to right.
type
(Optional) which defines the data type of the result , it can be either TEXT (0) or INTEGER (1).
TEXT or 0 : (default). It returns result with the data type text.
INTEGER or 1 : It returns result as an integer.
Lets take look on PATHITEM function, 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 |
PATHITEM function requires a string which is evaluated from PATH function as first argument, so for that first you need to get a PATH.
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.
Lets create a calculated column which uses the PATHITEM function which returns the 5th level of hierarchy as text for each row.
It returns blank, If there is no 5th level available.
PathItem = PATHITEM(CompanyHierarchy[Path], 5, TEXT)
Once you commit the DAX, drag the PathItem column measure into table visual next to Path field.
You can see, PATHITEM function returns the 5th level of hierarchy for each rows, positions are counted from left to right. It returns blank when 5th level hierarchy is not available for any path.
Also Read..
3,552 total views, 1 views today