Parameterized report with native SQL queries

[webportal]

Hi,

I'm retrieving data from SQL server with a query like:

SELECT * FROM MY_TABLE
WHERE D_ReferenceDate >= 202312

Is there a way to let the Power BI user choose which reference date from which retrieve the data from?

[webportal]

@dufoq3 

Thanks for your help.

I was thinking about having the Power BI user defining the cutoff date (not Power Query parameters).

[v-jingzhan-msft · Accepted solution]

Hi @webportal 

This can be possible with the help of Dynamic M query parameters in Power BI Desktop. This feature can let you bind a column field to a query parameter which is used in the M query. The column field can be used in a slicer in the report for report users to switch different values. The M code will then be modified by the slicer selections to send different queries to the data source. 

As the dynamic queries will be affected by the slicer selections, the underlying table must be in DirectQuery mode. There are some considerations and limitations you need to be aware of, please read Considerations and limitations section from the linked documentation above. 

Hope this would be helpful. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[dufoq3]

Power Bi Desktop user can edit parameters, but not a user of published report - maybe there is a way, but not in my knowledge 😉

[dufoq3]

Hi, it is possible. Create new parameter.

Edit your Source step:

= Odbc.Query("dsn=Your_SQL_Connection", "SELECT * FROM MY_TABLE WHERE D_ReferenceDate >= '" & DateParameter & "'")

Now you can select your parameter in query panel (left) and enter valid value i.e. 202312 etc.

This is how it works with my data: