Saturday, 10 February 2018

How to create and use Dynamic Query Parameters in Power BI Report

Creating and using Dynamic Query Parameters as a Filters in Power BI Report
If you want to allow the end users to Filter and view the Sales in their specific Sales Region data, instead of displaying all regions, we can do it by creating dynamic Parameter in the Report.
We can create a dynamic Query Parameter as follows..
Example :
Suppose we have a Power BI Report , where we have the data of various Sales Regions like North America, Africa, Europe.

 Now we will create a Dynamic Query Parameter based on the Sales_Region of the Query.
1) Go to Edit Queries

2) Create a Query List from Region_Name Column for using in Parameter
From Query Editor > Select column Region_Name from base Table and then R-click and choose the option as Add as new Query

Next Remove the Duplicates from the List, and rename the List

Now a unique List (DistRegion) has been ready to use in Parameter

3) Create a Dynamic Query Parameter using the above List
Go to Home > Manage Parameters > New Parameter

Next create a Parameter and select the suggested values from Query
Now the Parameter appears as follows

4) Apply Filter to the base Table using Parameter
Select the Column Region_Name then got Text Filters > Equals..

Next pass the Parameter as a value

Now the Parameter with the default value "Europe" has been applied to the Report. Please note that this Parameter value will apply dynamically based on user input.

Now Save, Close & Apply the Query Editor Window.

5) Save the Report as Template and Share that Template to the End Users
Once we created and apply the Dynamic Query Parameter, Save as the Report as Power BI Template, which we will share to the end users. This will prompt the users to Select their Sales Region to Load the Report.

Now, when a User opens the Template it will ask to select their Sales Region to View the Report 

Now if we select a Sales Region as Asia, the we will see only the "Asia" Region data as follows..

Important Notes:
Here user can choose and view any Region Data with no Restriction.This will NOT provide a Row Level Security.

If you want to Restrict the users to view only a specific Region data, we should Create and Define the Roles in Power BI Desktop then assign the People to those Roles in Power BI Service.We can also setup the Schedule Refresh in Power BI Service.

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

1 comment:

  1. Hosdy I am so delighted I found your blog, I really
    found you by accident, while I was searching on Aol for something
    else, Anyways I amm here now and wluld just like to say many thanks for a remarkable post and a all round thrilling blog (I also love the theme/design),
    I don’t have time to read through it all att
    the minute but I have book-marked it and also added your RSS feeds, so when I have
    time I will be back to rezd much more, Please do keep upp the excellent work.


Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts