Need Help! How to manage employee and manager relationship

[xiumi_hou]

Dear All,

I have a a user table which contains all the users ID , users name, user email.... (employee and manager are all on one colunmn)  One column Example:

Employee 1

Employee2 

Employee 3

Employee4 

Manager 1

Employee5 

Employee 6

Employee7

Manager 2 

Employee 8

Employee 9

Employee 10

Employee 11

Employee 12 

Employee 13

Employee 14 

Employee 15

Employee 16

Employee 17

Manager 3

If  one employee assigned to one manager, we total have three managers.  I want to show the employee data result by manager. how should I create the employee-manager relationship since this relationship do not exist in the table? Please provide more details. Thanks.!

[ChrisMendoza]

I used https://www.daxpatterns.com/parent-child-hierarchies/#organizational-structure. Maybe it will hep you.

[xiumi_hou]

Hi Chris, 

Thanks for the reply. My question is how the manager name and employee name are both on one colum. How should I assigned the employee to manager? Thanks. @ChrisMendoza 

[ChrisMendoza]

@xiumi_hou - 

The article I provided demonstrates building the relationship between employee and manager. Since that is not built in to your source, you have to build it.

---

[edhans]

You didn't say how these were related, so in my example I assumed the manager listed was the manager of the employees above him. I now have this table.

See this PBIX file to see how this was done in Power Query. The key is using the Fill Up command on the Transform tab.

[xiumi_hou]

@edhans  

Hi Edhans,

Thanks for the reply. No.. It is not in any order I need to mannully figure out which employee belong to which manager. Do you have any idea about this? Thanks. 

[edhans]

How would anyone know what manager beloned to which employee? You need a key that ties a manager to an employee. DAX is cool. It isn't magic. 

[xiumi_hou]

Hi all, @edhans @ChrisMendoza 

I have a list of which employee belongs to which manager like this: 

Manager 1       Manger 2       Manager 3

Employ11          Employ21         Employ31

Employ12          Employ22         Employ32

Employ13          Employ23         Employ33

Employ14          Employ24         Employ34

Employ15          Employ25         Employ35

But the data in our database are all stored in one column with no order. How should I bulid the relationship between manager and employee? Thanks

[edhans]

You need to unpivot your table. See my file link again. It will turn your table into a matrix that will allow you to link it to the database columns and assign managers to employees.

[ChrisMendoza]

@xiumi_hou -

The article accurately describes how you can accomplish your task. I have personally used this in a similar circumstance. I suggest you review the documents and apply the techniques.