Solved: Adding a summary column for parent at multlevel Hi...

k_rahul_g · ‎10-16-2024

Hi, I have a power BI table (BaseData) with columns as

WorkItemID, WorkItem Type, ParentID, Path (created using Path Function) , Effort Esitmate, Effort Spent.

for Simplicity, I have added another table (Parent_Child) that has just 2 columns - Parent ID and Child ID ( both referrring to workitems in parent table)

I want to add a column at workitem level, that goes recursively in the hierarchy and adds effort values for all child workitems.

I know it can be done using "PathContains" function, but due to large size of the dataset, powerBI just hangs before the calculation is completed.

sample Data

workItemID	ParentID	workItemType	Task Category	Path	Category_Path	_Total Estimate	_Total Effort
102085	32963	Task		3465\|32963\|102085	Program Epic \| Story \| Task \| \| \| \| \| \|	16	16
102087	32963	Task		3465\|32963\|102087	Program Epic \| Story \| Task \| \| \| \| \| \|	32	32
102337	32963	Task		3465\|32963\|102337	Program Epic \| Story \| Task \| \| \| \| \| \|	32	32
102340	32963	Task		3465\|32963\|102340	Program Epic \| Story \| Task \| \| \| \| \| \|	8	8
102859	32963	Task		3465\|32963\|102859	Program Epic \| Story \| Task \| \| \| \| \| \|	8	8
102866	32963	Task		3465\|32963\|102866	Program Epic \| Story \| Task \| \| \| \| \| \|	24	24
102867	32963	Task		3465\|32963\|102867	Program Epic \| Story \| Task \| \| \| \| \| \|	16	16
102868	32963	Task		3465\|32963\|102868	Program Epic \| Story \| Task \| \| \| \| \| \|	24	24
102870	32963	Task		3465\|32963\|102870	Program Epic \| Story \| Task \| \| \| \| \| \|	24	24
104009	32963	Task		3465\|32963\|104009	Program Epic \| Story \| Task \| \| \| \| \| \|	16
104010	32963	Task		3465\|32963\|104010	Program Epic \| Story \| Task \| \| \| \| \| \|	16	16
104015	32963	Task		3465\|32963\|104015	Program Epic \| Story \| Task \| \| \| \| \| \|	24	24
104016	32963	Task		3465\|32963\|104016	Program Epic \| Story \| Task \| \| \| \| \| \|	24	24
104018	32963	Task		3465\|32963\|104018	Program Epic \| Story \| Task \| \| \| \| \| \|	32	32
105825	32963	Task		3465\|32963\|105825	Program Epic \| Story \| Task \| \| \| \| \| \|	24	24
105826	32963	Task		3465\|32963\|105826	Program Epic \| Story \| Task \| \| \| \| \| \|	32	32
105828	32963	Task		3465\|32963\|105828	Program Epic \| Story \| Task \| \| \| \| \| \|	24	24
105829	32963	Task		3465\|32963\|105829	Program Epic \| Story \| Task \| \| \| \| \| \|	24	24
107107	32963	Task		3465\|32963\|107107	Program Epic \| Story \| Task \| \| \| \| \| \|	24	24
111741	32963	Task		3465\|32963\|111741	Program Epic \| Story \| Task \| \| \| \| \| \|	24	24

rajendraongole1 · ‎10-17-2024

Hi @k_rahul_g - Ensure that your BaseData table and the Parent_Child table have the necessary relationship based on the WorkItemID and ParentID. This will help DAX understand the hierarchy.
Recursive Summation Using a Measure the Effort Esitmate across all child work items:

Total Effort Recursive =
VAR CurrentWorkItem = MAX(BaseData[WorkItemID])
VAR ChildEfforts =
CALCULATE (
SUMX (
FILTER (
BaseData,
BaseData[ParentID] = CurrentWorkItem
),
[Effort Estimate]
)
)
VAR RecursiveChildEfforts =
CALCULATE (
SUMX (
FILTER (
Parent_Child,
Parent_Child[Parent ID] = CurrentWorkItem
),
[Total Effort Recursive]
)
)
RETURN [Effort Estimate] + ChildEfforts + RecursiveChildEfforts

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

View solution in original post

rajendraongole1 · ‎10-17-2024

Hi @k_rahul_g - Ensure that your BaseData table and the Parent_Child table have the necessary relationship based on the WorkItemID and ParentID. This will help DAX understand the hierarchy.
Recursive Summation Using a Measure the Effort Esitmate across all child work items:

Total Effort Recursive =
VAR CurrentWorkItem = MAX(BaseData[WorkItemID])
VAR ChildEfforts =
CALCULATE (
SUMX (
FILTER (
BaseData,
BaseData[ParentID] = CurrentWorkItem
),
[Effort Estimate]
)
)
VAR RecursiveChildEfforts =
CALCULATE (
SUMX (
FILTER (
Parent_Child,
Parent_Child[Parent ID] = CurrentWorkItem
),
[Total Effort Recursive]
)
)
RETURN [Effort Estimate] + ChildEfforts + RecursiveChildEfforts