User input for dynamic SQL Queries in Power BI desktop?

[Andy__]

Hello

We have a SQL query in a Power BI Desktop file, and this query selects from a database function. The function accepts an input. We would like to allow users to set this input through a visual element, such as a slicer.

The first thought came to mind was to use parameter as I had read it somewhere before.

A similar question was asked a few years back in the following thread.

https://community.fabric.microsoft.com/t5/Desktop/How-to-set-up-input-parameter-field-for-end-user-t...

It did not sound like it was possible in Power BI Desktop.

A few years have passed, and I wonder if it is possible or not to make a SQL query dynamic by allowing user to select an input as the condition for the query?

Thank you.

[v-xinruzhu-msft]

Hi @Andy__ 

If your data source is SQL Server, you can consider to use direct mode to connect to the data source, then set the paramater in power query , it can be edit in Power BI Desktop so that you can implement that filter the database dynamically, you can refer to the following link about how to set the paramater.

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Unlock the power of dynamic parameter binding for ... - Microsoft Fabric Community

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Andy__]

Hello Yolo,

Thanks for your information.  Somehow it did not work for me.  I am using Power BI Desktop May 2021, if that makes a difference.

Here were what I did.

- Created a new Power BI file.

- Selected SQL Server as data source and selected Direct Query.

- Entered the SQL select statement in the query dialog.

- Went to the query editor to create a parameter.

- Edited the query in the Advanced Editor, which appears to have converted the regular SQL statement to the M language.  I was able modify it to use the parameter.

- Went back to Power BI desktop to create a table under the Modeling ribbon.  But the New Table option is greyed out.  Therefore it appears it does not work for me.

Best regards

[v-xinruzhu-msft]

Hi @Andy__ 

After  testing, if your connection mode is direct query, the create new table can be used, try to clear permission and reconnect the sql again.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Andy__]

Hello Yolo,

I created a new file to do the experiement, and I did not save it.  Therefore I cannot clear the permission to check whether it works or not.  However, when I went back to the existing file, I found that the create new table option is available. 

However, the "Bind to parameter" option is not available!

When I looked around and when I looked back at one of your reference links, Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

I found under "considerations and limitations" that:

"

• Dynamic M query parameters are not supported on Power BI Report Server.

"

And we are using Power BI Report Server.

Best regards