How to refresh Power BI report based on parameters in visualization?

Question

As in the title. I want to refresh Power BI Desktop report based on parameters e.g. date parameters. I can create date parameters (and other parameters) in Query Editor so it's not a case. The case is how to refresh Power BI report (visualization) based on those parameters created in Query Editor. Is it possible?

Here is an example:

These are filters in my Power BI visualization. Based on year/month (user can choose month,year on a filter) report should be automatically refreshed - year and month (or date) must be passed as SQL parameter.

Are you talking about slicer? In power BI, if you change value in slicer, all related visuals updates instantly. — mkRabbani
No, not about slicer. I want to pass parameter from PBI Desktop (Raport View - visualization) to SQL through Query Editor. I created date parameters in Query Editor and want user to choose dates as to update data in report. So user chooses dates (from-to) in visualization (might be slicer, whatever) and those dates are passed to SQL as parameters. — Muska

Muska Muska · Accepted Answer · 2020-08-21T08:33:03

I found a solution for it. The key is to use NativeQuery here. The steps are as following:

Create stored procedure with parameters (e.g. DateFrom and DateTo) and import data to Power BI
In PowerQuery Editor create 2 parameters. Let's say startDate and endDate. Set some default values:

Go to 'Advance Editor' to change M code. The proper one should be as following:

let
    Source = Sql.Database("ServerName\12", "Base2"),
    Query = Value.NativeQuery(
            Source, 
            "EXEC [dbo].[storedProcedure_XYZ] @DateFrom = '" & Date.ToText(startDate) & "', @DateTo = '" & Date.ToText(endDate) & "'")
    
in
    
Query

@DateFrom and @DateTo are SQL parameters, PQ parameters (startDate and endDate) must be assigned to them in a way I showed above.

Important thing is that you can't put EXEC.... statement in 'SQL statement field' for importing data (of course you need to fill out server name etc.). I mean you can do it first to import raw data (see 1st point) but after that this field should remain empty. The whole job is now done by your M code using Native Query.

After clicking 'Ok' in your 'Advanced Editor' (see 3rd point) your SQL query will be executed along with parameters. Now you can see data refreshed in Power BI.
Go to visualization layer in your Power BI. Click on this little arrow down for 'Transform Data' and choose 'Edit Parameters':

Now enter values for your parameters.

Click OK and that's it! Your SQL query is being refreshed now based on parameters you passed from PBI Visualization layer and all refreshed data will be loaded into your data model in PBI - that means your visualizations will be updated automatically with new data. It's not statis any longer - it's dynamic now:)

Another good thing about it is that your base table which you imported to PQ from SQL won't change to function (as it usually is when you parametrize queries) - it still remain table even though you use parameters on your base query. It makes less problems with loading data into PBI data model, all columns remain on their place and none of them are removed. It works perfectly for me.

I also turned off Native Query Approval (go to Options>Security) as to PBI doesn't ask you for permission to execute your query. That's good thing to do when you use Native Query (bear in mind security issues, it's important here).

How to refresh Power BI report based on parameters in visualization?

2 Answers