Forum Discussion

Andy__'s avatar
Andy__
Frequent Visitor
2 years ago

User input for dynamic SQL Queries in Power BI desktop?

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-to-pass-an-ID/m-p/871398#M417945

 

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.

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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__'s avatar
      Andy__
      Frequent Visitor
      2 years ago

      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.