Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
maqsud
Helper III
Helper III

Hierarcal Problem with two columns to determine Top level

Given:

I have Two columns as shown below as shown below

 

Employee IDManager ID
E068E067
E071E067
E229E069
E248E144
E226E223
E236E241
E066E001
E067E001
E144E001
E223E001
E069E066


Problem Statement:

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


About:

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 IDManager IDLevel 1Level 2Head of Manager
E068E067E001 E001
E071E067E001 E001
E229E069E066E001E001
E248E144E001 E001
E226E223E001 E001
E236E241  E241
E066E001  E001
E067E001  E001
E144E001  E001
E223E001  E001
E069E066  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:

Annotation 2020-06-10 172154.png

For the related .pbix file,pls click here.

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

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:

Annotation 2020-06-10 172154.png

For the related .pbix file,pls click here.

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
vanessafvg
Super User
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!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors