Parent-Child Hierarchy Filtering Conditions

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"

)

---