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).
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)
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.
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.
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.
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
we have successfully applied the dynamic filtering using parameter in power Bi report .
This works great, thank you so much for the great explanation. I ahd one question and that is if it would be possible to apply this as a filter, just like a regular filter in the visual, and besides that if it also would be possible to select all countries.