cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

Hierarcal Problem with two columns to determine Top level

Given:

I have Two columns as shown below as shown below

 Employee ID Manager ID E068 E067 E071 E067 E229 E069 E248 E144 E226 E223 E236 E241 E066 E001 E067 E001 E144 E001 E223 E001 E069 E066

Problem Statement:

This problem is to identify the Head of Manager by using Employee and their Manager data.

We have an Employee ID and their Manager ID. Please note that Manager ID are from Employee ID. Since each manager has one Manager above their level.

STEPS:

1. First, we'll take all UNIQUE ID in Manager ID column.
2. Then for each ID from Manager ID column, we will look for their respective Manager ID(Manager)
3. Then we will create a new column say `Level 1` we will put manager for each Manager ID on their respective cell.
4. Similarly, we will repeat the above 3 processes again till there is no Manager ID for that particular ID.
5. This way we can identify the Head of Manager.

I am able to solve the problem in EXCEL.
By using =IFERROR(VLOOKUP(C2,\$A:\$B,2,FALSE),"")

But this approach lead me to create new column in excel for each level hierarchy. And putting the formula on first cell of that particular column and then dragging the result for each manager

But incase of big companies there would be **n** no. of level of hierarchy. So creating the new column in excel for each level of hierarchy would be time consuming task. Hence, I am looking for an optimal solution.

Expected Output:

 Employee ID Manager ID Level 1 Level 2 Head of Manager E068 E067 E001 E001 E071 E067 E001 E001 E229 E069 E066 E001 E001 E248 E144 E001 E001 E226 E223 E001 E001 E236 E241 E241 E066 E001 E001 E067 E001 E001 E144 E001 E001 E223 E001 E001 E069 E066 E001

The Employee ID column contain **UNIQUE ID** while Manager ID contain **DUPLICATES ID**.

Thank you for your time and consideration.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @maqsud

Create 4 calculated columns as below:

``````Column =
var _employee=CALCULATE(MAX('Table'[Manager ID]),FILTER('Table','Table'[Employee ID]=EARLIER('Table'[Employee ID])))
Return
CALCULATE(MAX('Table'[Manager ID]),FILTER('Table','Table'[Employee ID]=_employee))``````
``````Level 1 =

IF('Table'[Manager ID] in FILTERS('Table'[Column]),BLANK(),'Table'[Column])``````
``````Level 2 =
var _managerID= CALCULATE(MAX('Table'[Manager ID]),FILTER('Table','Table'[Column]<>BLANK()&&'Table'[Level 1]=BLANK()))
var _column=CALCULATE(MAX('Table'[Column]),FILTER('Table','Table'[Column]<>BLANK()&&'Table'[Level 1]=BLANK()))
Return
IF('Table'[Level 1]=_managerID,_column,BLANK())``````
``Head of Manager = IF('Table'[Level 2]<>BLANK(),'Table'[Level 2],IF('Table'[Level 2]=BLANK()&&'Table'[Level 1]<>BLANK(),'Table'[Level 1],IF('Table'[Level 2]=BLANK()&&'Table'[Level 1]=BLANK(),IF('Table'[Column]<>BLANK(),'Table'[Column],'Table'[Manager ID]))))``

Finally you will see:

Best Regards,
Kelly
2 REPLIES 2
Anonymous
Not applicable

Hi @maqsud

Create 4 calculated columns as below:

``````Column =
var _employee=CALCULATE(MAX('Table'[Manager ID]),FILTER('Table','Table'[Employee ID]=EARLIER('Table'[Employee ID])))
Return
CALCULATE(MAX('Table'[Manager ID]),FILTER('Table','Table'[Employee ID]=_employee))``````
``````Level 1 =

IF('Table'[Manager ID] in FILTERS('Table'[Column]),BLANK(),'Table'[Column])``````
``````Level 2 =
var _managerID= CALCULATE(MAX('Table'[Manager ID]),FILTER('Table','Table'[Column]<>BLANK()&&'Table'[Level 1]=BLANK()))
var _column=CALCULATE(MAX('Table'[Column]),FILTER('Table','Table'[Column]<>BLANK()&&'Table'[Level 1]=BLANK()))
Return
IF('Table'[Level 1]=_managerID,_column,BLANK())``````
``Head of Manager = IF('Table'[Level 2]<>BLANK(),'Table'[Level 2],IF('Table'[Level 2]=BLANK()&&'Table'[Level 1]<>BLANK(),'Table'[Level 1],IF('Table'[Level 2]=BLANK()&&'Table'[Level 1]=BLANK(),IF('Table'[Column]<>BLANK(),'Table'[Column],'Table'[Manager ID]))))``

Finally you will see:

Best Regards,
Kelly
Super User
try using the path function to solve this

https://docs.microsoft.com/en-us/dax/understanding-functions-for-parent-child-hierarchies-in-dax

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.