Metrics / semantic layer : what about semi-additive measures

[Fleid]

Hi team,

Sorry I haven't done proper research on that one. I didn't want that stone be left unturned, even if I could not dedicate the time to completely refine my thoughts before forgetting posting.

From what I understand of the current state of metrics, non additive measures will be supported via "derived" calculations (discussion).

Now I'm wondering how we intend to support semi-additive measures. As a quick reminder, a semi-additive measure is a measure that expects different aggregation behaviours depending on the dimension.

A quick example is a bank account balance. Let's say we're a bank manager, we want to sum balance across customers, branches or account types. But when it comes to time, we should not sum anymore, but instead get the last value of the period. Same thinking goes for inventories.

The OLAP engines of old allowed to define semi-additive behaviours at the measure x dimension grain, with options like:

• First / Last child (requires some kind of ordering - like time)
• First / Last non empty (same as above but you get the last existing value rather than nothing)
• By Account (data driven behaviour)
• Average of Children (not the average of leaves - but that only makes sense on dimensions with pre-defined hierarchies)
• Distinct Count of Children (same as above)
• ...

This article shows how this topic is addressed in DAX (Power BI "post aggregation" calculation language).

I'm just scratching the surface of metrics, and I'm wondering how we could define these types of calculations.