Data & Intelligence

Dynamic Filtering Using Parameters in Power BI

Zoom

Data is the lifeblood of modern businesses, and we’re generating more of it than ever. For making well-informed business decisions, it is crucial to fetch relevant information from the data and present it in a lucid manner. Microsoft’s Power BI suite is designed to quickly turn your data into useful information. Microsoft Power BI is data and analytics reporting tool that helps organizations bring together disparate data sets into reporting dashboards.

Let’s Dive

Open the desired power bi report you want to apply dynamic filtering (here we are using report for COVID data).Bolg3 1

When I open this report, it automatically loads all the data available in the dataset including all the countries in dataset, however I want to configure the report in a way that when I run the report, it should pop up with the list of countries to choose from. Hence the data should load only for that selected country. lets say one end user only want to see the data for India while another end user want to see only for Germany.

Let’s see how we can apply dynamic filtering using parameter to get desired result.

Step 1: open the report, open query editor window (Transform Tab)

Blog3 Pic 2

Data Intelligence - The Future of Big Data
The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

Step 2: Create the unique list of countries that will appear as an option to choose from ,when the report runs. To achieve this right click on country column(in data view tab) and select add as a new query. this would create the separate list of countries in it.

Bolg3 Pic3

Step3: Make the country list unique. (Right click on column – remove duplicate). now we have unique list of countries, lets store this list in form of parameter. navigate to home menu and select manage parameter. select new parameter. give appropriate Name, enter description, keep required checkbox checked as we don’t want the users to bypass the countries . choose query from suggested value. choose the newly created query in query tab. Select current value as any country. click on ok.

Blog3 Pic4

Blog3 Pic5

Step 4: Apply Parameterized filter to Table 1 table, so data load only for selected country. To achieve this click on dropdown icon on country column in Table 1, Go to Text filter – Equals – change filter type from Text to parameter. select the parameter we just created – click OK and then close and apply and let the model get updated.

Blog 3 Pic6

Step5: Last thing we need to do is to make the filter selection pop out when user run the report, in order to achieve this, save the file as a power bi template file (Pbit format).Now open the report and you will get the pop up to select the country once you select the country, data load only for selected country and you will see visual related to selected country

Picture7

 

Picture8

we have successfully  applied the dynamic filtering using parameter in power Bi report .

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Vaibhav Punwatkar

Vaibhav Punwatkar is a Senior Technical Consultant at Perficient. He is a Power BI developer and Power BI service Consultant. Vaibhav is excited to share his knowledge about Power BI and is open to suggestions from readers.

More from this Author

Subscribe to the Weekly Blog Digest:

Sign Up
Follow Us
TwitterLinkedinFacebookYoutubeInstagram