Count Parent Records that have Specified Criteria in a Child Record

[MTOnet]

I'm trying to get a count of parent records where a child record matches a selection from a Pie Chart to filters a 2nd chart, but just can't seem to get the formual to work.

I have a list of tasks with ID, Status, Type, Parent ID and Assignment.  I have a Pie Chart of all the names that are assigned to Child Tasks (all parents are of one type, and I have them filtered).

I have a 2nd Pie Chart to show the status of the Parent Tasks.  When I have no selection for Assignment, I can easily display the count of parent records by status.  What I am having difficulty with is getting the count of the parent records when I select one of the names from the Assignment Chart.  I have set the Assignment Pie Chart to Filter my 2nd chart, which is working, its just the calculation in my measure is not.

I'm thinking I want to filter the data by the assignment, so i can get all records that person is assigned to and then use that as an input to filter on records where the Parent ID exists in the PATH column that I had created.  I tried using PATHCONTAINS, but can't quite get the syntax to generate the result I am looking for.

Here is a simpified example of my data and expected Results

[parry2k]

@MTOnet I try to understand your question but not fully clear. Can you paste the data in table fomrat and also share the expected result. 

[MTOnet]

Hi @parry2k ,

Thank you for reaching out.

Here is a sample report with more data.

Sample Data 

Expected Result

-When no selection on Filter 1, then show the count of records by status when type is type 1 

-When selecting a value from Filter 1, Filter 2 is updated to show the status of type 1 records when the value selected from Filter 1 is on a child record of the type 1 record.

For example - When clicking on Person 8 in Filter 1, Filter 2 should 2 total records, 1 Status 1 & 1 Status 2.  So I'm trying to determine that the status of the parent is, when a child record matches the filtered assignment

[MTOnet]

Hi @parry2k 

I was trying to show what happens when selecting a name of tasks that are assigned and using person 8 as my example.

In the full set of data, person 8 only has 3 tasks, 28, 72 & 73.  I am trying to show what the status of the parent tasks of those items are.  so for task 28, I want to know the status of its parent, task 27.  For tasks 72 & 73, I want the status of the parent, task 69.  Task 27, has a status of of Status 1, tasl 69 has a status of status 2, so I would have  total count of those being 1 status 1, 1 status 2.

I'm basically trying to have the 2nd pie chart show the count of status for records of type 1 on the whole set of data and then when selecting a name, the 2nd chart shows the type 1 records where they show up on a record that is a sub-task of that parent.

[parry2k]

@MTOnet still lost but trying, see below, does it make sense when person 4 selected?

[MTOnet]

@parry2k I really appreciate your help and looking at this. What you have shown is close.  it appears that one record is missing from the calculation.  I would have expected 1 value for status 6 to appear as well.

Sorry if I am not explaining it well.  It makes complete sense to me what I am trying to do, but I can understand what I have in my head is not coming across well.

I have uploaded a new sample file.

Updated Sample File 

Table 2 shows the records that should be used in the calculation.  I am able to identify the records I want to be displayed in the table, I just can't turn it into a measure to be used for a visualization.  (Table 1 shows all records, parent and children, when any child record under the parent matches the assigned selection and Table 2 shows only the parent records in this case, which is the calcuation I am trying to get out of this).

[parry2k · Accepted solution]

@MTOnet here is what you need to do:

add a new table, let's call is Status with one column let's also call it Status with the unique value of each status, see below, and make sure it doesn't have a relationship with any table

Add following measure, I called it Total New

Total New = 
VAR __parentIds = VALUES ( data[Parent ID] )
VAR __countbyStatus = 
SUMMARIZE (
    FILTER (
        ALL ( data ), 
        data[ID] IN __parentIds && 
        data[Type] ="Type 1" 
    ), 
    [Status],
    "CountRows", COUNTROWS ( data ) 
)
RETURN 
MAXX ( 
    FILTER ( 
        __countbyStatus,
        [Status] =  SELECTEDVALUE ( 'Status'[Status] )
    ),
    [CountRows] 
)

Filter 2 pie chart, add Status from Status Table and above measure and you will get the following result based on Person 4 filtered in Filter 1 pie chart.

Hope this will get you going and you can take it from there.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[parry2k]

@MTOnet what is the logic you showed in the screenshot. Having a hard time understanding the logic,  I cannot connect dots, why filtering on person 8 will bring these records?