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
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
Solved! Go to 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.
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.
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.
User | Count |
---|---|
123 | |
96 | |
89 | |
73 | |
63 |
User | Count |
---|---|
138 | |
115 | |
111 | |
98 | |
95 |