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
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.
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,
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