PATHREVERSE function DAX

PATHREVERSE 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 backwards from right to left.



It is same like a PATHITEM function, but the only difference is that PATHREVERSE function counts the position of Item from right to left while PATHITEM function counts the position of Item 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. Position is counted backwards from right to left.

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

PATHREVERSE 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 PATHREVERSE function which returns the 4th level of hierarchy as text for each row, counting backwards from right to left.

It returns blank, If there is no 4th level available.

PathReverse = PATHITEMREVERSE(CompanyHierarchy[Path], 4, TEXT)

 

Once you commit the DAX, drag the PathReverse column measure into table visual next to Path field.

You can see, PATHREVERSE function returns the 4th level of hierarchy for each rows, positions are counted from right to left. It returns blank when 4th level hierarchy is not available for any path.

Also Read..

PATH function

PATHLENGTH function

PATHITEM function

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




 69 total views,  5 views today

Leave a Reply

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