Using DAX to remove invalid PATH rows

[BenHoward]

Hi, 

I'm using the PATH function in DAX to work out the hierarchy in an organsation, but as usual I have data quality issues which cannot be resolved in the source data.  My HR table has invalid parent data eg.

Emp Name	EmpID	Supervisor ID
Amir	1	1
Sharon	2	1
John	3	4

John has an invalid Supervisor ID.  How would I write DAX to create a new table where Supervisor ID is not in the EmpID column.  My resulting table would as follows, ie without the row containing the invalid supervisor ID.

Emp Name	EmpID	Supervisor ID
Amir	1	1
Sharon	2	1

I should add I have 50k plus rows so something that's not hard coding the supervisorID would be great!

Thanks,  Ben.

[BenHoward]

Fantastic, thank you.  One last question if I may, assuming my 1st table had 10 other columns, but I only wanted to return the 3 columns Emp Name, EmpId and Supervisor Id, how would I do that?  Thanks again!!

[Fowmy]

@BenHoward 

You can use SELECTCOLUMNS function 

[Fowmy · Accepted solution]

@BenHoward 

Create a new table:

New Table = 
	FILTER(
		Table1,
		Table1[Supervisor ID] IN ALL(Table1[EmpID])
	)