In this tutorial, we’ll discuss how we can create and use Power BI Parameters which is a powerful feature of the Query Editor.
There are instances when we want to put additional filters on our data within the query editor. We can do that by utilizing the Manage Parameters option within the Home ribbon.
For this tutorial, we’ll have an example of this feature by utilizing exchange rates. With this example, you’d see why you should use this and the reason why we should put filters on the Query Editor instead of putting them first inside the Power BI. There are also other occurrences where this will be very valuable, like when creating a report for many people.
What we’ll do here is to create only one report, and change the parameter. This parameter will then allow us to put filters across our entire report for an isolated customer, isolated sales region or store, or in this case, an isolated currency. I’ve already joined up a model in the background just for demonstration purposes.
Creating Power BI Parameters Via Query Editor
First, let’s click the Manage Parameters option within the Home ribbon.
Next is to create a new parameter by clicking the New option.
We’ll name this parameter as Currency. We’re going to place a filter here that filters the entire report for the currency that we have selected. We can also write a description here to make sure that we keep things as descriptive as possible for the continuity of our model. For this example, we’ll leave this blank.
We can also create filters or parameters under the Type dropdown. In this example, we’ll use the Text type.
For the Suggested Values, it could be any value, list of values, or query. We’ll select the List of values.
Then, we can type the different currencies that we have like AUD, USD, EUR, GBP, CHF, CAD, and JYP.
We can also implement a Default Value and Current Value. For this example, we’ve used USD as the value, then click the OK button.
If we look at the Queries pane, we’ll see the parameter that we have created (Currency), and that it has a different icon because it’s a parameter.
We’ll move that parameter inside the Parameter Query group.
We’ll also see this drop down that shows all the different currencies that we have.
We have this Manage Parameter button as well. By clicking this button, it’ll take us back to the popup box where we can make adjustments for our parameter.
Implementing Power BI Parameters In The Data Model
Now, we want to be able to implement our Currency parameter filter across our model. We need to think of all the different tables that the currency is referenced. In this case, it’s currently referenced into three tables, which are the Currencies, Exchange Rates, and Sales table.
Again, we need to implement or plug this parameter into these tables. We can do that by coming to a certain table and creating a filter.
For this example, let’s create a filter inside the Currencies table. So, we’ll click the Currency Types column dropdown, untick the Select All filter, select the EUR value, then click the OK button.
Now, we need to check our Advanced Editor.
Then, we’ll see that the last transformation that we did was about “Filtered Rows”.
We want to list out the parameter that we created, which we named as Currency. So, let’s change the “EUR” to Currency. This is because we want to dynamically filter by the parameter and not by Euro. After that, let’s click the Done button.
Then, we’ll see the changes on this table where the EUR was changed to USD.
Next is to go to the Exchange Rates table and do the same thing. We’ll filter the Foreign Ccy column by unticking the Select All filter, select the GBP value, then click the OK button.
Go to the Advanced Editor, change the GBP into Currency, then click the Done button.
Again, we’ll do the same thing for the Sales table. For the Sales table, we’ll filter the Currency Code column.
Everything inside the Advanced Editor is easy to understand if we have an understanding of the M code.
Understanding the M code is relevant when we’re trying to implement these kinds of transformations.
Those changes will make the default value of the columns that we filtered to USD. That’s because in the Currency parameter query, the value that we set for the Current Value is USD.
Let’s change this for example into CAD and see what will happen.
If we go and check the Currencies, Exchange Rates, and Sales table, we’ll see that they are automatically filtered into CAD.
That’s one of the ways to implement this kind of filter. Since these tables are now filtered, we can now click the Close & Apply to apply it in our model.
Now, our tables on this model are filtered based on the last filter that we did on our query editor. This means that our tables here are now filtered by CAD currency.
Changing Filters
To change the current filter that we’re using, go to the Home ribbon, click the dropdown of the Edit Queries, and select the Edit Parameters option.
Then, it’ll allow us to have a different option or change the current filter. For example, we can now change our filter from CAD to USD currency and apply it by clicking the OK button.
Lastly, click the Apply changes button.
After that, everything in the background is going to adjust by the filters that we’ve made. That’s another way to easily place a filter over our entire model.
***** Related Links *****
Dynamic Segmentation With Dynamic Parameters
Multiple What If Parameters In Power BI
Scenario Analysis Techniques Using Multiple ‘What If’ Parameters
Conclusion
Let’s now round things off with this powerful feature. You might think that we can filter in various ways. However, the big difference here is that this filter happens at the query level (Query Editor) instead of the report level (Power BI data model).
If we do it at the query level, it’ll bring in fewer data into our model compared to doing it at the report level. This is because the report level is going to overlay all of the data that we have. On the other hand, doing it on the query level is like redefining the tables behind our model via the query editor.
With that being said, being able to refine our data by implementing a parameter is probably much better instead of directly bringing huge volumes of data on our model. I hope this tutorial gave you an idea on how to use Power BI parameters in your own models.
All the best,
Sam
