cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
phaneendra
Regular Visitor

Parent/Child Hierarchy with duplicates in Employee ID

Hi everyone,

 

This is my first post in the community and i am super excited.

I have a data table as follows

Employee_IDManager_ID
1 
21
32
43
54
65
76
87
98
109
91
102


There are cases where some employees has more than 1 reporting manager. For instance Employee 9 reports to two managers 1 & 8. Likewise, Employee 10 has reports to managers 9 & 2.

 

This is my output when there are no duplicates in the employee ID`s

phaneendra_0-1653027961123.png

 

But, because of duplicate employee ID`s, this is the error I face

 

phaneendra_1-1653028071194.png

 

Please help me in resolving this error with the data sample data set i`ve shown above.

 

Really appreaciate Your support! 

Thanks in advance!!

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
JYA
Frequent Visitor

Hi,

You should be able to add a first column of this kind : 

Employee_UMID =
var employeeID = Employees[Employee_ID]
var managerID = Employees[Manager_ID]
var sameEmployeeID = SUMMARIZE(FILTER(Employees,Employees[Employee_ID]=employeeID),Employees[Employee_ID],Employees[Manager_ID])
var rk = RANKX(sameEmployeeID,[Manager_ID],managerID,ASC,Dense)
return employeeID & if(rk>1, "_" & rk,"")
 
As it's returning a text, you need to change Manager_ID as text column (or apply a number you are sure will not be duplicate instead),
then you should be able to apply your path : 
Column =
var p = PATH(Employees[Employee_UMID],Employees[Manager_ID])
var m = SEARCH("_",p,1,LEN(p)+1)
var r = LEFT(p,m-1)
return r
 
JYA_0-1653386422341.png

 

 

View solution in original post

7 REPLIES 7
JYA
Frequent Visitor

Hi,

You should be able to add a first column of this kind : 

Employee_UMID =
var employeeID = Employees[Employee_ID]
var managerID = Employees[Manager_ID]
var sameEmployeeID = SUMMARIZE(FILTER(Employees,Employees[Employee_ID]=employeeID),Employees[Employee_ID],Employees[Manager_ID])
var rk = RANKX(sameEmployeeID,[Manager_ID],managerID,ASC,Dense)
return employeeID & if(rk>1, "_" & rk,"")
 
As it's returning a text, you need to change Manager_ID as text column (or apply a number you are sure will not be duplicate instead),
then you should be able to apply your path : 
Column =
var p = PATH(Employees[Employee_UMID],Employees[Manager_ID])
var m = SEARCH("_",p,1,LEN(p)+1)
var r = LEFT(p,m-1)
return r
 
JYA_0-1653386422341.png

 

 
phaneendra
Regular Visitor

Hi JYA,

 

Thank you so much!!! 

works like a charm.  

Much appreciated!!!!!

v-henryk-mstf
Community Support
Community Support

Hi @phaneendra ,

 

Whether the advice given by @CNENFRNL  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.


Looking forward to your feedback.


Best Regards,
Henry

CNENFRNL
Super User
Super User

From whether technique or business perspective, it's bizarre that one employee is situated at different levels of the same organizational hierarchy. If he/she indeed plays different roles, assign different IDs to him/her. Or, a straightforward solution is to create another funcitonal hierarchy.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

Hi,

 

Thanks for taking time to investigate!

Let me explain the scenario. An employee was reporting to manager-A, Later in that month for some reasons the employee starts reporting to Manager-B. The system is taking time to update, so at times we get duplicate records. In such cases I face this issue in DAX.

 

Hope this brings in more light!!  

As to me, tt's kind of "slowly changing dimension" case.

Like I said, assign different to the employee and add different records in the Employee dimension table accordingly.

CNENFRNL_0-1653040200048.png

 

There are certain instrinsic constaints. Your case is inherently restricted. Refer to the official document,

PATH function (DAX) - DAX | Microsoft Docs

  • If ID_columnName has duplicates and parent_columnName is the same for those duplicates then PATH() returns the common parent_columnName value; however, if parent_columnName value is different for those duplicates then PATH() returns an error. In other words, if you have two listings for the same employee ID and they have the same manager ID, the PATH function returns the ID for that manager. However, if there are two identical employee IDs that have different manager IDs, the PATH function returns an error.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

Hi,

 

Thank you for your inputs. Yes you are right. But, our system doesnt permit us to alter the employee ID. 

However, I was able to find a solution using M-Query which resolved the issue. I am trying to replicate the same in DAX without using PATH function. Since, PATH function doesnt accept duplicates in Employee_ID.

 

Thanks for your time!! 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors