cancel
Showing results for
Did you mean:  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]
)
)
)```  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. Announcements #### The Power BI Community Show

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

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th! Top Solution Authors
Top Kudoed Authors
Users online (1,578)