Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dollarvora
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
v-easonf-msft
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
v-easonf-msft
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.

 

 

VijayP
Super User
Super User

@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! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.