Finding managers in organizational hierarchies

Organizational hierarchies are one of the most common hierarchies in organizational data. However, finding managers in such hierarchies can be challenging. Let’s look at how we can do that.

Introduction

First things first — Why is an organizational hierarchy something special?

Because it is an unbalanced hierarchy.

Unbalanced hierarchies are hierarchies in which the leaf element is not always at the same distance from the root element.

The product hierarchy in the Contoso data model is balanced because it always follows the same path: Product Categories à Product Subcategories à Products.

Consequently, the Product is always at the same distance from the root (Product Category).

A typical unbalanced hierarchy is an organizational hierarchy.

A few examples:

• The assistant to the CEO is 1 level below the top element (the CEO).
• A team leader is below a manager. And the manager is below the CEO. So, two levels.
• A team member is below a team leader. A team leader is below a manager. And the manager is below the CEO. He is three levels below the CEO.

The best example of an unbalanced hierarchy is a tree, where each leaf has a different distance from the trunk.

With this analogy, you can understand why each person in an organizational hierarchy is a “leaf.”

You can skip the following section if you are familiar with parent-child hierarchies.

The data

Usually, such a hierarchy is stored as a parent-child hierarchy.

Each element has an additional column, which points to his manager.

Look at the following table:

You can see that everyone with the ID 1091 in the column Mgr is a subordinate of Benedict Jones, the company’s CEO.

And everyone with ID 1025 in the column Mgr is a subordinate of Andrea Hudson.

This is how a parent-child hierarchy is stored with the least space.

Flattening the hierarchy

Unfortunately, Power BI cannot work with parent-child hierarchies.

This means we must flatten the parent-child hierarchy to a classic column-oriented hierarchy.

To transform the parent-child hierarchy into a classic hierarchy, I follow the methods described by Data Mozart:

For all levels after the first, I added logic to make sure that each column is filled:

Level2 = IF( 'Org'[Path Length] >= 2
          ,LOOKUPVALUE(Org[Name]
                ,'Org'[ID]
                ,PATHITEM('Org'[Path]
                ,2,INTEGER) )
          ,'Org'[Level1]
          )

This logic has the effect of copying the Name into the current column when an element in the hierarchy has no child below.

The result is the following table (extract of the entire table):

When you look at Mandy Davidson, you can see that each level is different.

But when you look at Carol Davidson, you can see she is at the fourth level. No further subordinate exists, so the name is copied to the fifth level.

The same applies to Carol Miller, located at the organization’s second level.

This is the best practice when modeling such kind of data.

Anyway, SQLBI has written an article on how to avoid this multiplication of Names and how to write Measures to cope with them:

It’s an interesting approach. I need to explore this method in more depth very soon.

When we add the hierarchy to a Slicer, it looks like this:

As you can see, Andrea Adams is shown as a node and as a Person in the hierarchy. This is correct in this case, as we will have data assigned to Andrea Adams while she is a Manager, and we want to see an aggregation of the data assigned to the part of the organization for which she is the manager.

This might be different for other types or hierarchies.

In this case, you might use the Custom Visual “Hierarchy Slicer,” which can hide elements with the same name as the parent.

Finding all Managers (and non-Managers)

Now that we have the data prepared, we can use it in Power BI to ask some questions.

The first is to find all the Managers in the organization.

Vice-versa is interesting as well: Who is not a Manager?

To answer this question, we need one more calculated column.

My first approach was to use a variant proposed by one of my colleagues using the EARLIER() function:

Is Manager (V1) =
IF (
    CALCULATE (
        COUNTROWS ( 'Org' )
        ,ALL ( 'Org' )
        ,'Org'[Mgr] = EARLIER ( 'Org'[ID] )
    ) = 0
    ,"Not Manager"
    ,"Manager"
)

This function uses context transition to search the entire table for rows with the ID of the current row in the Mgr column.

If you don’t know the concept of context transition, read my article on this topic:

While exploring this approach, I noticed that the EARLIER() function is marked with a “Not recommended” label on DAX.guide, and there is a performance warning on the Microsoft page for EARLIER().

For this reason, I developed my version of this column, which looks much more efficient:

Is Manager (V2) =
VAR PersNo = 'Org'[ID]
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Org' )
            ,REMOVEFILTERS ( 'Org' ) // Only rows with the actual Person in the hierarchy
            ,PATHCONTAINS (
                Org[Path]
                ,CONVERT (
                    PersNo
                    ,STRING
                )
            )
        ) > 1
        ,"Manager"
        ,"Not Manager"
    )

As recommended by DAX.guide, I use a Variable to store the current row context. Then, I still use context transition to search for rows with the ID of the current row in the Path columns.

If I find more than one row with the actual ID in the Path column, then the Person is a Manager.

Each Person is contained at least once in the Path column. Therefore, I must find the IDs with multiple occurrences to find the Managers.

The result is identical; I use the same logic but a different technique.

Moreover, this formula is easier to understand than the previous one.

The list of Managers is the following:

Finding Managers of Managers

This question is more complicated to answer.

In this case, I must find all Managers with subordinates who are also Managers.

I start with the same pattern as before, but I need one more check to do:

Is Manager of Manager =
VAR PersNo = 'Org'[ID]
VAR PathLen = 'Org'[Path Length]
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Org' ),
            REMOVEFILTERS ( 'Org' ) // Only rows with the actual Person in the hierarchy
            ,PATHCONTAINS (
                Org[Path],
                CONVERT (
                    PersNo,
                    STRING
                )
            )
                && // Only Rows which are not for the actual Person
            Org[ID] <> PersNo
                && // Check for Manager of Manager
                // Look at the level below --> If it is not empty, it should be different from the next level
                PATHITEM (
                    Org[Path],
                    PathLen + 1
                )
                    <> IF (
                        PATHITEM (
                            Org[Path],
                            PathLen + 2
                        ) = "",
                        PATHITEM (
                            Org[Path],
                            PathLen + 1
                        ),
                        PATHITEM (
                            Org[Path],
                            PathLen + 2
                        )
                    )
        ) > 0,
        "Is Manager of Manager",
        IF (
            'Org'[Is Manager (V2)] = "Manager",
            "No Manager of Manager",
            "Not Manager"
        )
    )

To find all Manager of Managers, I must check all rows to get all rows with the current ID in the Path but with rows with one more ID in the Path.

For example:

• Mandy Ho has the ID 1235
• Now I search all rows with this ID in the Path, which also has one more level in the Path. For example, Andrea Jones (ID: 1037), who is the Manager for Andrea Miller (ID: 1049)

Therefore, Mandy Ho is a Manager of Andrea Jones, who is also a manager.

Again, the approach to storing the current row context is critical to solving the question “Who is a Manager of Managers”.

Check the result for “Manager of Managers”

To check the result of this calculated column, I follow this process:

• I get the list of all “Manager of Manager” and copy the result into Excel

• Then, I check the data in the Table view of Power BI Desktop, filtering the column Path with the ID of each Entry (Contains “|1235|”:

These two rows prove that Mandy Ho is indeed a Manager of Managers.

We get no result Only for Benedict Jones, as he is at the top level and has no lead separator (“1091|”) in the Path column.

I can perform the negative test by checking all Managers, which are “No Manager of Managers,” in the same way.

Conclusion

While unbalanced hierarchies are often used with Power BI, answering the questions above can be challenging if we don’t know the proper techniques, like context transition.

Even after building the result, we must know how to test the results.

It would be fatal for our users to accept our report if they discover discrepancies in the results.

Another lesson I learned while writing this piece was to try to understand how a piece of code works that I didn’t write myself.

The first attempt to get the list of managers worked, but it had two issues:

• I couldn’t fully understand how it works.
• It was flawed with performance issues.

So, I decided to create my version to solve the problem, which I fully understand and can explain in detail.

This is the third, and for now, the last installment of my series on how to work with hierarchies in DAX.

The previous two are the following:

Please let me know in the comments if you have any questions or challenges with hierarchies, which I can cover in a future article.

References

The data is self-generated with phantasy names.

I generated this complete list by multiplying a list of First- and Last-Names with each other.

Working with Parent-child hierarchies on DAX-Patterns:

Understanding Parent-child Hierarchies on Data Mozart:

Don’t forget to subscribe to

👉 Power BI Publication

👉 Power BI Newsletter

and join our Power BI community

👉 Power BI Masterclass