Creating a user hierarchy from a manager column to use as hierarchy slicer

[lennythelucker]

Hi All,

I am breaking my head on this for several days now, so time to share this in the hope to get some new idea's / solution

I like to build a user hierarchy based on 1 column, The Manager column and use it in a hierarchy slicer,

I also want to connect this to the countries / regions slicer, so if you filter on a region, you see only the hierarchy of that region

I only want to get the hierarchy in place for the salesusers in the database

If I select a salesmanager, I want to see his data + all people in the team (under the SM in the hierarchy)

herunder an example of what I am trying.

hope somebody has an idea how to handle this,

thanks!

[v-shex-msft · Accepted solution]

Hi @lennythelucker,

You can try to use path and pathitem function to get the role hierarchy.

Steps:

1. Calculate column to analysis the role relationship:

Path = PATH(Role[Full Name],Role[Manager])

2. Add calculated columns to store the analysis relationship to original table:

First = PATHITEM([Path],1,0)
Second = PATHITEM([Path],2,0)
Third = PATHITEM([Path],3,0)
Fourth = PATHITEM([Path],4,0)

3. Create custom hierarchy with above columns.

Notice: sample table.

Regards,

Xiaoxin Sheng

[lennythelucker]

THANK YOU, THANK YOU, THANK YOU!!!!!

WOW!!

I already tried step 1, but then went in the direction of a calculated table and got lost in my logic 🙂

This works perfectly, I am soooo happy

I didn t know this pathitem formula, really cool

again, thank you so so so so soooo much!!!!