cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Creating an employee manager relationship table

I've a table as under:

Original table:

Employee NameManager Lvl1Manager Lvl2
ALY
BMY
CLY
DMY
ENZ
FNZ
GOZ

 

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:

IDEmployeeManager ID
1A8
2B9
3C8
4D9
5E10
6F10
7G11
8L12
9M12
10N13
1113
12Y 
13Z 



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:

IDEmployeeManager IDPath
1A812|8|1
2B912|8|2
3C812|8|3
4D912|9|4
5E1013|10|5
6F1013|10|6
7G1113|11|7
8L1212|8
9M1212|9
10N1313|10
111313|11
12Y 12
13Z 13

 

I'm just having a hard time converting my original table to the format of the desired employee table

1 ACCEPTED SOLUTION
Community Support
Community Support

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:

91.png

 

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.

 

 

View solution in original post

2 REPLIES 2
Community Support
Community Support

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:

91.png

 

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.

 

 

View solution in original post

Super User I
Super User I

@dollarvora 
use this Function while creating new column and let me know if that works! share your Kudoes

Result Column = PATH(EMPID,MANAGER ID)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors