Power BI dynamically feed dates from date slicer to oracle sql query in Power Query M

[kokotas]

I'm new to power bi and trying to teach myself using copilot but I keep running in circles without any outcome. I'd appreciate some help...

I have built my queries to fetch data from an oracle sql server, using a condition where date >= '' and date < ''

I would like to feed the dates to my query from a date slicer visual. I have created two measures for start and end date which correctly hold these dates from the slicer. But I can't find a way to pass these dates to the power query M editor.

Referencing them directly doesn't work. I've tried python, no go either. Trying to pass the measure dates to an intermediate table didn't work either, instead of the slicer values it would show the first date of the month and the current date, for start and end date respectively.

I have also tried a different approach, to build my data model by bringing the necessary tables from my oracle sql db inside power bi and then filter them by the dates in the date slicer. But I then I hit another wall.

I have rewritten the custom functions I have in my oracle sql db to power sql. But every time they would be called to refresh the data, they would query the oracle db instead of my local power bi tables.

Any insight would be welcome.

[Anonymous]

Hi @kokotas ,

You can also configure an incremental refresh or create a Power Automate flow (which requires you to have a Power Automate license) to refresh your data. If the problem has been resolved, please consider accepting replies that you find helpful as solutions. This can help other people with the same question find solutions more quickly.

Best regards,

Mengmeng Li

[kokotas]

Thanks for your help @DataInsights , I ended up importing the necessary tables and columns only from a year back till today, so that data will expand on refresh. Then I added my custom functions logic as new calculated columns. Then added measures for the values I want to show in the report. Filtering data via a date slicer works like a charm now.

[DataInsights]

@kokotas,

Try using dynamic M query parameters:

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters 

[kokotas]

@DataInsights 

Thanks, I have indeed tried that. The problem was I couldn't pass the dates selected in the date slicer to the parameters. How would I go about creating a table for the startdate and another for the enddate according to what is selected in the date slicer? I could only see the correct result for theses dates when using measures. Any attempt I made to bring the slicer values to a table, resulted in startdate being the first date of the current month and the enddate being the current date...

[DataInsights]

@kokotas,

The section of the article "Create tables of values" explains how to create date tables for parameter binding. Once you create the date tables, create slicers using the date tables.