Forum Discussion
New Column Creation
Anonymous ,
SelectEmpCombo = IF(EmpLevel[LEVEL]=1,LOOKUPVALUE(EmpLevel[EmpCombo],EmpLevel[Emp Name],EmpLevel[Mgr Name]))EmpCombo = CONCATENATE(EmpLevel[ID], CONCATENATE("," ,EmpLevel[Emp Name]))FindDelimiter = IF(EmpLevel[SelectEmpCombo]<> "",Find(",",EmpLevel[SelectEmpCombo]))ParentID = IF(EmpLevel[SelectEmpCombo]<>"",LEFT(EmpLevel[SelectEmpCombo],EmpLevel[FindDelimiter]-1))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!:smileyhappy:
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,
Nathaniel_C Hey thanks a lot! But could there be any reason why i am getting this error?
Anonymous ,
SelectEmpCombo = IF(EmpLevel[LEVEL]=1,LOOKUPVALUE(EmpLevel[EmpCombo],EmpLevel[Emp Name],EmpLevel[Mgr Name]))EmpCombo = CONCATENATE(EmpLevel[ID], CONCATENATE("," ,EmpLevel[Emp Name]))FindDelimiter = IF(EmpLevel[SelectEmpCombo]<> "",Find(",",EmpLevel[SelectEmpCombo]))ParentID = IF(EmpLevel[SelectEmpCombo]<>"",LEFT(EmpLevel[SelectEmpCombo],EmpLevel[FindDelimiter]-1))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!:smileyhappy:
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.
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.
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)
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!