Forum Discussion

bistabil's avatar
bistabil
Regular Visitor
7 years ago

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?