How to make a dynamic custom SQL query based on given date from the dateslicer?

[Dion_tw]

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!

[BA_Pete]

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

[Dion_tw]

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!

[BA_Pete]

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

[Dion_tw]

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?

[BA_Pete]

If you select your date slicer and go to Visual > Slicer Settings > Options > Style, you can change the slicer type to all sorts of different types:

If you use Between, Before, or After, this will create a slider with date entry fields with calendars behind them. If you make the slicer really small, the slider part of it will disappear and you'll just be left with the date entry elements.

Have a play with that and see if anything works for you.

Pete

[Dion_tw]

Yes, I knew if was possible in the settings. However, I don't have the option 'Between', 'Before' and 'After'. Maybe it has something to do with the parameter/data I am using?

[BA_Pete]

Is your parameter defined as Text type instead of Date type?

Pete