Graphed LogoGraphed
Login

How to Pass Parameter in Power BI Report

Cody Schneider•

Creating static reports that only show one view of your data is a thing of the past. To get real value, you need interactive reports that stakeholders can use to explore data on their own. This is where parameters in Power BI come into play, turning your rigid dashboards into flexible, dynamic tools for analysis. In this tutorial, we'll walk through exactly how to create and use parameters to filter data, enable "what-if" scenarios, and even pre-filter reports using a simple URL.

What Exactly Are Parameters in Power BI?

Think of a parameter as a reusable variable that can hold a value, which you can then use to modify your report's behavior. Instead of hard-coding a value - like filtering a sales report to only show data for "United States" - you can use a parameter. This allows you, or anyone viewing your report, to easily change that value from a dropdown, slicer, or even a link, without having to edit the report itself.

Why is this useful? Parameters unlock several powerful capabilities:

  • Dynamic Filtering: Let users filter the entire report for their specific region, product category, or time period. This turns one report into a customized experience for dozens of different stakeholders.

  • "What-If" Analysis: Create interactive sliders that let you forecast outcomes based on different assumptions. For example, you can model how revenue changes if you apply a 5%, 10%, or 15% discount.

  • Simplified Maintenance: If you need to change a server name or a file path that your report connects to, you can use a parameter. This way, you only have to update it in one place instead of digging through multiple queries.

Essentially, parameters give you and your end-users more control, making your reports more interactive and powerful.

Creating Your First Parameter in Four Simple Steps

The most common place to start with parameters is in the Power Query Editor, which is Power BI's data transformation engine. Let's create a basic parameter to filter a sales dataset by country. This allows a user to select one country and have all the data in the report update accordingly.

Step 1: Open the Power Query Editor

First, you need to get into Power Query. From the main Power BI Desktop window, go to the Home tab and click on Transform data. This will open up the Power Query Editor, where you wrangle all your raw data.

Step 2: Create a New Parameter

In the Power Query Editor, look at the Ribbon at the top. On the Home tab, click the Manage Parameters button, then select New Parameter from the dropdown. This opens the parameter configuration window.

Step 3: Configure Your Parameter's Settings

Now you'll see a dialog box with several options to fill out. Here's what they mean:

  • Name: This is the name you'll use to reference the parameter. Make it something clear and descriptive, without spaces. For our example, let's call it SelectedCountry.

  • Description: This is an optional but highly recommended field. Describe what the parameter does. A good description might be, "Used to filter the main Sales table to a single country."

  • Required: Leave this checked. It ensures the parameter must have a value.

  • Type: This defines the kind of data the parameter will hold, like Text, Decimal Number, Date, etc. Since we're filtering by a country name, select Text.

  • Suggested Values: This is where you control what values the user can choose from. You have three main options:

    • Any value: The user can type in any text freely. This is flexible but can lead to typos.

    • List of values: This lets you pre-define a static list of options. It's great for things that don't change often. Let's use this one. Enter a few country names from your data, like "USA", "Canada", and "UK".

    • Query: This lets you populate the choices dynamically from a data column in another query. This is the most robust option for long or changing lists, like product names or customer cities.

  • Default Value: The value used if none is selected. Let's enter "USA".

  • Current Value: The value the parameter is currently set to. Let's also set this to "USA".

Once you're done, click OK. You'll now see your new SelectedCountry parameter listed in the Queries pane on the left side of the Power Query Editor.

Step 4: Connect the Parameter to Your Data

Creating the parameter is only half the job, now you need to make it do something. With your sales data query selected, find the column that contains the country information (let's say it's called "Country").

  1. Click the filter dropdown arrow on the "Country" column header.

  2. Hover over Text Filters and click on Equals.

  3. In the "Filter Rows" window, you'll see a dropdown that says "Enter or select a value." Instead of choosing text, change this dropdown to Parameter.

  4. Your SelectedCountry parameter should now be available to select. Choose it and click OK.

You’ll notice that your data preview immediately updates to show only rows where the "Country" is "USA," because that's our parameter's current value. If you change the Current Value of the parameter back in the Queries pane, the data preview will update again. When you're finished, click Close & Apply on the Home tab to get back to the main report view.

Creating Dynamic "What-If" Scenarios

"What-if" parameters are a fantastic feature for performing sensitivity analysis directly within your finished report. Instead of filtering data sources, these parameters live on the report canvas itself and let users interact with a slicer (like a slider) to see how changing a variable impacts their KPIs.

Let's build a simple what-if scenario to forecast revenue with a variable discount rate.

Step 1: Use the "New Parameter" Button

In the main Power BI Desktop view (not the Power Query Editor), go to the Modeling tab in the top Ribbon and click New parameter > Numeric range.

Step 2: Configure the Numeric Range Parameter

A configuration window will pop up:

  • Name: Let's call it Discount %.

  • Data type: Choose Decimal number. This allows for percentages like 0.05.

  • Minimum: Set this to 0 (for a 0% discount).

  • Maximum: Set this to 0.5 (for a 50% discount).

  • Increment: This is how much the slider moves with each step. Let's set it to 0.05 (for 5% jumps).

  • Default: Set this to 0.1 (a 10% default discount).

Make sure the "Add slicer to this page" box is checked. When you click Create, Power BI does two things for you automatically:

  1. It creates a new calculated table behind the scenes using a DAX formula called GENERATESERIES() to hold all possible values from 0 to 0.5.

  2. It creates a new measure called Discount % Value that simply grabs the currently selected value from the slicer.

Step 3: Create a DAX Measure

First, review the auto-generated DAX function: Discount % = GENERATESERIES(0, 0.5, 0.05). This function creates a series of values with which the slicer interacts. While Power BI sets up this table relationship invisibly and efficiently, you may want to simplify the DAX measure produced.

Here's a streamlined version:

Discount % Value = SELECTEDVALUE('Discount %'[Discount %], 0.1)

This formula selects the user-chosen discount value, with a default of 0.1 in case no single value is selected, ensuring reliability.

Finally, employ this measure in a new calculation to assess net revenue:

Net Revenue= SUM(Sales[OrderCost]) - (SUM(Sales[OrderCost]) * 'Discount %'[Discount % Value])

This approach lets you and your team explore various pricing strategies and forecast revenue outcomes under different conditions.

Passing Parameters Using a URL Filter

Here's a clever way to "pass" a filter to other users: embed it directly in the report's URL. This is perfect for when you want to send someone a link to a report that's pre-filtered for their specific team, region, or any other data point.

You don't need to create a parameter in Power Query for this to work. It works by telling Power BI which table and column to filter, and what value to use, all within the URL.

The basic syntax looks like this:

https://app.powerbi.com/report-url?filter=TableName/FieldName eq 'Value'

  • TableName: The exact name of the table containing the column you want to filter.

  • FieldName: The exact name of the column you're filtering.

  • eq: Stands for "equals."

  • 'Value': The actual value you want to filter by. Note that text must be enclosed in single quotes.

For example, to filter our sales report for "Canada," we would first get the regular sharing link from the Power BI Service. Then, we'd append the filter to it:

https://app.powerbi.com/groups/me/reports/your-report-id/ReportSection?filter=Sales/Country eq 'Canada'

Important considerations:

  • Tables and columns should not contain spaces. Instead, use underscores or camelCase.

  • The field you are filtering does not need to be displayed in any visuals for the report to apply the filter.

Final Thoughts

Mastering parameters transforms your Power BI reports from static documents to genuinely interactive analytics tools for use by your data and business leaders. We explored the dynamic capabilities of Power BI, from query parameters to modeling and using URL filters. These features make your reports more engaging and informative.

While powerful business intelligence tools like Power BI offer features tailored for analytical insights, they can remain underutilized if not effectively implemented. Our team at Graphed aims to simplify this process by enhancing communication and understanding across your organization. Through intuitive design and clear language, we bridge gaps between technical data analytics and strategic business needs, bringing these tools into greater use for the whole team.