Forum Discussion
bistabil
7 years agoRegular Visitor
Optimizing hierarchy sum column
I need to calculate sum of integer value for all child elements in the hierarchy.
The solution posted here, https://community.powerbi.com/t5/Desktop/Summing-Aggregating-on-Parent-Child-Hierarchies/m-p/394500#M179959, solves my issue but it's painfully slow and only sometimes works. Other times it just loads indefinetly or throws out of memory exception.
The measure I'm using is:
Measure =
VAR c =
SELECTEDVALUE(DimWorkItemCurrent[SystemID])
RETURN
CALCULATE(
SUM(FactWorkItemScheduling[Hours]);
ALLSELECTED(DimWorkItemCurrent[SystemID]);
PATHCONTAINS(DimWorkItemCurrent[PBI_WorkItemPath]; c)
)
Even when I narrow down the slicers to only 100 rows it doesn't speed up so I presume the measure still does the calculation across the whole fact table, instead of across filtered rows.
I guess the ALLSELECTED part is not doing what I'm hoping it would do?
1 Reply
- v-chuncz-msftCommunity Support
You may take a look at links below.