Help with Calculation when Parent has no Child Records

[MTOnet]

I have records with related child records that represent tasks.  In chart 1, I am showing the number of tasks that a person is assigned to. In chart 2, I am showing status of all the parent tasks.  Please see the attached example file, which is a basic representation of my data.

I am having trouble getting chart 2 to show the correct total of parent records.  If you look at the data, you can see there 24 type 2 records, but if you look at chart 2, only 21 are being returned.  the 3 records (all in Status 3 currently) that are not being displayed are records that do not have any child tasks assigned them.  I can tell that this is the issue with my measure, I'm just not sure what I need to do differently to get the result I want and needing to the show the proper relationship between the two.

report is Here 

[mahoneypat]

Thanks for providing a pbix file.  It makes it much easier to help.  My first impression was that your measures are fairly complex.  Is that necessary?  For example, this measure returns a total of 24 in your Chart 2.

Type 2 IDs = CALCULATE(DISTINCTCOUNT('Table'[ID]), 'Table'[Type] = "Type 2")

Does that meet your need?  If not, please explain the additional logic in your measure and what you are trying to accomplish.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[MTOnet]

hi @mahoneypat , thanks for the suggestion, but that didnt give the result I as looking for, as it doesn't keep the proper relationship.  It is only showing the counts of the parent items,  I didnt explain the relationship between the two well, so hopefully the below expalnation makes sense. and why I provided the .pbix source to show the relationship, in case I didnt explain it well enough

In chart 1, I am taking all the child tasks and showing the total amount of tasks per person.  If a person has no assigned tasks, then they are not showing up on this chart.  In chart 2, I am trying to show the status of all parent items where a person selected in Chart 1 is listed as the assigned to on a parent item, or they are assigned to a task on a child item. 

For example, if I click on Person 9, I should see 3 results in Chart 2 (2 for status 3, 1 for status 1) as that person is assigned tasks on 3 different parent items, while the result on the given masure returns 0, since they are not assigned any of those parent items.  It is this part that is not calculating when a parent has no child tasks, as Person 9 should show 3, but currentlt only shows 2 results (those of thich are tasks on a parent item, but not the parent item they are assigned to which has 0 child tasks).  The result should also show when the assigned to on a parent item does not show on any tasks, like persons 11 & 12, who are assigned to a parent item, which have no tasks.  These are the three items that are missing. 

I am trying to add in a slicer for status and that is where I am having trouble. When I originaly didnt have a slicer for status, I could get the result I was looking for on chart 2, but adding in the slicer made me revisit my measure, and this is the closest I have been able to get.

[mahoneypat]

One thing I noticed is that your slicer does not include Status 6 (as you are filtering to Type 2 only).  Why is that?

In any case, I started simpler using the two measures below and got the result in the pic, which seems to be what you are looking for (when Person 9 selected in visual 1).

Use in visual 1

Tasks = DISTINCTCOUNT('Table'[ID])

Used in visual 2

Parent IDs = DISTINCTCOUNT('Table'[Parent ID])

Sorry that I need more explanation, but what is wrong with this simpler approach? 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[MTOnet]

@mahoneypat First, let me thank you for taking the time to look at this, I really appreciated it.  I am for a simpler approach, if possible.  I'm still learning, so any way to do what I am doing in a simpler way and learn, I am all for it.  At first, for your measure for Chart 1, it wasn't giving the values I was looking for, but after adding a visual level filter, it gave the correct results.  There is impact to another area of my report, but I can see how that can be fixed.  so thank you for that.

The measure for chart 2 still wasnt giving the proper results.  As now, when no items were selected, it showed more than the 24 results I would have expected.

What I am trying to show in chart 2, is acount of the statuses of all parent items (all parent items are type 2) where the selected person from chart 1 is assigned to a parent item (type 2) and where they have an assigned task on a parent item, whether they are assigned that parent item or not.

For example:

Person 8:

Assigned Type 2 item with ID 475068 with parent 470575 (so use status of 470568 in calculation)

Assigned Type 1 item with ID 507544 with parent 470568 (Use parent status in calculation, but already accounted for by previous scenario)

Assigned Type 1 item with ID 511162 with parent 508659 (Use parent status in calculation)

Assigned Type 2 item with ID 508725with parent 470860 (so use status of 508725 in calculation)

So for Person 8, there should be 1 - Status 1. 1 - Status 2, & 1 - Status 3

See below chart for all results

[mahoneypat]

Ok.  I think I understand.  This measure gets your desired results.  See matrix.

Parent Count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ID] ),
    ALL ( 'Table'[Assigned To] ),
    TREATAS ( VALUES ( 'Table'[Parent ID] ), 'Table'[ID] )
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[MTOnet]

This actually gets me right back to where I was, although in a simpler manner (and the TREATAS gives me something to look into and learn).  However it is not the result I am looking for, and that is my fault and i apologize..  I shared the wrong screenshot of the result.  For Person 8, they should have 3 values and not 2, as they have one story that doesnt have any child records.  The total when no person is selected, should be 24, as there are two other items with no child records assigned to pople that do not have any tasks.