topic Re: No CACULATE challenge in DAX Commands and Tips

No CACULATE challenge

AlexisOlson — Wed, 22 Mar 2023 16:46:49 GMT

For those haters of CALCULATE like Greg_Deckler, I challenge you to create a measure without CALCULATE that performs anywhere close to the same speed as this very simple measure that switches to an inactive relationship. It must also not break if a simple filter from any of the dimension tables is applied.

Sales Amount (Delivered) = CALCULATE ( SUM ( Sales[SalesAmount] ), USERELATIONSHIP ( Sales[DeliveredDateKey], 'Calendar'[DateKey] ) )

Please use the attached file.

Report preview:

As expected, we can see that sales during periods without a discount promotion have products delivered in promotion months.

Re: No CACULATE challenge

Greg_Deckler — Wed, 22 Mar 2023 17:20:26 GMT

AlexisOlson

Sales Amount (Delivered) NC = VAR __Dates = SELECTCOLUMNS('Calendar',"DateKey",[DateKey]) VAR __Table = FILTER(ALLSELECTED('Sales'), [DeliveredDateKey] IN __Dates) VAR __Result = SUMX(__Table, [SalesAmount]) RETURN __Result

In my testing the measures return within 50 milliseconds of one another.

Re: No CACULATE challenge

AlexisOlson — Wed, 22 Mar 2023 18:01:36 GMT

Nice!

Now try it without the slicer filtering and see how the performance compares. 🙂

Re: No CACULATE challenge

Greg_Deckler — Wed, 22 Mar 2023 18:16:47 GMT

AlexisOlson This one doesn't use CALCULATE either (LOL!) and is actually 3 times faster than the original measure from a DAX Query perspective.

NC3 = VAR __Table = CALCULATETABLE('Sales', USERELATIONSHIP ( Sales[DeliveredDateKey], 'Calendar'[DateKey] )) VAR __Result = SUMX(__Table, [SalesAmount]) RETURN __Result

Re: No CACULATE challenge

tamerj1 — Wed, 22 Mar 2023 18:22:19 GMT

Greg_Deckler

🤣🤣🤣

Re: No CACULATE challenge

AlexisOlson — Wed, 22 Mar 2023 20:17:49 GMT

Greg_Deckler Ha! The performance of that is the same as my CALCULATE version in my testing.

For a final challenge, see if you can write a cumulative version without CALCULATE or CALCULATETABLE that refreshes in under 10 seconds with no slicer filtering. The CALCULATE version refreshes in about 0.2 sec, including all the overhead:

Cumulative Sales Amount (Delivered) = VAR _MaxDate = MAX ( 'Calendar'[DateKey] ) VAR _Result = CALCULATE ( SUM ( Sales[SalesAmount] ), USERELATIONSHIP ( Sales[DeliveredDateKey], 'Calendar'[DateKey] ), ALL ( 'Calendar' ), 'Calendar'[DateKey] <= _MaxDate ) RETURN _Result

Re: No CACULATE challenge

Greg_Deckler — Thu, 23 Mar 2023 01:43:05 GMT

AlexisOlson It's a good more or less single use case where CALCULATE is beneficial but really just because USERELATIONSHIP was coded to work with CALCULATE. USERELATIONSHIP could have been coded to support FILTER as well. It's not really CALCULATE, it's the additional functions that were coded to work with CALCULATE. But, there are plenty of examples of the reverse. Try writing a version of these with only using CALCULATE and no X aggregator that A. Actually works and B. Performs significantly faster.

Write a running total measure that uses a single table data model only using only CALCULATE and no X aggregator
Write a working measure total for a semi-additive measure using only CALCULATE and no X aggregator. Measure Totals, The Final Word - Microsoft Power BI Community
Write a version of Open Tickets measure using only CALCULATE and no X aggregator
Write a Deseasonalized Correlation Coefficient measure using only CALCULATE and no X aggregator
Write a Net Work Days measure using only CALCULATE and no X aggregator
Write a Simple Linear Regression measure formula using only CALULATE and n X aggregator
Write Cthulhu using only CALCULATE and no X aggregator
Write an MTBF measure using CALCULATE and no X aggregator
Write a While loop measure using CALCULATE
Write a For loop measure using CALCULATE
Write a Days of Supply measure using only CALCULATE and no X aggregator
Write Hour Breakdown measure using only CALCULATE and no X aggregator
Write Lookup Value measure using only CALCULATE and no X aggregator
Write Overworked measure using only CALCULATE and no X aggregator

I could go on, I only got through 3 pages of 11 of the Quick Measure Gallery.

The point here is the the No CALCULATE approach is a far better and more flexible approach to writing DAX that allows you to solve real-world problems, problems that CALCULATE could never hope to touch. Yes, there are times when CALCULATE is a good idea for one reason or another. But, this fixation the DAX community has on CALCULATE is unhealthy. It makes DAX harder to learn and breeds this crazy culture where people want to use it everywhere when it is simply making their lives harder and is absolutely unnecessary the vast majority of the time when the simple approach of:

Create some VAR's
Create a Table VAR
X Aggregator

Will solve the VAST majority of problems in DAX without ever needing CALCULATE. Simple. No reason to worry about the internal workings of CALCULATE or context transition or pretty much any of the stuff that people find "hard" about DAX.

Re: No CACULATE challenge

tamerj1 — Thu, 23 Mar 2023 05:26:14 GMT

Hi AlexisOlson & Greg_Deckler
This one uses neither CALCULATE nor CALCULATETABLE and performs faster than the original one

Sales Amount (Delivered) 2 = VAR SelectedSales = ALLSELECTED ( Sales ) VAR SummarySales = SUMMARIZE ( SelectedSales, Sales[DeliveredDateKey], "@SalesAnount", SUM ( Sales[SalesAmount] ) ) RETURN SUMX ( VALUES ( 'Calendar'[DateKey] ), VAR FilteredSales = FILTER ( SummarySales, [DeliveredDateKey] = 'Calendar'[DateKey] ) RETURN SUMX ( FilteredSales, [@SalesAnount] ) )

Re: No CACULATE challenge

tamerj1 — Thu, 23 Mar 2023 05:45:09 GMT

AlexisOlson
Here you go

Cumulative Sales Amount (Delivered) 2 = VAR SelectedSales = ALLSELECTED ( Sales ) VAR SummarySales = SUMMARIZE ( SelectedSales, Sales[DeliveredDateKey], "@SalesAnount", SUM ( Sales[SalesAmount] ) ) VAR SummaryDates = SUMMARIZE ( 'Calendar', 'Calendar'[Year], 'Calendar'[MonthName], "@MaxDate", MAX ( 'Calendar'[DateKey] ) ) RETURN SUMX ( SummaryDates, VAR FilteredSales = FILTER ( SummarySales, [DeliveredDateKey] <= [@MaxDate] ) RETURN SUMX ( FilteredSales, [@SalesAnount] ) )

This is faster than yours. I guess Greg_Deckler has a very good point 😉

Re: No CACULATE challenge

tamerj1 — Thu, 23 Mar 2023 09:18:36 GMT

Greg_Deckler
Totally agree

Re: No CACULATE challenge

Greg_Deckler — Thu, 23 Mar 2023 12:24:36 GMT

tamerj1 Wow! Super impressive! That is lightning fast!! I ran 10-15 tests and your version has DAX query timing that is on average 2-3 times faster than CALCULATE ( ~75 milliseconds versus ~200 milliseconds. Overall time was consistently 100 milliseconds faster. Anonymous

The technique also works for the original problem and performs almost identically to the CALCULATE version.

NC4 = VAR __SelectedSales = ALLSELECTED( 'Sales' ) VAR __SummarySales = SUMMARIZE( __SelectedSales, [DeliveredDateKey], "__SalesAmount", SUM( [SalesAmount] ) ) VAR __Dates = DISTINCT('Calendar'[DateKey]) VAR __Result = SUMX( FILTER( __SummarySales, [DeliveredDateKey] IN __Dates), [__SalesAmount]) RETURN

Once again, CALCULATE goes down in defeat.

Re: No CACULATE challenge

Greg_Deckler — Thu, 23 Mar 2023 12:32:33 GMT

tamerj1 One other thing, on a whim decided to replace SUMMARIZE with GROUPBY. SUMMARIZE is waaaaaayyyy more performant than GROUPBY. Like orders of magnitude more performant.

Re: No CACULATE challenge

tamerj1 — Thu, 23 Mar 2023 12:40:24 GMT

Greg_Deckler

To be honest somehow I cheated 😅

I couldn't do that without context transition so I thought ok, SUMMARIZE can do that as well. Yes indeed GROUPBY does not perform context transition therefore would perform slower in this case.

Re: No CACULATE challenge

tamerj1 — Thu, 23 Mar 2023 12:42:45 GMT

Greg_Deckler

Yes I actually placed the solution of the first problem in a separate reply right before the the solution of the 2nd problem.

Re: No CACULATE challenge

Greg_Deckler — Thu, 23 Mar 2023 13:09:00 GMT

tamerj1 Somehow I missed that, the way these forums do threading is mildly infuriating at times.

Re: No CACULATE challenge

AlexisOlson — Thu, 23 Mar 2023 13:53:40 GMT

Greg_Deckler X aggregators are absolutely essential. I have no interest in comparing No CALCULATE vs No X aggregator since I'd rather not handicap myself at all.

Re: No CACULATE challenge

AntrikshSharma — Thu, 23 Mar 2023 14:19:28 GMT

AlexisOlson Good challenge! I am a Pro CALCULATE but recommend users not to stick with a single side.
Here are my solutions:

Solution 1 using INNERJOIN, performs in 40ms without any filter

Delivered Amount AS = VAR Dates = SELECTCOLUMNS ( VALUES ( 'Calendar'[DateKey] ), "DeliveredDateKey", 'Calendar'[DateKey] & "" ) VAR SalesByDelivery = SELECTCOLUMNS ( SUMMARIZE ( ALLSELECTED ( Sales ), Sales[DeliveredDateKey], "Sum", SUM ( Sales[SalesAmount] ) ), "DeliveredDateKey", [DeliveredDateKey] & "", "Sales", [Sum] ) VAR Result = SUMX ( NATURALINNERJOIN ( Dates, SalesByDelivery ), [Sales] ) RETURN Result

Solution 2 using CONTAINSROW or the IN performs in 23 ms

Delivered Amount AS 2 = VAR SalesByDeliveryDate = SUMMARIZE ( ALL ( Sales ), Sales[DeliveredDateKey], "TotalSales", SUM ( Sales[SalesAmount] ) ) VAR CurrentYearRows = FILTER ( SalesByDeliveryDate, CONTAINSROW ( VALUES ( 'Calendar'[DateKey] ), Sales[DeliveredDateKey] ) ) VAR Result = SUMX ( CurrentYearRows, [TotalSales] ) RETURN Result

Cumulative Total - I added a YearMonth column in the Dates table, since we are not showing date level I decided to reduce granularity, takes about 50ms.

CT DeliveredAmount AS = VAR SalesByDelivery = SUMMARIZE ( ALL ( Sales ), Sales[DeliveredDateKey], "@TotalSales", SUM ( Sales[SalesAmount] ), "@YearMonth", YEAR ( Sales[DeliveredDateKey] ) * 100 + MONTH ( Sales[DeliveredDateKey] ) ) VAR Result = SUMX ( FILTER ( SalesByDelivery, [@YearMonth] <= MAX ( 'Calendar'[YearMonth] ) ), [@TotalSales] ) RETURN Result

tamerj1 I tried and your code and it returns incorrect subtotals.

Re: No CACULATE challenge

AlexisOlson — Thu, 23 Mar 2023 14:34:24 GMT

I'm awarding the win to tamerj1 for the original problem. It's about 20ms in testing on my machine instead of 10ms for mine but that's still super fast.

I'm awarding the win to AntrikshSharma for the cumulative challenge since that solution works for subtotals as well.

Re: No CACULATE challenge

Greg_Deckler — Thu, 23 Mar 2023 17:52:42 GMT

AntrikshSharma Really impressive! If there is a restriction that you can't change the data model, then this slight tweak works just as well:

Cumulative Sales Amount (Delivered) AS rev = VAR SalesByDelivery = SUMMARIZE ( ALLSELECTED ( Sales ), [DeliveredDateKey], "@TotalSales", SUM ( [SalesAmount] ), "@YearMonth", YEAR ( [DeliveredDateKey] ) * 100 + MONTH ( [DeliveredDateKey] ) ) VAR Result = SUMX ( FILTER ( SalesByDelivery, [@YearMonth] <= MAX ( 'Calendar'[Year] ) * 100 + MAX( 'Calendar'[MonthOfYear]) ), [@TotalSales] ) RETURN Result

Re: No CACULATE challenge

tamerj1 — Fri, 24 Mar 2023 05:13:15 GMT

AntrikshSharma
Yes you are right I've over looked that but this is an easy fix. The outer SUMX should have been a MAXX

Cumulative Sales Amount (Delivered) 2 = VAR SelectedSales = ALLSELECTED ( Sales ) VAR SummarySales = SUMMARIZE ( SelectedSales, Sales[DeliveredDateKey], "@SalesAnount", SUM ( Sales[SalesAmount] ) ) VAR SummaryDates = SUMMARIZE ( 'Calendar', 'Calendar'[Year], 'Calendar'[MonthName], "@MaxDate", MAX ( 'Calendar'[DateKey] ) ) RETURN MAXX ( SummaryDates, VAR FilteredSales = FILTER ( SummarySales, [DeliveredDateKey] <= [@MaxDate] ) RETURN SUMX ( FilteredSales, [@SalesAnount] ) )