The word parameters is a little ambiguous when it comes to Power BI. I have written a number of articles that reference parameters, but never took the time to clarify their use cases. I wanted to dive in to the different types of parameters and understand the best way to use them. Paired with some tips and tricks, you will have a better idea of how to use all kinds of parameters in your reports.
Parameters in Power Query
In my opinion, parameters in Power Query are the most in your face. When you launch the Power Query editor, there is a button on the home ribbon that helps you manage them. But have you ever used them?
Parameters in Power Query allow you to add reusable values in your queries. The premise is simple – if you keep using the same value over and over again, why not make it easy on yourself?
How to Leverage It
To get started, open the Manage Parameters window in Power Query. From there, create a new parameter and give it a meaningful name. From there, you just need to specify the type of value, add your value, and click OK. Since I am creating a parameter for a reusable API key, I am just using text and adding in my key.
Now that I have the parameter in place, I can use it in an existing query. Since I was looking to easily reuse an API key, I can go and update my queries.
As an added bonus, I can share my full query without having to remember to hide my API key!
Common Use Cases
While the API key use case above is one of my favorite stories to tell, there are a few other key use cases.
One of the most famous use cases is when you need to create parameters for Incremental Refresh or Hybrid Tables. You need to create a RangeStart and RangeEnd parameters to setup the filtering on the report.
Another great use case is when you need to quickly change values to filter a report. In a past life, I worked for a company that had APIs for external reporting. Since customers always wanted similar reports, I created them with parameters so I could quickly update the API key and customer id. When a new customer wanted a custom report, I just updated the parameters to get their data in, made some adjustments, and sent the report out.
Tips and Tricks
Tip #1 – If you save your Power BI Desktop file (PBIX) as a template (PBIT), you will be prompted to enter the parameters when you use it. This helps save you a few clicks.
Tip #2 – All of your parameters can easily be updated in the Power BI Service without having to open the desktop file. Go to your data source settings and you will find the parameters section. This allows you to update your parameters like API keys with minimal effort.
Tip #3 – If you are using a deployment pipeline, you can easily swap between your Dev-Test-Prod databases. Just set parameters for your server and database fields in the advanced editor window and update them in the Power BI Service.
Function Parameters
Not as common of a use case, but functional parameters allows you to reuse a query quickly. I go into great detail on why to use functions with my article on how to use REST APIs in Power BI, but in short, they allow us to scale a base query rapidly.
How to Leverage It
One of my favorite endpoints pulls stock information by ticker symbol. I could build multiple queries and append them, but a function makes it easier. I just need to add a little code at the top to convert my base query to accept a parameter.
I specified the parameter that I wanted to gather and then apply it in the query. When I go to use the function, I will be prompted to specify the parameter so the query works properly.
Common Use Cases
I frequently use function parameters with APIs. Because some API endpoints require dynamic values, you will need to iterate with a function like I did with the stock symbols.
Another common use case for me is building common elements I might use in a report. If I am running into queries that are using paging , I can grab a function and invoke it to build out my base. Another common use case is to build out a date table when one is not available in my data source. Of course these are things we want to do as far upstream as possible, but if you are not in control of your database, you might need to create your own.
Tips and Tricks
Tip #1 – Invoking a function as a column is an easy way to specify your parameters with existing column values. The function will run on each row of your query using the values in the columns identified. It is a quick and easy way to apply a function!
Tip #2 – You can edit a query and add a function by hand. Just make sure you specify your parameter in the function or it will not work. I use this all the time for token based authentication with APIs.
Tip #3 – If you are using a few different API endpoints, you can create a base function to handle the majority of your query. Just add a parameter to the function to specify the endpoint. From there, you can add your endpoint or table name in the parameter field and invoke the function which speeds up your connection time.
What If Parameters
What if parameters are different than the other two parameters we discussed. Power Query and function parameters really help us with querying data efficiently. What if parameters are all about what you could do with your data.
How to Leverage It
We often find what if parameters when you want to run some simulated scenarios. I recently did a deep dive on what if parameters, but at a high level, we have two components.
The first component is the creating the parameter itself. We will go to the modeling ribbon and select new parameter. A new window will open and I can specify key components of my parameter which include the name, data type, minimum, maximum, increment value, and default. Once I click OK, a new slicer will appear.
But that was the easy part. We now have a new measure that is <Parameter Name> Value. This brings back the value you selected in your parameter. We now can use this measure in a measure to get our value. You will need to use a little DAX, but it is worth the extra effort.
Common Use Cases
The most common use case is to simulate revenue increases. I add the parameter to a formula that takes their current revenue and shows the change. Everyone is trying to make more money, so it makes sense.
One of my favorite use cases is to help filter data. I built a report that compared property assessments in my town. I used a what if parameter to help filter 3,500 properties to only a handful. Using key values such as year built, livable square footage, and lot size, the parameters found similar houses to the one I selected. Instead of having to fool around with filters, I could just quickly adjust my report parameters instead.
Tips and Tricks
Tip #1 – Always have the parameter make the slicer when you can. You can hide it in the selection panel if you don’t want to use it, but the slider is so nice. It makes the mobile experience even better!
Tip #2 – If you are using a number of parameters, don’t be afraid to create a page for your parameters. You can just sync the slicers to other report pages. This prevents clutter and speeds up load time for a page sine there are less objects in the view.
Tip #3 – You can create your own what if parameters with a custom table. This is nice when you want to use a non-numeric parameter. This is a common practice for filtering reports on things like top/bottom views.
Parameters on Parameters
You might have gotten to this point and said “Wow – I had no clue that this term is so widely used!”. I think it is important to understand the different kinds of parameters because they really can make an impact on your reports. Some of my reports maybe only use one of these while others use all three. The key is understanding how to properly use them.
Have you used any of these parameters before? Do you find them helpful in your report design and data models? If so, tell me in the comments below!
