Query parameters are a really strong feature in Power BI. You may watch the full video of this tutorial at the bottom of this blog.
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.
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.
Inside the query editor, we will use this awesome feature, Manage Parameters, to input 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 a specific state.
So, we go to Manage Parameters and click on NEW to create a new parameter. We will call it Location Filter, 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.
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.
And, if we look here, the entire table is going to be filtered for that particular parameter. Here, we filter it by Florida.
***** 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
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
[youtube https://www.youtube.com/watch?v=XcUsniWenuM?rel=0&w=784&h=441]
