How to Use a Parameter in SQL Query Power BI

Manually tweaking your SQL queries in Power BI every time you need to change a filter is a productivity killer. Whether it's updating a date range, switching a product category, or changing a sales region, constantly modifying the source query is tedious and prone to errors. This article will show you how to use parameters to make your reports dynamic, efficient, and much easier for you and your team to use.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly Is a Parameter in Power BI?

Think of a parameter as a placeholder in your custom SQL query. It’s like a "fill-in-the-blank" field that holds a value - such as a country name, a status, or a user ID. Instead of hard-coding a value directly into your query, you reference the parameter. This lets you or your report viewers change the value on the fly, and Power BI will dynamically rewrite the SQL query before sending it to your database.

Using parameters delivers two major benefits:

• Improved Performance: By filtering data at the source - the database - Power BI only has to import the specific slice of data you need. This is significantly faster and more efficient than importing a massive table and then applying filters within Power BI.
• Enhanced Interactivity: You can link parameters to report elements like slicers and dropdowns, allowing end-users to filter data directly from their database in real time, all without ever having to touch the Power Query Editor.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Getting Started: Connecting to a SQL Database

Before we can create a parameter, we first need to connect to a SQL data source where we can input a custom query. If you've already done this, you can skip to the next section.

If you're starting from scratch, here's how to connect:

1. In Power BI Desktop, navigate to the Home tab and click Get Data.
2. Select SQL Server database from the list.
3. Enter your server and database names. Crucially, expand the Advanced options section.
4. This is where you'll paste your query. For now, let’s use a simple, static query as a starting point. We'll use this example throughout the tutorial, which pulls sales data for just the 'North' region.

SELECT
  OrderID,
  OrderDate,
  SalesRep,
  Region,
  Amount
FROM
  Sales.Orders
WHERE
  Region = 'North'

Click OK and load the data. You now have a table in Power BI, but it’s static, it will always and only show sales for the 'North' region.

Step 1: Create a Parameter in Power Query

Now, let's turn our static 'North' value into a dynamic parameter. All parameter creation happens inside the Power Query Editor.

1. From the Home tab in Power BI, click Transform Data to open the Power Query Editor.
2. In the Power Query Home tab, click the Manage Parameters dropdown button and select New Parameter.
3. This opens the parameter configuration window. Let's walk through each setting.
4. Click OK. You'll now see your new p_Region parameter in the Queries pane on the left side of the editor.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Add the Parameter to Your SQL Query

Now that our parameter exists, we need to instruct our SQL query to actually use it. This involves modifying the source step where you first connected to your database.

1. In the Queries pane, select your SQL query (e.g., 'Sales.Orders').
2. In the Query Settings pane on the right, under Applied Steps, click the small gear icon next to the Source step. This re-opens the SQL Server connection window.
3. It's time to modify the SQL statement in the advanced options. This part is a bit tricky because we need to blend SQL syntax with Power Query's M formula language.

The original WHERE clause in our SQL was static:

WHERE Region = 'North'

We need to replace the hard-coded 'North' with our p_Region parameter. In Power Query's M language, you can combine text and parameters using the ampersand symbol (&). Your new statement will look like this:

SELECT
  OrderID,
  OrderDate,
  SalesRep,
  Region,
  Amount
FROM
  Sales.Orders
WHERE 
  Region = '" & p_Region & "'"

This looks intimidating, so let's break it down:

• '": Starts the string with a single quote for SQL syntax and closes the string.
• & p_Region &: Concatenates the value of the parameter p_Region.
• '": Adds the closing single quote after the parameter value.

Click OK. Power Query may show a warning about running a native database query. This is normal because Power Query can't preview the results until it sends the command to your SQL server. Click Run. The preview data in your table should now be filtered based on the Current Value you set for your parameter!

Now you can easily change the p_Region parameter's "Current Value" in Power Query at any time, and the table's data will refresh accordingly without having to edit the SQL code itself.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Making the Parameter User-Facing

Creating a parameter in Power Query is great for development, but the real power comes from allowing end-users to interact with it in the final report. How you achieve this depends heavily on whether your dataset is in Import or DirectQuery mode.

Parameters in DirectQuery Mode (For Live Interaction)

• DirectQuery is the mode where Power BI sends live queries to your database every time a user interacts with a visual. This is the only way to allow users to change the SQL parameter dynamically from the published report.
• Here's how to link your parameter to a slicer:
• That's it! Now, when a report user selects "East" from the slicer, Power BI automatically updates the p_Region parameter's value to "East" and re-runs the native SQL query against the database, all on the fly. This provides you with powerful and performant dashboards on top of large datasets.

Parameters in Import Mode (For Refresh-Time Filtering)

• In Import Mode, all data is imported and stored in the Power BI model. The downside is users cannot change the SQL parameter dynamically after publishing.
• The parameter's value is "baked" into the report at refresh time.
• This mode is useful when working with large datasets and you want to load only specific subsets, for example, data from a particular year. A report editor can change the parameter value before scheduled refreshes to load different data slices.

Tips and Common Pitfalls

• SQL Injection Warning: Never use the "Any Value" setting for parameters in a published report, as this allows users to type anything, which could inject malicious SQL queries. Always use the "List of values" to control input.
• Data Type Mismatch: Ensuring the data type of your Power Query parameter matches the SQL column is critical, mismatches can cause errors.
• Quotation Handling: The most common challenge with parameters is getting quotations right in SQL. Remember: SQL uses ' around text, and Power Query uses " to enclose strings. Be consistent.

Final Thoughts

Using SQL query parameters is a game-changer for Power BI development. It makes static reports dynamic and interactive, pushing heavy filtering to the database and resulting in faster performance and cleaner reports. At Graphed, we believe that accessing data should be straightforward. Instead of manual SQL and M coding, our platform Graphed allows you to describe what data you need in plain language. We handle the technical complexities so you can focus on insights, not syntax.