How to use Power BI Parameters in DAX and Power Query

Power Query Parameters are a great way to make your Power BI models easily maintainable. They act as global variables that can be used anywhere within Power Query.

In this article, we’ll take you through the highest activity use case of using parameters for business rules. Lastly, we’ll show you a technique that allows you to use parameters in DAX statements as well. Finally, using parameters ensures business rule continuity throughout your model.

Three Reasons to Use Parameters

Storing data connection information

Storing your data connection information in a parameter makes it easy to create reusable Power BI templates. Imagine creating a report model that can be used over and over within your organization without needing to do a code change. Simply enter the information once and watch the magic occur from there.

In order to use parameters in this fashion, you’ll need to enable the option to show it in Data source and Transformation dialogs.

To enable, in Power BI Desktop, go to

• On the Home tab in the ribbon, select File
• Select Options and Settings
• Select Options
• Select Power Query Editor on the left navigation of the pop-up dialog
• Under Parameters, ensure that Always allow parameterization in data source and transformation dialogs is checked

Once Power BI is restarted, you’ll see a dropdown menu appear in front of the entry fields in your data source dialogs. This dropdown will enable you to select a parameter.

Storing commonly used images

Images such as KPI indicators are generally used a great deal in our reporting. Since Power BI can’t access authenticated storage to retrieve images, the most efficient way to add these images to the model is to convert them to Base64 text and store them in a parameter. You can learn the basics here: Chris Webb’s BI Blog: Storing Large Images In Power BI Datasets Chris Webb’s BI Blog (crossjoin.co.uk) Note, in our use, we use this site Base64 Image Encoder (base64-image.de) to convert the image to a Base64 string, add the prefix as Chris describes, and store this full result in a parameter.

Business rule conditions

Business is in a state of constant evolution so it makes sense to ensure your BI content can easily evolve with the business. We’ve found that many business rules around transaction limits, number of days, and other comparators will change over time. Storing them as parameters makes it extremely easy to update this information and execute a business rule change without making a code change.

In the video below, we’ll go deeper into this very common scenario. You’ll see how to set up your parameters for this use in both Power Query and DAX, as well as how to edit the parameters in Power BI Desktop and PowerBI.com.

---

---

How To Video