Forum Discussion
Power BI Query: Identify Parent record with qualifying Child records
Hi All - Looking for some assistance with Query or filter Issue: I have a child TABLE A with a Direct relationship to PROJECT table. I want to filter TABLE A to ONLY show projects that have NO ch...
I think I figured this out:
1. Added a Custom column with If statement to ADD 0 for nulls and 1 for not null
= Table.AddColumn(#"Removed Other Columns", "Custom", each if [cxp_status] = null then 0 else 1)
2. Added a Group By on ProjectID and SUM of Custom Column added to Count column
= Table.Group(#"Added Custom", {"PROJECTID},{{"Count", each List.Sum([Custom]), type number}})
3. FLITERED on Count = 0
= Table.SelectRows(#"Grouped Rows", each ([PROJECTID] <> null) and ([Count] = 0))
Im left with 2 columns PROJECTID and Count. I can then filter the on Count = 0 which should give me all projects that have all related child records with status = NULL
sound right?