Home » DAX » DAX Parent & Child – PATH Function

DAX Parent & Child – PATH Function

Path DAX Function

The Path function returns a delimited text string with the identifiers of all the parents of the current identifier, starting with the oldest and continuing until current. Its comes under Parent and child functions.

Refer similar post:- PATHCONTAINS DAXPATHLENGTH DAX, PATHITEM DAX

Syntax:

PATH(<ID_columnName>, <parent_columnName>)



Description:

S no. Parameter Description
1 ID_columnName -The name of an existing column containing the unique identifier for rows in the table.
-This cannot be an expression.
-The data type of the value in ID_columnName must be text or integer, and must also be the same data type as the column referenced in parent_columnName.
2 parent_columnName -The name of an existing column containing the unique identifier for the parent of the current row.
-This cannot be an expression.
-The data type of the value in parent_columnName data type must be text or integer, and must be the same data type as the value in ID_columnName.

Note:

  • If ID_columnName is BLANK then PATH() returns BLANK.
  • If ID_columnName contains a vertical bar ‘|’ then PATH() returns an error.
  • This function is not supported for use in Direct Query mode




So let’s start with an example, sample dataset format as below:

Table Name – EmployeeData

EmpId Dept EmpName Parent Id
1 Manager Sandeep Raturi
2 Team Lead Antonio Moreno 1
3 Team Lead Aria Cruz 1
4 Team Lead Dinesh Raturi 1
5 Developer Matt 4
6 Developer Jhon 4
7 Developer Jacobs 2
8 Developer David 3
9 IT Support Jack 2

In above dataset we have EmpId, Dept, EmpName & Parent Id columns, and Parent Id column contains Employee manager Id.

Now PATH function help us to find the path that connects an employee to their manager.

Follow these steps in order to create Path-




Step-1: Create a New column.

Step-2: DAX formula screen appears, now write below DAX code.

Path = PATH(EmployeeData[EmpId],EmployeeData[Parent Id])

Step-3: Now you can see the hierarchy path of employees.

Path DAX

Path DAX

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

Example-1:  For EmpId 2, Parent Id is 1, so Path returns 1|2.

Example-2:  For EmpId 5, Parent Id is 4, here Path returns 1|4|5, that mean Id 5 manger is 4 & 4 manager is 1.

Refer this- How to get manager name from specified path?

Refer similar post:- PATHCONTAINS DAXPATHLENGTH DAX, PATHITEM DAX





Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our Contact form , we will revert to you asap.

 

Leave a Reply