Forum Discussion

BenHoward's avatar
BenHoward
Helper I
5 years ago
Solved

Using DAX to remove invalid PATH rows

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 NameEmpIDSupervisor ID
Amir11
Sharon21
John34

 

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 NameEmpIDSupervisor ID
Amir11
Sharon21

 

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

Thanks,  Ben.

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    BenHoward 

    Create a new table:

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

     

3 Replies

  • BenHoward's avatar
    BenHoward
    Helper I
    5 years ago

    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's avatar
    Fowmy
    Super User
    5 years ago

    BenHoward 

    Create a new table:

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