Power BI Query Parameters: Optimizing Tables

No comments

Query parameters are a really strong feature in Power BI.

There are many places that you can implement filters in Power BI, but on some occasions, it’s better to do this at the query layer versus the report level.

You might want to create filters at the query level if you have massive tables from a database that your organization has implemented. You don’t want to bring in the entire underlying table every single time into your Power BI model.

A good example of this is tables that are just millions of rows long, perhaps 10 or 20 million rows long. Most tables and databases are created for capturing everything, so you’re going to have data across a significant time span.

By using query parameters, you can cut down the time span at the query layer and not bring all of that data into your model.

This is going to significantly reduce the size of your model and also ultimately the speed. The greater the size of your model, the longer it’s going to take to evaluate calculations over those data sets and data tables.

If you can cut down the size of your tables and optimize your models by utilizing these query parameters, then that’s a solid technique to implement for your development.

In this blog I run through how you can actually implement this in a relatively simple way.

However, there are so many ways that you could actually use this, so expand your mind around the possibilities.

How To Utilise Query Parameters

Query parameters allow you to filter your data or arrange your data based on parameters or entries you can or someone else makes in your models.

This example is a simple report where we have some locations, sales by city within that location, and by customers.

power bi query parameters

With query parameters, you don’t have to re-build a new report to generate certain data where you need to be more specific.

For instance, you want to look at a particular region and not have to look at the entire data set, you can put a parameter to filter this report. And then, it automatically adjusts the data report.

So to do this, we go into the query editor.

power bi query parameters

Inside the query editor, we will use this awesome feature, Manage Parameters, to input parameters.

power bi query parameters

We will now create a simple parameter and hopefully you can recognize how you could utilize this in a number of different ways.

Creating A Parameter

In this example, we are going to create a query parameter for the location. We will filter the STATE CODE here, so we can then drill into the very specific region or specific state.

So, we go to Manage Parameters and click on NEW to create a new parameter. We will call it LocationFilter, and then write the description.

There’s a bunch of options for Type and Suggested Values, which allows you to get advanced in your parameter.

Choose TEXT for the type, and LIST OF VALUES for the suggested values.

power bi query parameters

Then, we list down few of the state codes under List of Values.

We can actually default it to something, and so in this example, we will default it to Florida. Once everything is filled out appropriately, click OK.

And now we have this LocationFilter, we will put it up into our parameters group. Putting things into groups like this is another good technique so they are a bit organised in here.

Within this filter, we can actually select all the different regions we put in here. This example is just a small list, but you can definitely do your own more substantially.

We could choose Florida here or Georgia, and so on.

So now, we can integrate this to our query, which in this example we use Location.

Integrating Parameter Into A Query

In the Location query, we will dynamically filter the State Codes with the parameter we just made.

We can do this manually by clicking on the State Codes column and choose your desired state.

To incorporate the LocationFilter parameter, we go to Advanced Editor.

We look at the last row here, which says Filtered Rows. It is filtered by “Florida” (FL) because we manually filtered the State Codes by FL.

So instead of Florida, we type in LocationFilter here as our parameter. Then, click Done.

You can go back to the parameter and change the current value to GA for Georgia, or NY, etc., and it will automatically filter those tables.

power bi query parameters

And, if we look here, the entire table is going to be filtered for that particular parameter. Here, we filter it by Florida.

Conclusion

In this blog, we discussed how to create and utilise query parameters. We used LocationFilter as our example parameter to filter the State Codes for our Location query.

I hope this sparks some ideas about how you can replicate it in your own models.

If you are diving more into the query editor, and if you would like to know how to develop great Power BI models, then check out my advanced course at Enterprise DNA Online.

This course, Advanced Data Transformations & Modeling, will take your skills to the next level.

Cheers,

Sam

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

membership banner 3

***** Related Links *****
Practice Tips When Using The Advanced Editor in Power BI
Build A Comprehensive Date Table in Power BI Really Fast
Unpivot and Pivot Basics in Power BI – Query Editor Review

***** Related Support Forum Posts *****
Query Parameters
Creating & Using Parameters
Parameterized Query
For more query parameter support queries to review see here….

Enterprise DNA Events

Leave a Reply

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