How to createtable from one table but filtering based on values from another table

[rdwd15]

Hello Friendly Helpers,

I have been racking my brain and the interwebs and the youtubes for this.  I am former DB dev, and i could so this faily easily in SQL, but alas I am using the tools available to me.  So I have a table with the following data:

This table has all employees in it with the id of the employee they report to.

AllEmployees
EmployeeID	EmployeeName	ManagerID
1	A	99
2	B	1
3	C	1
4	D	2
5	E	2
6	F	3
7	G	3
8	H	9
9	I	9
10	J	9

I created a table that where the managerID = 1 from the AllEmployees table. 

Level1Managers
EmployeeID	EmployeeName	ManagerID
2	B	1
3	C	1

On this table, i would like to create a table that is populated with employees that have the ManagerID that is in Level1Managers.

Level2Managers
EmployeeID	EmployeeName	ManagerID
4	D	2
5	E	2
6	F	3
7	G	3

So essentially i am planning on creating an org hierarchy out of this, going many many times over until it is complete.

For now, i would like to create the 3rd table.  I have been using createtable, but i am not sure how to put a list of dynamic values in the validation portion of the expression.

I tried:

LevelTwoManagers = CALCULATETABLE('All',LevelOneManagers[manager] = <this is where i get stuck>

Essentially i want to bring back the all the values from the AllEmployees table where ManagerID = LevelOneManagers.EmployeeID

HELP!!!!!  I am sure it is so very easy, but I just can't figure it out.  I have used things like instersect, and filter and i just can't seem to find what i am looking for.

[jsaunders_zero9 · Accepted solution]

Hi @rdwd15,

Have you considered using PATH as a basis to build your org heirachy?

Heirachy = PATH(Employee[EmployeeID],Employee[ManagerID])

Let me know if that helps

[rdwd15]

Thanks, that totally looks like what I want to do but with so many fewer complicated steps. 

I will give this a try. 

[jsaunders_zero9]

Good to hear @rdwd15, be sure to check back to let me know how you went.

[rdwd15]

Hello J.  Thanks so much, yes this will work exactly how i want it to, and so much easier than my long conviluted way i was going about it.

Thanks again.

[jsaunders_zero9]

Glad I could help and save you some time 👍

[jsaunders_zero9 · Accepted solution]

Hi @rdwd15,

Have you considered using PATH as a basis to build your org heirachy?

Heirachy = PATH(Employee[EmployeeID],Employee[ManagerID])

Let me know if that helps

[jsaunders_zero9 · Accepted solution]

To further expand on the above, splitting it can be done using PATHITEM (Index from left) or PATHITEMREVERSE (Index from right).

Manager 1 = PATHITEMREVERSE(Employee[Heirachy],2)
Manager 2 = PATHITEMREVERSE(Employee[Heirachy],3)
Manager 3 = PATHITEMREVERSE(Employee[Heirachy],4)