Refresh a Direct Query with PBI visual

[Anonymous]

Hello everybody.

I have a stored procedure in my database (SQL SERVER) that has a parameter.
Could I send and return a direct query containing this procedure, changing the parameter using a visual within the dashboard and without having to click "Refresh"?

I've already tried to use a Power Query parameter, but I couldn't find any way for the user to change this parameter through PowerBi. To do this, the user would have to open Power Query and change it manually.

I know you must be thinking that this would work as a query system, like using an HTML template with PHP for example, and that's the idea, I would just like to know if this is possible within PowerBi, if any of you have already done something similar .

Thank you for everyone's attention.

[rajendraongole1 · Accepted solution]

Hi @Anonymous -Power BI does not natively support dynamic parameter passing for stored procedures without a refresh. However,using parameter tables, views, or paginated reports, can give you a similar experience. Each option has its trade-offs depending on the specific needs and available resources.Power BI still requires a refresh (automatic or manual) to update the results, but this can happen in real-time as long as the dataset is using DirectQuery and the parameter table is being updated via visuals.

you can try below approach:

Create a Parameter Table in SQL: You can create a table in SQL that holds parameter values for the stored procedure. Your visuals (slicers, dropdowns) in Power BI can update this table in real-time using DirectQuery.

Create a SQL Stored Procedure: The stored procedure can use the parameter table to fetch the required value and run the necessary logic. When the user selects a different value in Power BI, the stored procedure picks it up.

Connect DirectQuery to a View or Function: Use DirectQuery to call the result of a view or table-valued function based on the stored procedure, which dynamically references the parameter table.

Parameters - Power Query | Microsoft Learn

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

Create parameters for paginated reports in Power BI Report Builder - Power BI | Microsoft Learn

Parameters - Power Query | Microsoft Learn

Hope above details helps.