Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MTOnet
Helper III
Helper III

Calculate Number of Child Records

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

Sample Table.png

Desired Result

Desired Result.png

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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]
)

path.PNG

 

Best Regards,

Icey

 

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

 

 

View solution in original post

2 REPLIES 2
MTOnet
Helper III
Helper III

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.

Updated Sample.png

Icey
Community Support
Community Support

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]
)

path.PNG

 

Best Regards,

Icey

 

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

 

 

Helpful resources

Announcements
October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.