Calculate Number of Child Records

[MTOnet]

I am having difficulty trying to calculate the number of child records on a parent record.  I haven't been able to come up with a measure that gives me the result I am looking for.  Any help is appreciated.

Below is some sample data, Table1

Desired Result

[MTOnet]

So, I need a little more help with this one.  I believe I have figured this out, using Path and Path Length, but I am not able to get it to work, due to my data.  I'm running getting errors that I'm not sure how to handle.

I am trying to do some reporting off of Azure DevOps data and I am running into what I think is a permission related issue, resulting in records that have a Parent ID that is not available in the data.

I am trying to generate some information related to work done in an Iteration.  There are some User Stories that our team has worked that came from another Team.  I do not have access to that Team, so the ADO connector tool is not bringing over the WorkItem that relates to the Parent.  I beleive this is causing the PATH function to return an error (The value XXXXX  in Table'ParentID' must also exisit in Table'ID'.  Please add the missing data and try again').  Since I dont access to the Team that the Parent Workitem exists in, I cannot add this data.

Below is a more complete representation of my Data, based off of this error.  In the below data, User Story with ID 9, has a Parent ID (feature - ID 11) from another team, which doesnt actually exist in my data.  How can I work around this scenario?  Ultimately, I am trying to get the measure to identify if there is no Testing task associated to the User Story, but I can't get by this data issue.

[Icey · Accepted solution]

Hi @MTOnet ,

Try this:

Parent ID 1 =
IF (
    'Table (2)'[Parent ID] IN VALUES ( 'Table (2)'[ID] ),
    'Table (2)'[Parent ID],
    BLANK ()
)

Path = PATH('Table (2)'[ID],'Table (2)'[Parent ID 1])

Path 1 =
IF (
    LOOKUPVALUE ( 'Table (2)'[ID], 'Table (2)'[ID], 'Table (2)'[Parent ID] )
        = BLANK ()
        && 'Table (2)'[Parent ID] <> BLANK (),
    'Table (2)'[Parent ID] & "",
    'Table (2)'[Path]
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.