Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@BenHoward
Create a new table:
New Table =
FILTER(
Table1,
Table1[Supervisor ID] IN ALL(Table1[EmpID])
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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!!
You can use SELECTCOLUMNS function
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@BenHoward
Create a new table:
New Table =
FILTER(
Table1,
Table1[Supervisor ID] IN ALL(Table1[EmpID])
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |