Forum Discussion
DirectQuery (bug?): all possible Date filter values are being output to SQL
We are making some small improvements in the product to handle cases where the large IN slice can be eliminated. However, there could still be a valid use-case where we have large IN slice.
Have you tried rewriting the SQL query with the large IN slice into an equivalent range filter? Does that improve the query performance for you on the SQL side?
Running the SQL in SSMS, if I change:
WHERE ( ([t1].[DateKey] IN (CAST( '20140315 00:00:00' AS datetime),CAST( '20140206 00:00:00' AS datetime),CAST( '20140620 00:00:00' AS datetime),CAST( '20141118 00:00:00' AS datetime),
to:
WHERE t1.Year = '2015'
The execution time changes from 4m52s to 1 second.
([t1] is the Date table)
Thanks for confirming that.
I'm thinking the root cause may be a Measure containing ALL(), for example:
AP Balance =
CALCULATE (
-sum('3 Accounts Payable'[Amount Master]),
FILTER (
ALL ( 'Date' ),
'Date'[DateKey] <= MAX ( 'Date'[DateKey] )
)
)If you think about it, perhaps this makes sense: when I set a slicer filter on [Date].[Year], the engine has no way of knowing that [Date].[Year] = YEAR([Date].[DateKey]), at least not in my case as the data is physically populated in the underlying SQL table.
So the question now becomes: is there a way for me to re-architect this such that in can infer that fact and properly optimize this query?
