Forum Discussion
Solved
Calculation on parents from child inputs
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 ...
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 🙂
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 🙂
there might be simpler and cleaner solutions out there as im still new to this, but hope this helps atleast 🙂
Hello Anonymous
I've tested your proposition and it works like a charm! Thank you so much!
