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.
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).