How to Use Parameters in Power BI

Creating interactive Power BI reports is a game-changer for sharing data, but static charts can often lead to more questions than answers. To truly empower your team, you need reports they can interact with. This guide will walk you through exactly how to use parameters in Power BI to turn your static dashboards into dynamic, user-friendly tools that answer questions on the fly.

What Are Parameters in Power BI, Anyway?

Think of a parameter as a variable that you can build into your report. It holds a value - like a number, date, or text - that a user can change without ever having to edit the underlying dataset or report structure. When the user changes the parameter's value, the data, calculations, and visuals in your report update automatically to reflect that change.

In simpler terms, parameters let you ask "What if?" directly within your dashboard. Instead of creating a dozen different charts to show different scenarios, you can create one dynamic chart and let the user explore the possibilities themselves.

For example, a marketing manager could use a parameter to:

• Test how a 5%, 10%, or 15% increase in ad spend might affect projected ROI.

• Filter a massive sales report to only show data for a specific region.

• Adjust a sales commission rate to see its impact on the team's total compensation.

Parameters replace the need for hardcoded values and give the report's end-users the power to slice, dice, and analyze data in ways that are meaningful to them.

Two Main Types of Parameters

In Power BI, you'll primarily encounter two types of parameters, each serving a distinct purpose. Understanding the difference is key to knowing which one to use for your specific goal.

1. What-If Parameters

These are the parameters that most users think of when they want to make their reports interactive. "What-If" parameters are designed for end-users to simulate different scenarios directly on the report canvas. When you create one, Power BI automatically generates a slicer that your audience can use to input or select values.

Common Uses:

• Building a simple forecasting model (e.g., "What if our website conversion rate increases by 2%?")

• Modeling financial projections (e.g., "Adjust the assumed inflation rate to see the impact on our budget.")

• Setting dynamic goals or targets (e.g., "Change the quarterly sales target to see which reps are on track.")

These parameters live entirely within the PBIX file and are tied to DAX measures to make your visuals respond to user input.

2. Query Parameters

These are more of a backend tool used within the Power Query Editor. Unlike "What-If" parameters, Query Parameters are not for end-user interaction. Instead, they are used by the report creator to change the data that is being loaded into the data model.

Common Uses:

• Filtering large datasets: If you're connecting to a huge database, you can use a parameter to limit the data you pull in (e.g., only load sales data for the "United States" instead of the entire world). This drastically improves report performance.

• Changing data sources: You can use a parameter to easily switch between different environments, like a staging server and a production server, by simply changing the source URL or server name parameter.

• Adjusting API calls: When pulling data from an API, you can use parameters to modify query values, like a Start Date or an API version number.

Think of Query Parameters as a way to control the source of your data before it even gets into your report, while "What-If" parameters are a way to interact with the data after it has been loaded.

How to Create a 'What-If' Parameter (Step-by-Step)

Let's create a simple "What-If" parameter to model a sales team's commission. We'll build a slicer that a sales manager can use to adjust the commission rate and see the updated potential earnings for their team.

Assume you have a simple table called SalesData with columns for salesperson, total_sales, etc.

Step 1: Create the New Parameter

• On the Power BI Desktop ribbon, go to the Modeling tab.

• Click on New parameter. Since we're working with numbers, select Numeric range.

Step 2: Configure the Parameter Settings

A dialog box will appear. Here’s how to set it up:

• Name: Give your parameter a descriptive name. Let's call it Commission Rate.

• Data type: Since it's a percentage, choose Decimal number.

• Minimum: Set the lowest possible value. Let's use 0.02 (for 2%).

• Maximum: Set the highest value. Let's use 0.10 (for 10%).

• Increment: This is the amount the slicer will move with each step. An increment of 0.005 (.5%) will give us good precision.

• Default: The starting value when the report is first opened. Let's use 0.05 (5%).

• Add slicer to this page: Keep this box checked. Power BI will automatically create a slicer visual for your new parameter on the current report page.

After you click OK, two things happen:

1. A slicer for "Commission Rate" appears on your canvas.

2. In the Fields pane, you'll see a new table called Commission Rate with two generated items: a column also named Commission Rate and a DAX measure called Commission Rate Value.

The measure is simply:

This measure dynamically captures the value currently selected by the user in the slicer.

Step 3: Use the Parameter in a DAX Measure

Now, we need to create a new measure that uses the parameter's value to calculate the commission.

• Right-click on your SalesData table in the Fields pane and select New Measure.

• Write the following DAX formula:

This formula multiplies the total sales by the dynamic value selected in our 'Commission Rate' slicer.

Step 4: Visualize the Result

Finally, add a table or a chart visual to your report. Add the salesperson and the new Total Commission measure. Now, when you drag the "Commission Rate" slicer, you'll see the Total Commission values update in real time for each salesperson. You’ve just made your report interactive!

How to Create a Query Parameter (Step-by-Step)

Now, let's look at the other type. Imagine you're working with a massive dataset of global website traffic, but you only want to analyze data for one specific country at a time to keep your report fast. A Query Parameter is perfect for this.

Step 1: Open the Power Query Editor

• From the Home tab in Power BI Desktop, click on Transform data. This will open the Power Query Editor.

Step 2: Create the New Parameter

• In the Power Query Editor's ribbon, go to the Home tab.

• Click the Manage Parameters dropdown and select New Parameter.

Step 3: Configure the Parameter

A new window will open for you to define the parameter:

• Name: Let's call it TargetCountry.

• Description: (Optional but helpful) "Used to filter traffic data for a specific country."

• Required: Check this option.

• Type: Choose Text since we'll be using country names.

• Suggested Values: You can select List of values to provide a predefined set of options. Let's add "USA", "Canada", and "UK".

• Default Value: What it defaults to if no selection is made. Let's enter "USA".

• Current Value: The value that will be used for the current session. Set it to "USA".

Click OK. You'll now see your parameter listed in the Queries pane on the left.

Step 4: Apply the Parameter to Your Data

• Select your WebTraffic query from the Queries pane.

• Find the column that contains country names (e.g., Country).

• Click the filter dropdown arrow on that column's header.

• Select Text Filters > Equals.

Step 5: Link the Filter to the Parameter

In the "Filter Rows" window, to the left of the text input field, click the dropdown icon that likely says "ABC Text". Select Parameter from the options.

The text box will change to a dropdown listing your available parameters. Select our TargetCountry parameter.

Click OK.

You’ll now see your data preview instantly filter to show only rows where the Country is "USA" (your current value). To test it, you can change the "Current Value" of the TargetCountry parameter to "Canada" in the main parameter window, and you’ll see the data preview update again.

Now, when you click Close & Apply, Power BI will only load data for the country selected by that parameter's current value. If you need to analyze a different country later, you can simply go to Transform data > Edit parameters and change the value, without having to edit the filter steps manually.

Final Thoughts

Learning to use parameters transforms your Power BI reports from static documents into interactive, analytical dashboards. Whether you're adding "What-If" slicers for business modeling or using Query Parameters to manage massive datasets, they provide a level of flexibility and performance that is essential for effective data analysis.

While parameters are powerful, setting them up still involves multiple steps and a decent learning curve within a complex tool like Power BI. At Graphed, we decided to tackle this challenge differently. We automated the entire dashboard creation process so you can get dynamic insights without learning DAX or navigating menus. Instead of building a parameter to ask "What if?", you can just ask your data directly something like, "Show me my sales forecast if revenue increases by 10% next quarter and compare it against last quarter," and get an answer in seconds. It allows anyone on your team to drill down and explore data without needing to be a BI expert.