topic Re: CALCULATE Challenge - Round 1 in DAX Commands and Tips

CALCULATE Challenge - Round 1

Greg_Deckler — Fri, 03 May 2024 21:31:44 GMT

For those haters of No CALCULATE like AlexisOlson, 😅, I challenge you to create measures with an explicit CALCULATE that for the prescribed scenarios perform anywhere close to the same speed as the very simple No CALCULATE measures that perform date intelligence calculations. Here are the rules:

Must contain an explicit CALCULATE
The explicit CALCULATE must not be superfluous, i.e. CALCULATE(SUM('Table'[Value])) if using SUM('Table'[Value]) would do just fine.

A sample No CALCULATE measure is:

NC Intenet Sales (YTD) = VAR __Date = MAX('Dates'[Date]) VAR __Year = YEAR(__Date) VAR __Table = SUMMARIZE( ALL('FactInternetSales'), 'FactInternetSales'[OrderDate], "__Year", YEAR([OrderDate]), "__Sales", SUM('FactInternetSales'[SalesAmount])) VAR __Result = SUMX(FILTER(__Table, [__Year] = __Year && [OrderDate] <= __Date),[__Sales]) RETURN __Result

The PBIX file is attached below. There are 16 measures for calculating such things as previous year to date, previous month to date, previous week to date, etc. Here is a reference video that also describes the scenario and original performance results:

Re: CALCULATE Challenge - Round 1

Anonymous — Wed, 24 Apr 2024 06:10:29 GMT

HI Greg_Deckler,

Thanks for your sharling, I think these technical research will help other user to understanding how dax calculate.

Regards,

Xiaoxin Sheng

Re: CALCULATE Challenge - Round 1

AlexisOlson — Mon, 22 Apr 2024 16:02:30 GMT

I've noticed the model involved can make a big difference in which approach works best and I don't fully understand why yet, so I'm attaching another (much bigger) model I've been tinkering with for additional comparisons.

Re: CALCULATE Challenge - Round 1

Greg_Deckler — Mon, 22 Apr 2024 16:26:53 GMT

AlexisOlson Big difference I see is that the visual doesn't go to the Date granularity, which means it isn't the same scenario.

Re: CALCULATE Challenge - Round 1

AlexisOlson — Mon, 22 Apr 2024 18:31:38 GMT

Good point on the granularity. Even matching granularity though, there are still different behaviors between files that I don't understand (in particular, TOTALYTD performs similarly to your measure in Contoso).

Re: CALCULATE Challenge - Round 1

lbendlin — Mon, 22 Apr 2024 20:42:49 GMT

In general you don't want to include the syntax sugar in your tests. Keep to the basic functions.

Re: CALCULATE Challenge - Round 1

Greg_Deckler — Mon, 22 Apr 2024 21:27:24 GMT

AlexisOlson I'll have to look closer but both have a data table marked as a date table. The Dates table in Contoso though has significantly less rows in it. Triple in fact.

Re: CALCULATE Challenge - Round 1

AlexisOlson — Tue, 23 Apr 2024 00:03:42 GMT

There seems to be something pathological going on that's making the query planner do weird things with simpler measures. I'd really like to figure out what as I think that knowledge may be broadly applicable in measure optimization. It should not be doing this (some sort of cross-join?).

I got the above when using this code:

VAR _CurrYear = SELECTEDVALUE ( Dates[Year] ) VAR _CurrDate = SELECTEDVALUE ( Dates[Date] ) VAR _Result = CALCULATE ( [Internet Sales], Dates[Year] = _CurrYear, Dates[Date] <= _CurrDate ) RETURN _Result

Using WINDOW is better but it still has steps with 1.4 million records.

CALCULATE ( [Internet Sales], WINDOW ( 1, ABS, 0, REL, ORDERBY ( Dates[Year], ASC, Dates[Date], ASC ), PARTITIONBY ( Dates[Year] ) ) )

Can any DAX masters like marcorusso or jeffrey_wang shed some light?

Re: CALCULATE Challenge - Round 1

Greg_Deckler — Tue, 23 Apr 2024 00:23:06 GMT

Re: CALCULATE Challenge - Round 1

AlexisOlson — Tue, 23 Apr 2024 00:42:37 GMT

I think this should qualify, though it isn't super clean.

VAR _AllDates_ = SUMMARIZE ( ALL ( FactInternetSales ), Dates[Year], Dates[Date] ) VAR _DateRange_ = WINDOW ( 1, ABS, 0, REL, _AllDates_, ORDERBY ( Dates[Date] ), PARTITIONBY ( Dates[Year] ) ) VAR _Result = CALCULATE ( [Internet Sales], _DateRange_ ) RETURN _Result

Re: CALCULATE Challenge - Round 1

Greg_Deckler — Tue, 23 Apr 2024 03:39:10 GMT

AlexisOlson I'll have a look in the morning. But if it does work, I mean, thank God it's such an obvious and easy solution that even a DAX novice could understand and doesn't rely on a function introduced within that last year which pretty much means the solution was unsolvable for 7 years or so...

Re: CALCULATE Challenge - Round 1

tamerj1 — Tue, 23 Apr 2024 05:52:51 GMT

AlexisOlson
Exactly.
What the formula engine is trying to do is to create the following table:

Date

Date YTD

Sum of SalesAmount

Then it does the aggregation over Date.

With help of some mathmatics, given that the Date table is 7,670 rows the formula engine would produce a crossjoin table of around (1/2 )* (7,670) * (7,670) = 29,414,450 rows. This is simply the area of a triangle.
In opttimized versions, the corossjoin is happening to only the dates that do exist in the fact tabe (1,124) rows. That would result in a table of around (1/2 )* (1,124 * (1,124) = 6,316,680 rows.
This is why the cardinality of the Dates[Date] and FactInternetSales[Order Date] do matter alot.

Re: CALCULATE Challenge - Round 1

tamerj1 — Tue, 23 Apr 2024 14:46:15 GMT

AlexisOlson

What the formula engine is trying to do is to create the following table:

Date

Date YTD

Sum of SalesAmount

Then it does the aggregation over Date.

Usually I use the Window function following a NoCALCULATE approach by pre-calculating values then pushing all other calculation to the formula engine. For example the following produces the simplist and fastest query plan along with the minimum number of storage engine queries among all other solutions.

Internet Sales (YTD) Window = VAR AllDateSales = SUMMARIZE( ALL ( FactInternetSales ), Dates[Year], Dates[Date], "@Amount", SUM ( FactInternetSales[SalesAmount] ) ) VAR Result = SUMX ( WINDOW ( 0, ABS, 0, REL, AllDateSales, ORDERBY ( Dates[Date] ), PARTITIONBY ( Dates[Year] ) ), [@Amount] ) RETURN Result

I would however say that your WINDOW-CALCULATE solution matches the performance of other NoCALCULATE solutions so I vote for it as an acceptable solution.

Re: CALCULATE Challenge - Round 1

Greg_Deckler — Tue, 23 Apr 2024 12:35:29 GMT

AlexisOlson In my testing looking at the DAX query in Performance Analyzer this seems consistently about 40%-50% slower than the fastes No CALCULATE approach. But, we're quibbling over 10's of ms here so I feel like this passes. However, there are still 15 other date intelligence measures in the file that are part of the challenge.

Re: CALCULATE Challenge - Round 1

AlexisOlson — Tue, 23 Apr 2024 14:10:31 GMT

thank God it's such an obvious and easy solution that even a DAX novice could understand and doesn't rely on a function introduced within that last year which pretty much means the solution was unsolvable for 7 years or so...

WINDOW isn't required. A plain filter works fine too and has been around since the beginning.

VAR _AllDates_ = SUMMARIZE ( ALL ( FactInternetSales ), Dates[Date] ) VAR _CurrDate = MAX ( Dates[Date] ) VAR _CurrYear = YEAR ( _CurrDate ) VAR _DateRange_ = FILTER ( _ALLDates_, YEAR ( Dates[Date] ) = _CurrYear && Dates[Date] <= _CurrDate ) VAR _Result = CALCULATE ( [Internet Sales], _DateRange_ ) RETURN _Result

However, there are still 15 other date intelligence measures in the file that are part of the challenge.

I'm not really interested in doing all 15 other ones as I don't think they'll reveal much that this YTD example doesn't already. If there is one that you think is meaningfully different, let me know.

Re: CALCULATE Challenge - Round 1

Greg_Deckler — Tue, 23 Apr 2024 15:03:40 GMT

AlexisOlson A bit worse, a little over 50% without WINDOW but still within acceptable limits. I'll take the technique and apply it to the other 15 calculations and let you know if there is an issue. The interesting thing is that if you use a more traditional CALCULATE approach like below, the timing increases to nearly twice that of No CALCULATE:

Alexis Olson TYD CALCULATE 3 = VAR _CurrDate = MAX ( Dates[Date] ) VAR _CurrYear = YEAR ( _CurrDate ) VAR _Result = CALCULATE ( [Internet Sales], FILTER ( SUMMARIZE ( ALL ( FactInternetSales ), Dates[Date] ), YEAR ( Dates[Date] ) = _CurrYear && Dates[Date] <= _CurrDate ) ) RETURN _Result

The other interesting thing about this approach is that you are pretty much destroying the star schema or at least not leveraging it in the slightest. You effectively create a new table that includes all rows in the fact table summarized by Date and then create a filter clause over that. So this kind of says to me that the mantra of the star schema coupled with CALCULATE as being the silver bullet for all things DAX is effectively nonsense because you basically have to blow-up the star schema to get CALCULATE to function at any reasonable performance level.

Finally, I don't think it's intuitive that the SUMMARIZEd table includes all dates in it considering the filter context within a row within the table is to a specific date. That seems weird to me although I imagine it is because using SUMMARIZE against ALL of the fact table and then by Date brings all of those dates back into context somehow versus the individual date at the visual row.

Re: CALCULATE Challenge - Round 1

tamerj1 — Tue, 23 Apr 2024 15:13:46 GMT

Greg_Deckler , AlexisOlson

I would say that both Alex's solutions (WINDOW and SUMMARIZE) look more like a NoCALCULATE approach.
The only difference is replacing

SUMX ( __TableVar, 'Table'[Column] )

with

CALCULATE ( SUM ( 'Table'[Column] ), __TableVar )

in fact once filter ALL ( 'Table' ) is used, CALCULATE remains there with absolutely no meaning of existence.

Re: CALCULATE Challenge - Round 1

AlexisOlson — Tue, 23 Apr 2024 15:56:42 GMT

The other interesting thing about this approach is that you are pretty much destroying the star schema or at least not leveraging it in the slightest. You effectively create a new table that includes all rows in the fact table summarized by Date and then create a filter clause over that. So this kind of says to me that the mantra of the star schema coupled with CALCULATE as being the silver bullet for all things DAX is effectively nonsense because you basically have to blow-up the star schema to get CALCULATE to function at any reasonable performance level.

Finally, I don't think it's intuitive that the SUMMARIZEd table includes all dates in it considering the filter context within a row within the table is to a specific date. That seems weird to me although I imagine it is because using SUMMARIZE against ALL of the fact table and then by Date brings all of those dates back into context somehow versus the individual date at the visual row.

The SUMMARIZE works on the expanded fact table, which leverages the star schema. I find using SUMMARIZE this way useful in many situations.

This situation where you have a visual with thousands of rows does indeed seem to be a pathological case for basic CALCULATE patterns. There are other situations where those same patterns will outperform the No CALCULATE approach in the post (see the Contoso example I uploaded, for example). I'd expect Tamer's WINDOW solution to work well broadly.

Re: CALCULATE Challenge - Round 1

lbendlin — Tue, 23 Apr 2024 16:01:17 GMT

Don't forget that the star schema is mostly smoke and mirrors, and that behind the scenes everything is based on expanded tables anyway.

Re: CALCULATE Challenge - Round 1

Greg_Deckler — Tue, 23 Apr 2024 18:51:05 GMT

tamerj1 Wow, hmm, there was that second rule. This formula (below) produces the same result as AlexisOlson's orignal which technically makes the CALCULATE superfluous. That said, the CALCULATE does not JUST wrap a function that would work without it so I think that going strictly by the rules specified Alexis' formula technically meets the specified criteria.

Alexis Olson TYD CALCULATE 4 = VAR _AllDates_ = SUMMARIZE ( ALL ( FactInternetSales ), Dates[Date] ) VAR _CurrDate = MAX ( Dates[Date] ) VAR _CurrYear = YEAR ( _CurrDate ) VAR _DateRange_ = FILTER ( _ALLDates_, YEAR ( Dates[Date] ) = _CurrYear && Dates[Date] <= _CurrDate ) VAR _Result = SUMX ( _DateRange_, [Internet Sales]) RETURN _Result