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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.