Assign employees to manager

[lokosrio]

Hi,

I have a table:

ID	E-mail address	IsManager	ManagerId
100	employee1@company.com	0	200
101	employee2@company.com	0	200
102	employee3@company.com	0	200
103	employee4@company.com	0	201
104	employee5@company.com	0	201
200	manager1@company.com	1
201	manager2@company.com	1

based on this I want to create a new table, which will assign employees to managers:

E-mail address	IsManager	id
manager1@company.com	1	100
manager1@company.com	1	101
manager1@company.com	1	102
manager2@company.com	1	103
manager2@company.com	1	104
employee1@company.com	0	100
employee2@company.com	0	101
employee3@company.com	0	102
employee4@company.com	0	103
employee5@company.com	0	104

I tried with CROSSJOIN function:

UNION(
SUMMARIZE(table,table[E-mail],table[IsManager],table[id]),
CROSSJOIN(
        SUMMARIZE(FILTER(table,table[IsManager]="1"),table[E-mail],table[IsTeamLeader])
,SUMMARIZE(table,table[id]))
)

but without success - it is assigning all the data, not only related to the manager. Could you please help me with this?

[mahoneypat · Accepted solution]

Please see this article on how to leverage the PATH function for this.

On the Right PATH() - A DAX/Power Query approach to working with parent/child hierarchies. - P3 Adap...

Pat

[lokosrio]

hi @mahoneypat,

works great - this is what I wanted to achieve, thank you very much!