Running total yields terrible performance

[6mon]

I get the results I need using this

SUMX(
    FILTER(
        ALL(DIM_DATE[THE_DATE]);
        DIM_DATE[THE_DATE] <= max(DIM_DATE[THE_DATE])
    );
    [QUANTITY]
)

but the performance is terrible. 

The dev fact table contains a mere 2000 rows, the prod is close to 3 000 000... 

Are those scans 'normal' ?

QUANTITY is defined as such : 

QUANTITY:=
SUMX(
    GROUPBY(
        FACT_COST_RESOURCE_CATEGORY;
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_ACTIVITY];
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_PROJECT];
        FACT_COST_RESOURCE_CATEGORY[ID_DIM_DATE];
        "QTY";
        MINX(CURRENTGROUP();[QUANTITY_RES_CAT_PERIOD_COL])
    );
    [QTY]
)

[QUANTITY_RES_CAT_PERIOD_COL] being a semi-additive column

[Anonymous]

@6mon 

I think the issue is that you are using two iterators (SUMX and GROUPBY) over your Fact table in the Quantity Measure.  As a test, what happens if you just make quantity a simple SUM or a constant? If that works faster, then we have a better idea on that it's the quantity measure causing the problem.

I"m not sure on your dataset, but maybe using ADDCOLUMNS to a SUMMARIZE function instead of the groupby could work?  Or maybe pushing some the calculationts to Power Query so the bulk of the complexity is done at refresh time. Just thinking out loud here...

[Mariusz]

Hi @6mon,

Try CALCULATE version if its any better.

CALCULATE(
    [QUANTITY],
    FILTER(
        ALL(DIM_DATE[THE_DATE]);
        DIM_DATE[THE_DATE] <= max(DIM_DATE[THE_DATE])
    )
)

Hope this helps
Mariusz