Blogs

Parameter Binding with DirectQuery in Power BI

Inkey Solutions, June 27, 202569 Views

The Problem

When working with large volumes of data in Power BI using Import mode, you may encounter long refresh times, frequent timeout errors, and sluggish performance during “Close & Apply” operations in Power Query. These issues become more prominent as the dataset grows, making it difficult to maintain responsiveness and efficiency in the report development process.

To overcome this, many users switch to DirectQuery mode to avoid importing large datasets altogether. However, DirectQuery comes with its own challenge: it still retrieves the entire dataset by default unless filters are explicitly applied — leading to unnecessary load on the data source and slow report performance.

The Solution

This is where Power BI’s M Parameters feature shines. By binding slicers to query parameters, you can dynamically push filter conditions (like top N rows, date ranges, or categories) directly into the SQL query sent by Power BI. This ensures only the required data is retrieved from the source, drastically improving performance, reducing query load, and enhancing user experience.

In this post, we’ll walk through a practical example of how to use a slicer to control the number of rows retrieved from a SQL Server source — all in DirectQuery mode — using Dynamic M Parameters.

Let’s get started with the step-by-step implementation.

Steps

Step 1: Connect to SQL Server in DirectQuery Mode

Start by connecting your report to a SQL Server database, and make sure to select the DirectQuery connection mode.

🔹 This is crucial—Dynamic M Parameters work only in DirectQuery, not in Import mode.

Step 2: Limit Rows Using ‘Keep Top Rows’

In Power Query Editor, apply a Keep Top Rows transformation to limit the table.

• Select Keep Top Rows
• Initially, set a static number like 10

This is just to generate the step we’ll soon replace with a dynamic parameter.

Step 3: Create a New Parameter

Go to:

Home → Manage Parameters → New Parameter

Configure the following:

• Name: TopRanks
• Data Type: Number/ Any (or Text if your use case needs it)
• Suggested Values: Any Value
• Current Value: Set to a default (e.g., 10)

This parameter will replace the static row count in the next step.

Step 4: Replace Static Row Count with Parameter in Advanced Editor

• Open Advanced Editor for the table and locate the line where FirstN or Table.FirstRows is used.
• Replace the static number (e.g., 10) with your new parameter:
  • Table.FirstN(Source, TopRanks)

This change makes the number of rows returned dependent on the parameter value.

Step 5: Validate the Change

• Return to Power Query and change the parameter value manually.
• You should see the number of rows in the table update based on the parameter value.

This confirms the logic is working as expected.

Step 6: Load Data and Set Up Report Layout

• Load the data model that includes the dynamically filtered table.
• Then, create a new table visual that will display the data based on the selected number of rows.

Step 7: Bind a Field to the Parameter in Model View

Switch to Model View, and do the following:

• Create or identify a field that contains values like 1, 2, 3… (e.g., a manually created table of integers).
• Select this field.
• In the Properties pane, scroll to Advanced > Bind to Parameter
• Choose the parameter (TopRanks) you created earlier.

Now this field controls the parameter.

Step 8: Create the Report Visuals

• Use the field you bound (e.g., values 1–10) in a Slicer
• Use your data table in a Table visual

Now when the user selects a value like 3, the underlying query fetches only the top 3 records.

Step 9: Test the Result

Try selecting different values from the slicer:

• Select 2 → only top 2 row appears

• Select 5 → now see top 5 rows

The number of rows adjusts dynamically — and efficiently — at the query level.

Considerations and Limitations

While Dynamic M Parameters can significantly enhance performance and interactivity in DirectQuery mode, there are some important considerations and limitations to be aware of:

• One-to-One Binding Only: A single parameter can only be bound to one field, and a single field can’t be bound to multiple parameters.
• No Aggregations: Dynamic M Parameters do not support aggregated values — you can only pass raw field values.
• Incompatibility with Row-Level Security: They aren’t compatible with Row-Level Security (RLS) at this time.
• Parameter Naming Restrictions: Parameter names cannot include spaces or be reserved DAX keywords. A good practice is to append “Parameter” to the name (e.g., TopNParameter) to avoid conflicts.
• Table Name Limitations: Table names used in queries shouldn’t contain spaces or special characters.
• Handling Date/Time Parameters: If your parameter is of Date/Time type, cast it explicitly using Date(<YourParameter>) within your M query.
• Security Prompts for SQL Sources: When using SQL sources, changing parameter values may trigger a confirmation dialog due to the security setting “Require user approval for new native database queries.” You can disable this in Power BI Desktop under Options → Security.
• Excel Compatibility: Dynamic M Parameters may not function correctly when consuming a Power BI semantic model from Excel.
• Not Supported in Power BI Report Server: These parameters currently aren’t supported in Power BI Report Server deployments.
• No Dynamic Source Switching in Service: Changing data sources dynamically via parameters isn’t supported in the Power BI Service. Refer to Power BI’s documentation on dynamic data sources for more details.
• Being aware of these limitations helps you design more stable and maintainable solutions using Dynamic M Parameters in Power BI.

 

Unsupported Parameter Data Types

The following parameter types are not supported when binding fields to M parameters:

• Any
• Duration
• True/False (Boolean)
• Binary

Unsupported Filters

Dynamic M Parameters do not support the following filtering options:

• Relative time slicers or filters
• Relative date filters
• Hierarchy slicers
• Multi-field include filters
• Exclude/Not filters
• Cross-highlighting
• Drill-down filters
• Cross-drill filters
• Top N filters

Unsupported Filter Operations

Certain filter logic cannot be used with fields bound to M parameters:

• And (logical AND operations)
• Contains
• Less than (<)
• Greater than (>)
• Starts with
• Does not start with
• Is not
• Does not contain
• Is blank
• Is not blank

Final Thoughts

Managing large datasets in Power BI often leads to performance challenges — long refresh times, sluggish queries, and overloaded models. While DirectQuery helps avoid heavy imports, it still fetches full datasets unless guided by smart filtering.

Dynamic M Parameters provide an elegant, scalable solution by enabling user inputs (via slicers) to directly influence the queries sent to the data source. This not only reduces the amount of data retrieved but also enhances overall report efficiency and responsiveness.

By pushing filtering logic to the source, you optimize backend processing and deliver a smoother refresh experience.

 

Reference Link:
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters#considerations-and-limitations