Home » PATH Function DAX

PATH Function DAX

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..

PATHLENGTH

RELATED

USERRELATIONSHIP

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 1,447 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.