How to pass dynamic date parameter to SQL query through slicer or any alternate solution

[akambliViz]

Hello Team,

There is a requirement of passing a date picker parameter to the report dataset to fetch only the data for that specific date(Single datepicker).

The data is more than 3Million rows which needs to be populated using a SQL query where the date parameter needs to be given by User and then only the query needs to be hit to the DB to fetch the data only for that particular date. 

How can we achive the dynamic parameter which will be selected by User as a single date picker and will be pass in the where clause of the SQL query to fetch only that data for that particular date?

Thanks in advance.  

[Anonymous]

Hi @akambliViz ,

Please refer this document: Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Hope it is helpful for you.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[akambliViz]

Hi @Anonymous ,

Thanks for your response.

I had already went through this link however there are limitation for Direct Query for SQL Server Datasource. Please check below snippet due to which the power bi doesn't show bind to parameter property in Advance section. 😞

Link for the reference.

Bind to parameter not showing in Modeling tab if w... - Microsoft Fabric Community

[Anonymous]

Hi @akambliViz ,

The official document for this feature don't have this limitation now, so please try it.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[akambliViz]

Hi @Anonymous ,

Thanks for your response. I had already tried this the only problem is unable to bind the date parameter in the DimDate table.

Bind to Parameter doesn't show DynamicDate parameter in the list.

it only shows other two text parameters.

Could you please assist.

Thanks in advance.

[miTutorials]

You can setup a dynamic parameter, please see the below tutorial.

Setup Dynamic Parameters to Filter data in your PowerBI Report | MiTutorials - YouTube

[akambliViz]

@miTutorials Thanks so much for your response. In the video the date parameters are selected before loading the resport. However the requirement is to select the single date slicer from home page and then on button click fetch the data only for that selected date.

Data source SQL Query date parameters needs to be pass through about date slicer.

[Uzi2019]

Hi @akambliViz 
Are you using Direct Query or Import mode???

[akambliViz]

Hi @Uzi2019 ,

Thanks for your response.

Currenlty using Import. However how can we achieve this using both Import and Direct query.