cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

Accepted Solutions
Highlighted
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
Highlighted
Solution Sage
Solution Sage

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

Result Column = PATH(EMPID,MANAGER ID)

Highlighted
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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors