Forum Discussion

DorisCMoore's avatar
DorisCMoore
Frequent Visitor
3 years ago
Solved

Calculation on parents from child inputs

Hello Power BI Experts!

 

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

 

IDTypeParentIDRemainingHours
1001Bug  
1002Task10018
1002Task10015
1003Task10011
1004Task10015
1004Task10012
1005Bug  
1006Task10053
1007Task10051
1007Task10050
1008Bug  
1009Bug  
1010Task10091

 

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
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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 🙂



2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      DorisCMoore
      Frequent Visitor
      3 years ago

      Hello Anonymous 

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