CALCULATE() or iterator function

[lbendlin]

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.

[Anonymous]

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

[AlexisOlson]

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