Optimal way to calculate semi additive measure on a periodic snapshot model (on a tabular model)

[6mon]

Let's imagine a star schema that has a fact table and 3 dimensions : date, project and ressource. The fact table has 2 measures : costs and quantities.

Here's the twist : the costs are linked to ressources and the quantities to project.

So for one day, there could be 2 entries in the fact table :

• 200$ and 100 units for resource A, Project 1
• 50$ and 100 units for resource B, Project 1

Project 1 now has 100 units done and costs 250$.

First question : are the units semi-additives? Or is it something else?

Second question : what's the best way to calculate those in DAX ? (Import Tabular model, live connection power BI)

My current strategy is :

QUANTITY :=
SUMX(
    SUMMARIZE(
        FACT;
        FACT[ID_DIM_PROJECT];
        FACT[ID_DIM_DATE];
        "QUANTITY";
        FIRSTNONBLANK(FACT[QUANTITY_RESSOURCE];1)
    );
    [QUANTITY]
)

but it's slow. Almost unusable.

Also (and it might be important) I chose a periodic snapshot model : one entry per project per ressource per day. Is it a bad idea to only keep the entries that have a measure value? According to the literature there should be 'empty' rows for those days but it represents millions of rows in my case, kinda of a waste...

And it gets worst when I get to the cumulative measures such as : 

QUANTITY_CUM:=
IF(
    min(DIM_DATE[THE_DATE]) <= [PROJECT_END_DATE]+1;
    CALCULATE(
        SUMX(
            FILTER(
                ALL(DIM_DATE[THE_DATE]);
                DIM_DATE[THE_DATE] <= max(DIM_DATE[THE_DATE])
            );
            [QUANTITY]
        )
    )
)

[v-shex-msft]

Hi @6mon,

Actually, I found your formula contains two looping calculation through whole table.

If your datasource contains large amount of records(over millions), it obviously will cause the performance issue during formula calculation.

Maybe you can try to split your formula to two part to reduce the looping amount of each measure calculation.

For example:

First Quantity =
CALCULATE (
    FIRSTNONBLANK ( FACT[QUANTITY_RESSOURCE]; 1 );
    ALLSELECTED ( FACT );
    VALUES ( FACT[ID_DIM_PROJECT] );
    VALUES ( FACT[ID_DIM_DATE] )
)

Total =
SUMX ( ALLSELECTED ( FACT ); [First Quantity] )

Regards,

Xiaoxin Sheng