Calculation on parents from child inputs

[DorisCMoore]

Hello Power BI Experts!

Here's an example of data I'm working with:

ID	Type	ParentID	RemainingHours
1001	Bug
1002	Task	1001	8
1002	Task	1001	5
1003	Task	1001	1
1004	Task	1001	5
1004	Task	1001	2
1005	Bug
1006	Task	1005	3
1007	Task	1005	1
1007	Task	1005	0
1008	Bug
1009	Bug
1010	Task	1009	1

I'm having items of different types: Bug and Task.

Both are related, so I can see from my Task rows to which Bug they're linked to through the ParentID column.

The data contains history version for each item, so they can be duplicated to reflect different states (here, the number of RemainingHours decreasing).

I'm trying to put in place Measures (or Columns, I'm not sure what's feasible) in order to:

• Calculate the number of unique Tasks linked to a Bug
• Calculate the MAX number of RemainingHours that have been linked to a Bug
• Calculate the number of RemainingHours linked to a Bug

For example, the result should shows that:

Bug-1001 is linked to 3 Tasks, had 14 Hours planned and has 8 hours remaining.

Bug-1005 is linked to 2 Tasks, had 4 hours planned and has 3 hours remaining.

Bug-1008 is not linked to any Task

Bug-1009 is linked to 1 Task, had 1 hour planned and has 1 hour remaining.

So far, I've tried this (to have the number of planned hours):

SUMX(SUMMARIZE(AllWorkItems,AllWorkItems[Parent Work Item Id],"MaxRemainingWork",MAX(AllWorkItems[Remaining Work])),[MaxRemainingWork])

But it shows me the max number of planned hours for one Task and doesn't aggregate all the Tasks within one Bug.

Can someone knows how to do that?

Thanks,

Doris

[Anonymous · Accepted solution]

hello, i have tried it using calculated columns as you can see on screenshot. i have used interim columns to come up with the planned/max remaining hours per bug and remaining hours per bug:

Unique Tasks per Bug =

var tasktype = 'Table'[Type]

var id_ = 'Table'[ID]

return if(tasktype = "Bug",calculate(countrows(distinct('Table'[ID])),filter('Table','Table'[ParentID] = id_)))

Max Hours per Task =

var tasktype = 'Table'[Type]

var id_ ='Table'[ID]

return calculate(maxx('Table','Table'[RemainingHours]),filter('Table','Table'[ID]=id_))

Max Hours per Bug =

var tasktype = 'Table'[Type]

var id_ = 'Table'[ID]

return if(tasktype = "Bug",calculate(sumx(distinct('Table'[Max Hours per Task]),calculate(max('Table'[Max Hours per Task]))),filter('Table','Table'[ParentID]=id_)))

Remaining Hours per Task =

var tasktype = 'Table'[Type]

var id_ ='Table'[ID]

return calculate(MINX('Table','Table'[RemainingHours]),filter('Table','Table'[ID]=id_))

Remaining Hours per Bug =

var tasktype = 'Table'[Type]

var id_ = 'Table'[ID]

return if(tasktype = "Bug",calculate(sumx(distinct('Table'[Remaining Hours per Task]),calculate(MIN('Table'[Remaining Hours per Task]))),filter('Table','Table'[ParentID]=id_)))


there might be simpler and cleaner solutions out there as im still new to this, but hope this helps atleast 🙂

[DorisCMoore]

Hello @Anonymous 

I've tested your proposition and it works like a charm! Thank you so much!