Hierarchy of employees

Whether we use Excel BI or Power BI data model, we can create a hierarchy of employees manually or with the help of some DAX functions. In this text you will learn how to use two such functions: PATH and PATHITEM. With their help, it is possible to create calculated columns that show the hierarchical level of each of the employees within the company.

We start from one simple table which consists of the employee ID, the name (IME I PREZIME) and the code of the manager (NADREĐENI). The first and third columns should be of the same type. We will turn it into a data table and then, by clicking on the Add to Data Model icon, add it to the data model.

First we need to create a calculated column that shows the HIJERARHIJA. For this purpose, we will use the formula within which the PATH function is located:

=PATH([ID],[NADREĐENI])

The PATH function has two arguments. The first is the ID of the worker, and the second is the ID of his manager. Instead of workers, we could define a hierarchy of subordinate/superior organizational parts, products, services … In our example, the formula will return a path that shows at which hierarchical level the employee is and who are all his superiors.

If we want to specifically find out whether an employee belongs to a certain level of the hierarchy, we will write the formula:

=PATHITEM([HIJERARHIJA],1,1)

This function has three arguments. The first is a path that we have just created. The second argument is the number that indicates the hierarchical level, and the third is the parameter that indicates whether the result in the column should be text or number. After creating this formula for each of the levels, we will be able to have a clear insight into which of the employees belongs to which level of the hierarchy.