Forum Discussion
Parent-Child Hierarchy Filtering Conditions
Community ChampionI would maybe start by creating a separate Parent table and linking that to the Child table using ID to ParentID.
The first part of your filter condition is easy enough > Filter for Apple and Done, it's the AND with the second child that is making me think a bit, as you would typically need to do this as an OR.
Will you only ever have 2 conditions required? Or does the solution need to allow for more?
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
Hi Allison, thanks for your quick reply!
Point 1 (linking that to the Child table using ID to ParentID) is done.
Point 2 (would typically need to do this as an OR) I need here here also an AND condition to filter exatly for the right items - otherwise it would show me also the wrong parent items.
Can you show me an example of the formula for point 2 please?
- AllisonKennedy
Try something like this, as a MEASURE:
DoneApples = IF(ISBLANK(MAXX(FILTER(Child,Child[Item]= "Apple" && Child[Status] = "Done"),Child[Parent ID])),"A",IF(MAXX(FILTER(Child,Child[Item]= "Apple" && Child[Status] = "Done"),Child[Parent ID])=MAXX(FILTER(Child,Child[Item]= "Pear"&& Child[Status] = "Done"),Child[Parent ID]), "Yes", "No"))Then put it in a table or matrix with the Parent[Name].It can probably be made a bit more elegant if you have the time, but let me know if that doesn't make sense or if it works.Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
Hi Allison,
I transferred your suggestion to the real table as below but it says as error "Too many arguments were passed to the FILTER function. The maximum argument count for the function is 2." What does this mean?
TEST = IF(ISBLANK(MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Apple" && 'Project Tracker'[State]="Done");'Project Tracker'[Parent Work Item Id]));"A";IF(MAXX(FILTER('Project Tracker';'Project Tracker'[Title]= "Apple" && [State]="Done");[Parent Work Item Id])=MAXX(FILTER('Project Tracker';[Title]= "Pear"&& [State]="To Do";[Parent Work Item Id]); "YES"; "NO")))- AllisonKennedy
Anonymous That means you have a syntax error, you need to close the FILTER function with )
Try adding the red ) as below and delete the final 😞
TEST = IF(ISBLANK(MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Apple" && 'Project Tracker'[State]="Done");'Project Tracker'[Parent Work Item Id]));"A";IF(MAXX(FILTER('Project Tracker';'Project Tracker'[Title]= "Apple" && [State]="Done");[Parent Work Item Id])=MAXX(FILTER('Project Tracker';[Title]= "Pear"&& [State]="To Do");[Parent Work Item Id]); "YES"; "NO"))
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.