Parent-Child Hierarchy Filtering Conditions

[Anonymous]

Hi, I am very new in Power BI and have following issue to find the right formula combination in Power BI.

Is this possible in Power BI and if yes how can I realize thi?

Thanks in Advance 🙂

[AllisonKennedy]

I 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. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

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. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

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. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

@AllisonKennedy The formula works now, thanks!

But as result it shows in the new "TEST" column in all cells the red "A" from the formula below - do you think we missed here something else?

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]

Sorry, you can change the red A to "No" and are you doing this as a MEASURE or Column? It will not work as a Column, so needs to be done as a MEASURE.

Do as a MEASURE, but you will need to use it in the right configuration, so only works/makes sense when used in a Matrix or Table with Parent Name or ID in the rows. 

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. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

Hi @AllisonKennedy ,

I changed "A" to "NO", put the formula in a measure and involved in a matrix report - but there is no change. All the values are shown with "NO" now.

Would it be possible for you to explain the 3 areas of the formula - maybe I can check the steps from myside when I am able to understand the formula?

TEST =

IF (ISBLANK (MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Apple" && 'Project Tracker'[State]="Done");'Project Tracker'[Parent Work Item Id]));"NO";

IF (MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Apple" && 'Project Tracker'[State]="Done");'Project Tracker'[Parent Work Item Id])=

MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Pear" && 'Project Tracker'[State]="To Do");'Project Tracker'[Parent Work Item Id]);"YES";"NO")

)

[AllisonKennedy]

@Anonymous Here's my best attempt at explaining my reasoning behind the formula, along with some inline commments below: 

My thinking is that you want to get the Parent based on a condition looking at two different children, so I have created a MEASURE that finds all parents that meet child 1 condition, and checks if that is the same parent for child 2 condition. This only works when created in the context of a Matrix using the Parent name in rows and the MEASURE in values, because that list of 'all parents' will only look for/accept the current selected value of parent as per the filter context of the rows in the matrix, and if both children return that parent, then they are equal to each other and therefore it returns "yes" show the Parent. Because there are times when a given parent will not meet either condition, I have added the check for ISBLANK to make sure that blank = blank doesn't give you a false positive. 

---

@Anonymous wrote:

Hi @AllisonKennedy ,

I changed "A" to "NO", put the formula in a measure and involved in a matrix report - but there is no change. All the values are shown with "NO" now.

Would it be possible for you to explain the 3 areas of the formula - maybe I can check the steps from myside when I am able to understand the formula?

TEST =

IF (ISBLANK (MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Apple" && 'Project Tracker'[State]="Done");

-- Look for Title = Apple and State = Done in the Project Tracker table, note this will use the filter context of the matrix so will only check for the projects with the parent ID in the row in the matrix

'Project Tracker'[Parent Work Item Id])); 

-- If Title = Apple and State = Done, return the max Parent Work Item Id

"NO";

-- If Parent Work Item Id is not found, it will be blank, so "NO" will be returned

-- This next part will only happen if Parent Id is not blank and the parent Id for the parent in the matrix row has at least one child with Title = Apple and State = Done

IF (

MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Apple" && 'Project Tracker'[State]="Done");'Project Tracker'[Parent Work Item Id])

--check again for the same parent Id for Title = Apple and State = Done (we already know it's not blank)

=

-- check if that Apple Done project parent ID is equal to Pear To Do

MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Pear" && 'Project Tracker'[State]="To Do");'Project Tracker'[Parent Work Item Id]);

-- check for the Parent Id for child 2 (Pear To Do)

"YES";

-- If both children are found in the Parent Id, return "Yes" (show that parent in the filter)

"NO")

-- If not both children are found, return "No"

)

---

[Anonymous]

Hi @AllisonKennedy ,

after several runs the formula is working in a matrix as measure - thanks for your support and for the detailed description!

There is now only one thing that is not working as expected. How can I adjust the (red marked field) in the formula from "is equal to Apple" to "contains text like Apple" e.g. instead of 'Project Tracker'[Title]="Apple" >> 'Project Tracker'[Title] contains text "Apple"?

TEST =

IF (ISBLANK (MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Apple" && 'Project Tracker'[State]="Done");'Project Tracker'[Parent Work Item Id]));"NO";

IF (MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Apple" && 'Project Tracker'[State]="Done");'Project Tracker'[Parent Work Item Id])=

MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Pear" && 'Project Tracker'[State]="To Do");'Project Tracker'[Parent Work Item Id]);"YES";"NO")

)

[AllisonKennedy]

@Anonymous  You're very welcome. Glad it's working. 

To acheive the Contains rather than equal to, try using CONTAINSSTRING https://docs.microsoft.com/en-us/dax/containsstring-function-dax

TEST =

IF (ISBLANK (MAXX(FILTER('Project Tracker';CONTAINSSTRING(SELECTEDVALUE('Project Tracker'[Title]),"Apple") && 'Project Tracker'[State]="Done");'Project Tracker'[Parent Work Item Id]));"NO";

IF (MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Apple" && 'Project Tracker'[State]="Done");'Project Tracker'[Parent Work Item Id])=

MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Pear" && 'Project Tracker'[State]="To Do");'Project Tracker'[Parent Work Item Id]);"YES";"NO")

)

[Anonymous]

@AllisonKennedy  After investing some hours for adjustments on the formula to adapt this to my real case with real data, it is working now very well. Thanks for your support!!!

[Anonymous]

@AllisonKennedy  after working with real data I realized that the measure are not showing everytime the right results:

Instead of showing the Items with"Apple" on state "To Do" AND "Pear" on state "Done", the formula is showing also the Items with "Apple" on state "In Progress" AND "Pear" on state "Done"- do you have any idea why this happens? Below is the measure that I used for this.

Measure  = IF (ISBLANK

(MAXX(FILTER('Project Tracker',CONTAINSSTRING(SELECTEDVALUE('Project Tracker'[Title]),"Apple")&&'Project Tracker'[State]="To Do"),'Project Tracker'[Parent Work Item Id])),"NO",IF

(MAXX(FILTER('Project Tracker',CONTAINSSTRING(SELECTEDVALUE('Project Tracker'[Title]),"Apple")&&'Project Tracker'[State]="To Do"),'Project Tracker'[Parent Work Item Id])=

MAXX(FILTER('Project Tracker',CONTAINSSTRING(SELECTEDVALUE('Project Tracker'[Title]),"Pear")&&'Project Tracker'[State]="Done"),'Project Tracker'[Parent Work Item Id]),"YES","CHECK")

)

[AllisonKennedy]

@Anonymous  The measure looks ok. If you're getting a result for Parent Work Item Id that means that at least one of its children is Apple To Do and at least one of its children is Pear Done. Is it possible that there's also a third child that is Apple In progress? 

[Anonymous]

@AllisonKennedy  there are also other childs wich has the states "To Do", "In Progress" or "Done", but these childs has diffrent titles/names as seen on the graphic below. I checked it and there is no futher Apple with a state "In Progress". Do you think a change in the formula from "at least" to "exactly" can help us here? 

[AllisonKennedy]

@Anonymous  Sorry for the delay - are you still struggling with this one? Not sure which 'at least' you're referring to?

[Anonymous]

@AllisonKennedy  Issue is solved now. I delete the parts with SELECTEDVALUE and include in the table only the fields "Parent ID" and "Measure". It doesnt work if "Child ID" is also included in the table - this was the problem 😉

[AllisonKennedy]

Awesome! Glad it's working.