I've a table as under:
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:
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:
I'm just having a hard time converting my original table to the format of the desired employee table
Go to Solution.
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 =
'Original table'[Manager Lvl1],
'Original table'[Employee Name], 'Table'[Employee Name]
VAR column_lv2 =
'Original table'[Manager Lvl2],
'Original table'[Manager Lvl1], 'Table'[Employee Name]
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 _ EasonIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
View solution in original post
@dollarvora use this Function while creating new column and let me know if that works! share your Kudoes
Result Column = PATH(EMPID,MANAGER ID)
Check out the on demand sessions that are available now!
Check out the Winners!
Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.