topic CALCULATE() or iterator function in DAX Commands and Tips

CALCULATE() or iterator function

lbendlin — Thu, 09 Jul 2020 11:39:22 GMT

There seems to be some personal preference involved with the use of CALCULATE(MAX()) versus MAXX() etc. I am still trying to understand the arguments pro and contra (as well as the context transition differences). I looked at the query plans in DAX Studio for the following versions of the same measure

var p=SELECTEDVALUE('Table'[pid]) var c=CALCULATE(max('Table'[date]),all('Table'),'Table'[pid]=p,'Table'[value]="1") var p=SELECTEDVALUE('Table'[pid]) var c1=maxx(filter(all('Table'),'Table'[pid]=p && 'Table'[value]="1"),'Table'[date])

They yield the same result. The query plan for the calculate() version has 21 lines and two scans. The final query is

SELECT MAX ( 'Table'[date] ) FROM 'Table' WHERE 'Table'[pid] = 123 VAND 'Table'[value] = '1';

The query plan for the maxx() version has only 12 lines, but the final query is

SELECT MAX ( 'Table'[date] ) FROM 'Table' WHERE 'Table'[value] = '1' VAND ( PFCASTCOALESCE ( 'Table'[pid] AS INT ) = COALESCE ( 'CallbackDataID ( p ) ' () ) ) ;

And the CallbackDataID is all jumpy and colorful, telling me it rather shouldn't be there.

Granted, my test data set is tiny, but isn't that a sign that in this scenario calculate() produces the better query?

Edit: Adding TREATAS() to the mix.

var p=SELECTEDVALUE('Table'[pid]) var c2=CALCULATE(max('Table'[date]),all('Table'),TREATAS({(p,"1")},'Table'[pid],'Table'[value]))

It produces the same final query as the filter enumeration, but but seems to perform a little bit leaner.

Re: CALCULATE() or iterator function

AlexisOlson — Mon, 13 Jul 2020 20:45:32 GMT

As a general rule of thumb, it's usually preferred to not use iterator functions where they aren't needed. For many expressions, they will be internally optimized to the same final query but it's much easier to inadvertently write poorly performing code with iterators (e.g. putting IF functions inside of one where using a filter would be more efficient).

I'd recommend reading up on SUM vs SUMX articles for more detail and context.

https://exceleratorbi.com.au/use-sum-vs-sumx/

https://radacad.com/sum-vs-sumx-what-is-the-difference-of-the-two-dax-functions-in-power-bi

Re: CALCULATE() or iterator function

Anonymous — Wed, 15 Jul 2020 08:26:02 GMT

If you read The Definitive Guide to DAX by The Italians, you'll find a chapter about optimization. CALCULATE with simple (Boolean) filters is ALWAYS preferred to FILTER due to internal optimizations. Simple as that.

Best
D