Direct Query Date Slicer - Generated SQL is different

[howellchrisj]

I am running into a Direct Query that is taking a while to execute and pull back results when a Date slicer is utilized from the dimDate.Date field.  I currently have the following data model below and the active relationship is between EndDate and Date.  The problem I am running into is when I use the dimDate.date for the slicer, the SQL generated lists all dates using an "in" statement.  When bypassing and just using the facttable.EndDate, it uses <, >, which is much more efficient and more effective.  Would anyone have any ideas of what would need to be changed, so it uses the <> instead of the "in"?  I utilized performance analyzer to determine the DAX/SQL script being executed

This is when it works using the EndDate within the Slicer
--Dax
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('factCombined'[EndDate])),
AND(
'factCombined'[EndDate] >= DATE(2021, 1, 1),
'factCombined'[EndDate] < DATE(2022, 1, 1)
)
)
--SQL
([t10].[EndDate] < CAST( '20220101 00:00:00' AS datetime))
AND
([t10].[EndDate] >= CAST( '20210101 00:00:00' AS datetime))
)

This is when it splits the values out using the dimDate.Date field in the Slicer
--DAX
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('dimDate'[Date])),
AND(
'dimDate'[Date] >= DATE(2021, 1, 1),
'dimDate'[Date] < DATE(2022, 1, 1)
)
)

--SQL
([t10].[EndDate] IN (CAST( '20220101 00:00:00' AS datetime), CAST( '20220102 00:00:00' AS datetime), CAST( '20220103 00:00:00' AS datetime), .... etc....

The EndDate and dimDate.Date are both set to date data types

BTW... this utilizes the standard Slicer and I have it using the Between setting

[v-xiaoyan-msft]

Hi @howellchrisj ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thanks in advance for your kind cooperation!

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[howellchrisj]

@v-xiaoyan-msft , @amitchandak 's solution is a solution but doesn't truly solve the issue.  
I was able to get additional details from Chris Webb that the reason for the splitting of the dates in the SQL was due to the difference in the data sources.  I was able to test this and validate it.  If you have a direct query data set related to an imported data set, for some reason the SQL Generated by Power BI is not effectively created.  I am not sure why this is the case, but for now, it is.

[amitchandak]

@howellchrisj , With an independent date slicer , you can try measure like

Between dates =
var _max = minx(allselected(Date), Date[Date])
var _min = maxx(allselected(Date), Date[Date])
return
calculate(count(Table[Project Number]), Filter(Table,(Table[EndDate] <=_max && Table[EndDate]) >=_min ) )

[howellchrisj]

@amitchandak, thank you for the suggestion and I may have to go with that approach.  Ultimately, I was hoping to leave the data model alone and figure out if I am doing something wrong elsewhere since the underlying SQL generated by PBI changes between the two different fields.