Optimizing hierarchy sum column

[bistabil]

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#..., 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?

[v-chuncz-msft]

@bistabil 

You may take a look at links below.

• https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance
• https://www.sqlbi.com/topics/optimization/