PATH, PATHLENGTH, PATHITEM, PATHITEMREVERSE, PATHCONTAINS – playing whith hierarchies (DAX – Power Pivot, Power BI)

This article describes functions that can be used for managing of hierarchical tables. I mean tables having some items in one column and their “superiors” in another.

In these tables:

  • Values in first column have to be unique
  • Values in the second column don´t have to be uniqe – since an item can be superior to multiple items.
  • Values can be found in both first and second column – since some item can be superior to another and having superior at the same time.

The typical example of hierarchical table is a table describing management structure in a company – where every employee has his direct supervisor assigned.

This structure can be graphically described like this:

So which functions can be used to play with this hierarchy?

PATH

PATH displays all knots / levels above the item. In a company it is list of managers that are above some employee – his supervisor, his supervisors supervisor, his supervisors supervisors supervisor… Names will be separated by a vertical bar.

There are two arguments – the column with unique items and column with their supervisors.

PATHLENGTH

PATHLENGTH shows, how many levels is “above” item. In this company it shows how many managers is in a hierarchy above (plus 1).

The biggest boy in a company, that has no supervisor, has number one, his subordinate is number two etc.

PATHLENGTH has one input – output of previous PATH function. So, generally, PATHLENGTH can´t be used without PATH.

PATHITEM

PATHITEM function finds an item in some level above. It is counted from right (from top).

So, for example, with argument “1”, it shows the highest person in hierarchy for everybody.

So the input of PATHITEM is the output of PATH and number defining the level from top that should be displayed.

PATHITEMREVERSE

PATHITEMREVERSE works similarly to PATHITEM, but counts positions from left (from bottom). So with 1 as a second parameter it displays the direct supervisor.

PATHCONTAINS

PATHCONTAINS verify, whether there is the specified item anywhere above in the hierarchy. 

The first argument of PATHCONTAINS is a path, the second is the searched item.

The file can be downloaded from here.

hierarchy