Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
howellchrisj
Frequent Visitor

Direct Query Date Slicer - Generated SQL is different

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

 

howellchrisj_0-1639670051410.png


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

howellchrisj_0-1639671125402.png

 

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

howellchrisj_1-1639671428179.png

 

4 REPLIES 4
v-xiaoyan-msft
Community Support
Community Support

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.

@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
Super User
Super User

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.