Forum Discussion

kurrysamir's avatar
kurrysamir
Helper I
1 year ago
Solved

Direct Query using Stored Procedure with a date/time parameter

Hi All, I have a single date filter which passes the date to the stored procedure. It works perfectly fine using Import Mode. I need to execute a stored procedure using direct query as the scenario...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi kurrysamir ,
    Thanks for the update!
    The issue is with the slicer style. When you use the "Between" style, even for a single date, Power BI treats it as a range filter, which is not supported for dynamic M parameters. Thats why switching to a drop-down works . It applies a direct, single-value filter, which is supported. Just make sure your slicer is set to List or Drop-down, and the field is from a proper date table.
    If you would like to see support for single-date "Between" slicers in the future, feel free to suggest it on the Power BI Ideas forum. Feedback submitted through these channels is frequently reviewed by the product teams and can contribute to meaningful improvements.

    Hope this helps.If so,consider accepting it as solution.

    Regards,
    Pallavi.

kurrysamir's avatar
kurrysamir
Helper I
1 year ago

Hi Pallavi,

I would be needing this as a date filter, as using a drop-down to traverse dates behind 3-4 months wont be a feasible solution.

Please correct my understanding here, if use the Import mode on the stored procedure I get the data for that particular date and hence on the charts. By using Incremental refresh can I persist the historical data for the last one-more years? By doing this we ensure we have the latest data for the selected date today would persist, so on next day the user wants to select yesterdays data. Its still available in Power BI Staging.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi kurrysamir ,
Thank you again for the follow-up!

Switching to Import mode with Incremental Refresh is a good fit for your scenario with this setup.

You may run your stored procedure or table-valued function to fetch data for the selected date.

Incremental Refresh will automatically load and persist data for past dates.

You may schedule daily refreshes so new data is added each day, and users may freely filter past and current dates using a full "Between" date range slicer , no limitations like in DirectQuery.

Just make sure your stored procedure supports date-based filtering and set up the RangeStart/RangeEnd parameters correctly in Power BI.

Hope this resolve your query.