An Introduction to Parameters in Power BI – Part 2 Query Parameters
Nick Hilton | 15 September 22 | 6 min read
Welcome to the second of a two-part post on Parameters in Power BI. In part 1 which you can find here we had a high level look at and introduction to ‘What-if’ Parameters which can help you to forecast and analyse potential future metrics. This has been a learning experience for me and the What-if forecasting had me thinking of all sorts of ideas and writing this post has been just as insightful and hopefully it will be for you too. Here we’re going to continue with the basic, high level introduction but instead focus on the other half of Power BI’s parameters which exist in Power Query. Before you think I’ve forgotten all about those wonderful Field Parameters which have just been added as a great new feature then fear not, I’ve already got a post on that over here!
Power Query Parameters?
Whereas what-if parameters are modifying the results of the data shown in the reporting layer, Power Query parameters happen in the data preparation layer (Power Query). Therefore, they’re adjustments to the datasets you’re bringing into your model. This opens up some powerful functionality and opportunities. From what I can see the two biggest use cases are security and governance via limiting queries with dynamic filters and creating development pipelines through the ability to swap strings in the connection details of your data sources e.g., dev -> test -> prod variations.
Use Case 1: Using Query Parameters in Filter Rows
In certain situations, there are going to be benefits for only allowing your report designers to be able to access one subset of data at a time, for example your dataset has sales and revenue for every country your organisation operates in but your report designers in each country only need to access one country, their own. By applying query parameters you can create a popup box to open when the pbix is opened in Power BI desktop where users can input or select a value. Once the value is entered the full dataset is refreshed. Limiting down the dataset likes this theoretically will help improve performance and file sizes by reducing the data to a fraction of its entirety, but more so the value here I think is that ability to limit access to data outside of a certain environment.
How to use:
1. To start you’ll need any pbix with a data model you have access to and can refresh
2. Find a table you’d like to filter and a field you’d like to filter on. In my case I’m going to use my logistics demo to pretend I’m a IT manager at head office sending out a report to different locations and use the ‘depot’ field.
3. Duplicate that table, remove all the other columns, remove duplicates and then using the button in the transform tab click ‘convert to list’. A list is one type of input a parameter can base it’s values off.
4. On the home tab on the ribbon select manage parameters then new parameter.
5. Change your suggested values to be ‘Query’ and Select the list you just created
6. Add a Current/Default value and click ok
7. You’ve just created a parameter! Now we need to filter a row with it
8. Find the field in your table that you want to filter.
9. Navigate to text filters and then the operational logic you want to apply (equals, does not contain, etc). In this case we want Equals.
10. Click the abc drop down and change the selection to ‘Parameter’
11. Select your parameter and press ok
12. Apply the query changes!
13. To test your newly configured parameters select transform data then edit parameters
14. If everything has gone to plan then you’ll see a dropdown list with your values!
Your query parameter is now complete and your file can be distributed. At this point you may want to consider exporting this file as a template (.pbit) file which is your file without any data any it. When a user opens that template they will be prompted to enter any parameters that are set up and only after that will the file populate with data. This could be the difference between distributing a 10kb file and one stretching into the gigabytes depending on your data sizes!
Use Case 2: Parameterising a data source
Okay so I’ve saved the best till last here because I really think this is the number one use for query parameters. With the click of a button you can swap the server and database connections strings around for whole datasets. We’ll stick with swapping the database field in this explanation but parameterising the server is exactly the same process.
Imagine you have 3 databases, Development, test, and production. As you develop your report you may want to be using development data, after that it may progress through the pipeline from dev to test, then to production. I’ve seen all sorts of examples for why people may want to swap between databases, things like having a matching but separate database for each customer, or testing data in an old system against a new one. The essential part is that each table needs to have an equivalent table in each database with the same name, e.g. dbo.sales needs to exist in dev, test, and prod.
How to use:
1. Get a pbix and add a few data sources from a SQL database. You don’t need the databases to test adding a parameter, it’s just that when you come to apply it it won’t work, but hey gotta learn somehow!
2. On the home tab on the ribbon select manage parameters then new parameter.
3. Fill in the details using ‘list of values’ as the suggested value and entering text into the table in the window. Then select your default and current values. Before clicking ok
4. If you were parameterising the server as well then at this point you’d add a new parameter and add those details in
5. Now go through each of your queries and using cog on the source row in the query steps adjust the source of each query to be using your parameter by clicking on the little box to the edge of the textbox, selecting parameter and making sure it’s the one you’ve just made that’s displaying. For me that’s the imaginatively named: ‘DatabaseParameter’
6. That’s it! All you need to do when you want to swap between them is to click your parameter in the list of queries and choose the one you want.
I’m always surprised by how straightforward some of these more advanced Power BI capabilities are!
The final part to this is that when it’s published to the service the option to adjust the parameter now sits in the dataset settings where the parameter option will no longer be greyed out.
And so we come to the end of this two-parter on query parameters. This has been a bit of a learning experience for me too and I’ve tried to fill a bit of a niche in providing an accessible higher-level introduction to these topics. Personally, I find a lot of explanations can be too quick to jump into the deeper detail leaving me saying, once again, ‘but I don’t get it!’ but if after reading this you feel like you understand what’s going on and are ready to expand on that understanding with more advanced tutorials then I’ll be happy I’ve achieved what I set out to do!
Hmm, what to learn next….