cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
6mon
Helper II
Helper II

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

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]
        )
    )
)
1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors