Forum Discussion
DirectQuery (bug?): all possible Date filter values are being output to SQL
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????