Power BI Parameters Via Query Editor

by | Power BI

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.

Power BI Parameters

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.

Power BI Parameters

Next is to create a new parameter by clicking the New option.

Power BI Parameters

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.

Power BI Parameters

We can also create filters or parameters under the Type dropdown. In this example, we’ll use the Text type.

Power BI Parameters

For the Suggested Values, it could be any value, list of values, or query. We’ll select the List of values

Power BI Parameters

Then, we can type the different currencies that we have like AUD, USD, EUR, GBP, CHF, CAD, and JYP

Power BI Parameters

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.

Power BI Parameters

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.

Power BI Parameters

We’ll move that parameter inside the Parameter Query group.

Power BI Parameters

We’ll also see this drop down that shows all the different currencies that we have. 

Power BI Parameters

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.

Power BI Parameters

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. 

Power BI Parameters

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. 

Power BI Parameters

Now, we need to check our Advanced Editor

Power BI Parameters

Then, we’ll see that the last transformation that we did was about “Filtered Rows”. 

Power BI Parameters

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.

Power BI Parameters

Then, we’ll see the changes on this table where the EUR was changed to USD.

Power BI Parameters

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. 

Power BI Parameters

Go to the Advanced Editor, change the GBP into Currency, then click the Done button. 

Power BI Parameters

Again, we’ll do the same thing for the Sales table. For the Sales table, we’ll filter the Currency Code column.

Power BI Parameters

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. 

C:\Users\Gel\AppData\Local\Microsoft\Windows\INetCache\Content.Word\22.jpg

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

Related Posts