Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I've a table as under:
Original table:
Employee Name | Manager Lvl1 | Manager Lvl2 |
A | L | Y |
B | M | Y |
C | L | Y |
D | M | Y |
E | N | Z |
F | N | Z |
G | O | Z |
I want to add an id to all employee levels and also a column that specifies the manager ID for each employee as under:
Desired Employee Table:
ID | Employee | Manager ID |
1 | A | 8 |
2 | B | 9 |
3 | C | 8 |
4 | D | 9 |
5 | E | 10 |
6 | F | 10 |
7 | G | 11 |
8 | L | 12 |
9 | M | 12 |
10 | N | 13 |
11 | O | 13 |
12 | Y | |
13 | Z |
My main aim in doing this is to add a path column to create a path of each employee manager relation so that I can add row level security:
The path function that I'd be using is:
EmployeePath= Employee[ID], Employee[Manager ID])
so that my end table would look like this:
ID | Employee | Manager ID | Path |
1 | A | 8 | 12|8|1 |
2 | B | 9 | 12|8|2 |
3 | C | 8 | 12|8|3 |
4 | D | 9 | 12|9|4 |
5 | E | 10 | 13|10|5 |
6 | F | 10 | 13|10|6 |
7 | G | 11 | 13|11|7 |
8 | L | 12 | 12|8 |
9 | M | 12 | 12|9 |
10 | N | 13 | 13|10 |
11 | O | 13 | 13|11 |
12 | Y | 12 | |
13 | Z | 13 |
I'm just having a hard time converting my original table to the format of the desired employee table
Solved! Go to Solution.
Hi, @dollarvora
Try steps as below:
1. Create a calculated table:
Table = UNION(DISTINCT('Original table'[Employee Name]),DISTINCT('Original table'[Manager Lvl1]),DISTINCT('Original table'[Manager Lvl2]))
2.create a index column in new table
Index = RANKX('Table','Table'[Employee Name],,ASC,Dense)
3.create calculated columns "Manage Name" ,"Manage ID" ,"PATH" :
Manager Name =
VAR column_lv1 =
LOOKUPVALUE (
'Original table'[Manager Lvl1],
'Original table'[Employee Name], 'Table'[Employee Name]
)
VAR column_lv2 =
LOOKUPVALUE (
'Original table'[Manager Lvl2],
'Original table'[Manager Lvl1], 'Table'[Employee Name]
)
RETURN
IF ( ISBLANK ( column_lv1 ), column_lv2, column_lv1 )
Manage ID = LOOKUPVALUE('Table'[Index],'Table'[Employee Name],'Table'[Manager Name])
Path = PATH('Table'[Index],'Table'[Manage ID])
The result will show as below:
For more details,please check the attached pbix file.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @dollarvora
Try steps as below:
1. Create a calculated table:
Table = UNION(DISTINCT('Original table'[Employee Name]),DISTINCT('Original table'[Manager Lvl1]),DISTINCT('Original table'[Manager Lvl2]))
2.create a index column in new table
Index = RANKX('Table','Table'[Employee Name],,ASC,Dense)
3.create calculated columns "Manage Name" ,"Manage ID" ,"PATH" :
Manager Name =
VAR column_lv1 =
LOOKUPVALUE (
'Original table'[Manager Lvl1],
'Original table'[Employee Name], 'Table'[Employee Name]
)
VAR column_lv2 =
LOOKUPVALUE (
'Original table'[Manager Lvl2],
'Original table'[Manager Lvl1], 'Table'[Employee Name]
)
RETURN
IF ( ISBLANK ( column_lv1 ), column_lv2, column_lv1 )
Manage ID = LOOKUPVALUE('Table'[Index],'Table'[Employee Name],'Table'[Manager Name])
Path = PATH('Table'[Index],'Table'[Manage ID])
The result will show as below:
For more details,please check the attached pbix file.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@dollarvora
use this Function while creating new column and let me know if that works! share your Kudoes
Result Column = PATH(EMPID,MANAGER ID)
Proud to be a Super User!
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |