Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I want to create a new column based on the following logic:
this is how my dataset looks:
Id -> Emp Name's ID
Emp Name -> Employee Name
Mgr Name -> The Employees Manager
Apart from these 3 I have an additional column called Level (I was not sure how to put that as a column here for a mock dataset, so going to explain that in words)
So if Emp Name Law is the topmost, his direct reports say in this case Tra or Eri would be level 1. If you take Emp Name Meli, she is level 1 to her manager Tra, but her level = 2 for Law (Because Meli reports to Tra (Level 1) who reports to Law (Hence Level 2)
So I want to create a new column called Parent_ID based on the following logic:
Basically Parent ID is the manager's ID of the employee, but those ID's whose level =1 (The dataset already has these level numbers, so the DAX just needs this to be mentioned).
For example, You can see that Emp NameMeli (ID =4) reports to Mgr Tra (ID =2) So the Parent_ID column for Meli is 2 (Law would be a manager for her as well, but Law' ID does not become her Parent ID). But my dataset is so huge so I wanted to know if there is an easy way to do this. Similarly Emp Name Haz (ID=6) who reports to Tra also has Parent_ID =2
Another example would be Emp Name Chr (ID=7) reports to Haz(ID = 6) so Chr would have parent ID = 6.
Is there any way this can be done? The level part was just to describe how it works. But in my huge dataset i already have a level mapping with these managers. But I need the logic of the DAX for the new column to account for this such that they only get Parent_ID of their direct managers and not the indirect ones. Appreciate the help!
Solved! Go to Solution.
@Anonymous ,
This should do it, although you may want to combine the columns. I left them separate for ease in debugging. First we combine the two columns [ID] and [Emp Name] into [EmpCombo] separated by ",". (This gives us a unique identifier for all. )Then we use IF() and LOOKUPVALUE () in [SelectEmpCombo. That gives us the answer, but then we need to separate out the Emp ID.
So, we use IF() and FIND() in [FindDelimiter] to find how many characters the ID is. Finally we use IF() and LEFT() to get the ParentID.
If this solves your issue, please consider marking it Solved. KUDOS are nice too!
Proud to be a Super User!
Hi @Anonymous ,
If above suggestion provided by @Nathaniel_C does work, please kindly mark it as solution to help others find it more quickly. thanks!
Hi @Anonymous
Here is your original data table with a calculated column add [ParentID] The blanks are where we don't have data in the [Emp Name] column for the manager's name.
I had to copy the first two columns and create a second table (EMPID) in order to do the following DAX.
Proud to be a Super User!
@Nathaniel_C Hey thank you for your respone.
The level example is as follows: If emp X reports directly to Mangager Y, then X's level for Y is level 1 (Because she directly reports).
Now this Manager Y itself is an employee who has manager Z. So Y's level 1 is person Z but X reports to Y who reports to Z so X is level two for Z. So in my table i have a column called Level that maps this number. But my parent ID is based on direct report only (Level 1). There should be a filter criteria in my DAX that says Level = 1.
Also all columns are from the same table
(For example:)
Emp Manager Level
X Y 1
Y Z 1
X Z 2
The bold column states that Z is a manager for X also but level 2. But i should get only y's id as parent_id not Z's for the employee X.
@Anonymous ,
Are you trying to create a calculated column to add to your table with dax?
Did my answer display how you would expect the ParentID to display?
I know we have some additional work to do with the new Level data that you described, but want to know if we are on the same page so far.
Thanks,
Proud to be a Super User!
Hey, so the logic without the level part is correct.. but it throws an error in powerbi for me and i am not able to create that calculated column
But basically i need the same LOGIC taking into account the factor " WHERE LEVEL = 1
Thanks for your time!
@Anonymous ,
The reason that the calculated column throws an error is that we need to search another table for the value. So the way around this would be to create a virtual table. However to verify that we are on the same page, using your original data, would we expect to see the following for the employee PRAK? Multiple entries on the EMP column, showing all their managers, not just direct reports? (Columns are ID, Emp Name, Mgr Name, Level)
ID Emp Name Mgr Name LEVEL
1 | TRA | LAW | 1 |
2 | ERI | LAW | 1 |
3 | LAW | JE | 1 |
4 | MELI | TRA | 1 |
5 | JA | TRA | 1 |
6 | HAZ | TRA | 1 |
7 | CHR | HAZ | 1 |
8 | CAT | HAZ | 1 |
9 | PRAK | JA | 1 |
10 | AK | PRA | 1 |
9 | PRAK | TRA | 2 |
9 | PRAK | LAW | 3 |
9 | PRAK | JE | 4 |
Proud to be a Super User!
Hi @Anonymous ,
Think this is it. Apparently if we surround the LOOKUPVALUE() with an IF (), then it can look at the same table for the values. This is blank where the Level is not 1, or where we don't have the ID for the Mgr Name.
Please consider marking this as your solution, or let me know if we have further work.
Cheers,
Proud to be a Super User!
@Nathaniel_C Hey thanks a lot! But could there be any reason why i am getting this error?
@Anonymous ,
I will be offline for a couple of hours, but I wonder if you might have two employees with the same name, and different ids? or a typo on entering the id. Then you would have multiple values for the same name.
Proud to be a Super User!
@Anonymous ,
Can you build a table like mine in PBI and recreate my solution? Then we could check that we are at least matching there.
Proud to be a Super User!
@Nathaniel_C Hey I think I found the problem (But not sure what the solution is) .. So when we look up through Emp_Name, there are multiple instances where 2 or more empl have the same name, but obviously they are different people so they have different ID's. This is why the problem is occuring.
The additional columns i have with this table are - Emp USERID (Text Field), Manager USERID(Text Field) and Manager ID (Integer Field). Not sure if we could use any of these if we could use any of these. An example with all the fields are as follows:
Emp ID Emp Name Emp_UserID Mgr_Name Mgr_ ID Mgr_USERID Level
636 James Br H21 Gar 627 HBB 1
636 James Br H21 Gor 288 E43 2
636 James Br H21 James Br 122 H03 6
636 James Br H21 Jeff 164 COR 7
632 James Br H53 Jeff 164 COR 6
632 James Br H53 Matt 577 H15 1
122 James Br H03 Jeff 164 COR 1
The colors show the same emp. This is the problem I am facing. (Manager ID is not a part of the original column. I got it additionally through Merge Table)
@Anonymous ,
This should do it, although you may want to combine the columns. I left them separate for ease in debugging. First we combine the two columns [ID] and [Emp Name] into [EmpCombo] separated by ",". (This gives us a unique identifier for all. )Then we use IF() and LOOKUPVALUE () in [SelectEmpCombo. That gives us the answer, but then we need to separate out the Emp ID.
So, we use IF() and FIND() in [FindDelimiter] to find how many characters the ID is. Finally we use IF() and LEFT() to get the ParentID.
If this solves your issue, please consider marking it Solved. KUDOS are nice too!
Proud to be a Super User!
@Nathaniel_C Thank you so much for the follow up! I just did a small change; I lloked up the ID of the Emp with Emp Combo and Manager Combo (Created Manager Combo = Manager ID + Manager Name). Then this worked for me!
Parent_ID = if(Table[Level]=1, lookupvalue(Table[ID], Table[EmpCombo], Table[ManagerCombo]))
@Anonymous ,
Great!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |