See this post for the functional difference between ISCROSSFILTERED and HASONEVALUE.
This DAX query on AdventureWorks runs in less than a second (SP1).
DEFINE
MEASURE 'Date'[Bad Measure] =
COUNTROWS(GENERATE(ALL('Internet Sales'), VALUES('Date'[Week Of Year])))
MEASURE 'Date'[Conditional Evaluation] =
IF(ISCROSSFILTERED('Product'[Product Name]), [Bad Measure], 0)
// IF(HASONEVALUE('Product'[Product Name]), [Bad Measure], 0)
EVALUATE
ADDCOLUMNS(
VALUES('Date'[Calendar Year])
,"Conditional Evaluation", [Conditional Evaluation] //('Product'[Product Name] = "Mountain Pump")
)
ORDER BY 'Date'[Calendar Year]
Uncomment the product filter ['Product'[Product Name] = “Mountain Pump”] to include it. Now it takes about 8 seconds. Looking at the query, this makes sense because it has to call the “Bad Measure”. This suggests that IF ISCROSSFILTERED is optimized to perform conditional logic only when necessary. This behavior is similar to short-circuit evaluation, but not quite the same thing.
Now comment out the line containing ISCROSSFILTERED and uncomment the line containing HASONEVALUE. It takes about 8 seconds whether the product filter is applied or not. This suggests that using IF HASONEVALUE is not optimized; it does perform conditional logic unnecessarily.
Having watched Marco Russo’s Inside DAX Query Plans session from the PASS 2012 Summit, I ran Profiler traces to confirm this theory. For the above query at least, this does seem to be the case.
- Even when the product filter is not applied, IF HASONEVALUE executes a Vertipaq SE Query on ‘Internet Sales’[RowNumber]. RowNumber is an internal, unique column on every table.
- The physical DAX query plan includes the following operation, which I believe means it is iterating 19.2 million rows for the formula engine to deal with.
Spool_Iterator<Spool>: IterPhyOp IterCols(0, 108, 136)('Date'[Calendar Year], 'Internet Sales'[RowNumber], 'Date'[Week Of Year]) #Records=19206564 ...
Note: 19,206,564 = 60,398 (number of rows in Internet Sales) * 53 (number of distinct Week of Year values) * 6 (number of rows in the resultset)
Needless to say, none of this happens with IF ISCROSSFILTERED when the product filter is not applied. There are less Vertipaq SE Queries, and the largest Spool_Iterator operation in the physical plan is for 6 rows (the number of rows in the resultset).
