Create dynamic SQL server query

[Anonymous]

Hi Power BI Community,

I need your support with suggestions on how to create an SQL Query that can change dynamically based on selected options from the user. I found a guide that discusses this titled: Dynamic M Query Parameters in Power BI Desktop - Learn | Power BI, but the guide appears to be irrelevant since I cannot find the "bind to parameter" field in my Model view section.

What I need to do is receive a different set of values populated on my visuals based on a slicer. The slicer holds terms such as 2022/23, 2023/24 ... And by selecting one of the options, data in the query should be updated with records that match the selected term. How can I achieve this?

Thanks in advance for your support.

Cheers.
 

[Anonymous]

Hi @Anonymous ,

@lbendlin  provided a great idea for the solution, the dynamic M query parameter gives the model author more control over the selection of filters to be incorporated into the DirectQuery source query. For more detailed information about query parameters and power bi templates, please refer to the documentation: Deep Dive into Query Parameters and Power BI Templates | Microsoft Power BI Blog | Microsoft Power B....

In addition, you can also use dax logic to generate a dynamic power bi slicer. For specific operation steps, please refer to the document: Dynamic Power BI Slicer Using DAX Logic (mssqltips.com).

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[lbendlin · Accepted solution]

since I cannot find the "bind to parameter" field in my Model view section

Your data source needs to be in Direct Query Mode.  You need to have prepared a parameter for your query.  Then the "Bind"  option will be visible when you select the column to bind to and expand the Advanced section.