Dynamic SQL Query with Parameters from URL

[bmanahan]

We want to use Power BI for reporting for our distributors in their back office.  Many of the reports require computationally intensive processes that must be done on the SQL server for each individual distributor.  We cannot have a query that pulls the data for report for a distributors as that would take much too long and pull in so much data the report would operate too slowly.

Ideal solution would be to put the Power BI in an iframe and pass the distributor ID as an input paramater.  The input paramater would then be used for a dynamic SQL query to pull process the report for that specific distributor. 

I am having difficulting figuring out hwo to do this.  I have been able to filter a Power Bi paramater with a URL.  I have been able to create a dynamic SQL query using a Power Query editor parameter.  I have not been able to figure out how to make the dyanmic SQL query based on the Power BI paramater, nor have I been able to sync the Power BI paramater to the Power Query Editor parameter.  I am also open to any other solution to this problem.

[v-junyant-msft · Accepted solution]

Hi @bmanahan ,

May I ask if this is the Power Bi paramater you are referring to:

If yes, then your needs cannot be realized. The Power BI parameter and the Power Query parameter are two different things and there is no way they can be linked together. And Power BI parameter can not be used to create a dynamic SQL query.

You can achieve a similar effect by using DirectQuery and dynamic M query parameters to control your SQL query based on slicer or filter selections in your report:
Bind this table's field to a Power Query parameter as described in this documentation. This allows the selection in a slicer or filter to dynamically adjust the query parameter.
Use a slicer in your report for distributors to select the distributor ID, which then dynamically adjusts the SQL query through the bound parameter.
While this approach does not directly use the URL parameter to adjust the Power Query parameter, it provides a way to dynamically control the data being queried based on user interaction with the report.

As an alternative, consider using Power Automate to trigger your SQL queries with the distributor ID as an input parameter. Power Automate can then process the data as needed and push the results into a Power BI dataset. Use Power Automate to create a flow that triggers the extraction process. You can set the flow to run on a schedule or to be triggered by an event. Within the flow, use the SQL Server connector to execute SQL queries that extract the necessary data based on the distributor ID. You can pass the distributor ID as a parameter to the SQL query to retrieve distributor-specific data.

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

[bmanahan]

Much close now, but I still have issues.  I created a new query to pull in all the distributor ids, I think used the method in the video from the link to bind the paramater.  So I am now set the paramater for my query when I filter the my data on the distributor id table (table name Dist_id, column name Dist_ID).  This is all great.

Now my issue is that I cannot set the filter though a URL.  I tried setting the fitler by add the following at the end of the end of the embded URL, but nothing appears to be fitlering.  &filter=dist_id/dist_id%20eq%201121

Any ideas on what is wrong to get my filter to work?

[bmanahan]

I think I found my problem with the URL string.  It is case sensitive.  Once I fixed that it is working.