Forum Discussion
How to make a dynamic custom SQL query based on given date from the dateslicer?
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
Thanks a lot! I managed to get it to work. However, I now have to 'dropdown-slicers' where a user has to scroll down really far to check the appropiate dates.. Is it possible to create a dates slicer where they have a date picker menu to easil y pick dates?
