Forum Discussion
DirectQuery (bug?): all possible Date filter values are being output to SQL
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?
Well, I tried adding a new calculated (in M) column and using that in the slicer, same problem.
= Table.AddColumn(dbo_DimDate, "YEAR_CALC", each Date.Year([DateKey]), Int64.Type)
Also tried adding a new calculated column in the model and putting a slicer on that, same problem.
Thinking a bit more though....my cannot optimize theory above is actually wrong. The slicer options are basically a "select distinct [SlicerColumn] from the underlying table, the fact that it is (or is not) YEAR(DateKey) is completely irrelevant; all [Date] rows that match can be selected simply by: WHERE [Date].[Year] = {selected slicer value}
Any ORM has to handle this scenario, so I can't understand why it can't be optimized here. What it is doing now is actually far more complicated than the efficient statement would be to generate, so something weird must be going on????
We are considering a general optimization to deal with big OR slices on a date column. Hopefully, that would help you. But, currently, running-total like calculations will have big OR slices by design. Having said that, I would have expected SQL engine to have handled it better.
True, MSSQL should handle that statement better (at the very least the huge IN() statement should be used to create a temp table which is then joined to) but even so, Power BI shouldn't be sending this statement in the first place.
Should I open a bug report for this?