Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
15 | |
10 |
User | Count |
---|---|
57 | |
50 | |
44 | |
21 | |
19 |