Forum Discussion
How to make a dynamic custom SQL query based on given date from the dateslicer?
Hey all,
I have managed to create a custom SQL query that is working fine.
However, the start- and enddate are now hardcoded in the query itself.
I want to make it dynamic, so that when a user uses the 'datepicker'/'dateslicer' in my dashboard. The query uses that start- and enddate. Is this possible?
Kind regards!
7 Replies
Hi Dion_tw ,
It sounds like your best bet would be to set up your model/report using Direct Query:
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery
This will essentially apply any slicers/filters selected to the WHERE clause of an on-the-fly SQL query to your source.
Pete
Thanks for replying!
I already am using directquery to my dashboard. However, when I created a table visual and make the neccesary filters the query needs to load about 60 seconds...
When I make the custom query, it only takes 0.5 seconds. That's why I am trying to just use custom made qeuries so I can make sure to send the most effective query to the database.
Any more tips would be very welcome!
In that case you may have more luck using dynamic M parameters, but it's quite a bit more in-depth as a solution:
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
Pete
