Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
This article describes how to create and work with dynamic M query parameters in Power BI Desktop. With dynamic M query parameters, model authors can configure the filter or slicer values that report viewers can use for an M query parameter. Dynamic M query parameters give model authors more control over the filter selections to incorporate into DirectQuery source queries.
Model authors understand the intended semantics of their filters, and often know how to write efficient queries against their data source. With dynamic M query parameters, model authors can ensure that filter selections incorporate into source queries at the right point to achieve the intended results with optimum performance. Dynamic M query parameters can be especially useful for query performance optimization.
Watch Sujata explain and use dynamic M query parameters in the following video, and then try them out yourself.
Note
This video might use earlier versions of Power BI Desktop or the Power BI service.
To work through these procedures, you must have a valid M query that uses one or more DirectQuery tables.
The following example passes a single value through to a parameter dynamically.
In Power BI Desktop, select Home > Transform data > Transform data to open the Power Query Editor.
In the Power Query Editor, select New Parameters under Manage Parameters in the ribbon.
In the Manage Parameters window, fill out the information about the parameter. For more information, see Creating a parameter.
Select New to add more parameters.
When you're done adding parameters, select OK.
Once you create the parameters, you can reference them in the M query. To modify the M query, while you have the query selected, open the Advanced Editor.
Reference the parameters in the M query, as highlighted in yellow in the following image:
When you're done editing the query, select Done.
Create a table for each parameter with a column that provides the possible values available to be dynamically set based on filter selection. In this example, you want the StartTime
and EndTime
parameters to be dynamic. Since these parameters require a Date/Time
parameter, you generate the possible inputs to dynamically set the date for the parameter.
In the Power BI Desktop ribbon, under Modeling, select New table.
Create a table for the values of the StartTime
parameter, for example:
StartDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))
Create a second table for the values of the EndTime
parameter, for example:
EndDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))
Note
Use a column name that's not in an actual table. If you use the same name as an actual table column, the selected value applies as a filter in the query.
Now that you created the tables with the Date
fields, you can bind each field to a parameter. Binding a field to a parameter means that as the selected field value changes, the value passes to the parameter and updates the query that references the parameter.
To bind a field, in the Power BI Desktop Model view, select the newly created field, and in the Properties pane, select Advanced.
Note
The column data type should match the M parameter data type.
Select the dropdown under Bind to parameter and select the parameter that you want to bind to the field:
Since this example is for setting the parameter to a single value, keep Multi-select set to No, which is the default:
If you set the mapped column to No for Multi-select, you must use a single select mode in the slicer, or require single select in the filter card.
If your use cases require passing multiple values to a single parameter, set the control to Yes and make sure your M query is set up to accept multiple values. Here's an example for RepoNameParameter
, which allows multiple values:
Repeat these steps if you have other fields to bind to other parameters.
You can now reference this field in a slicer or as a filter:
In this example, the Power BI Desktop model has a field called Country, which is a list of countries/regions bound to an M parameter called countryNameMParameter. This parameter is enabled for Multi-select, but isn't enabled for Select all. To be able to use the Select all option in a slicer or filter card, take the following added steps:
To enable Select all for Country:
In the Advanced properties for Country, enable the Select all toggle, which enables the Select all value input. Edit the Select all value or note the default value.
The Select all value passes to the parameter as a list that contains the value you defined. Therefore, when you define this value or use the default value, make sure the value is unique and doesn't exist in the field that's bound to the parameter.
Launch the Power Query Editor, select the query, and then select Advanced Editor. Edit the M query to use the Select all value to refer to the Select all option.
In the Advanced Editor, add a Boolean expression that evaluates to true
if the parameter is enabled for Multi-select and contains the Select all value, and otherwise returns false
:
Incorporate the result of the Select all Boolean expression into the source query. The example has a Boolean query parameter in the source query called includeAllCountries
that is set to the result of the Boolean expression from the previous step. You can use this parameter in a filter clause in the query, such that false
for the Boolean filters to the selected country or region names, and true
effectively applies no filter.
Once you update your M query to account for the new Select all value, you can use the Select all function in slicers or filters.
For reference, here's the full query for the preceding example:
let
selectedcountryNames = if Type.Is(Value.Type(countryNameMParameter), List.Type) then
Text.Combine({"'", Text.Combine(countryNameMParameter, "','") , "'"})
else
Text.Combine({"'" , countryNameMParameter , "'"}),
selectAllCountries = if Type.Is(Value.Type(countryNameMParameter), List.Type) then
List.Contains(countryNameMParameter, "__SelectAll__")
else
false,
KustoParametersDeclareQuery = Text.Combine({"declare query_parameters(",
"startTimep:datetime = datetime(", DateTime.ToText(StartTimeMParameter, "yyyy-MM-dd hh:mm"), "), " ,
"endTimep:datetime = datetime(", DateTime.ToText(EndTimeMParameter, "yyyy-MM-dd hh:mm:ss"), "), ",
"includeAllCountries: bool = ", Logical.ToText(selectAllCountries) ,",",
"countryNames: dynamic = dynamic([", selectedcountryNames, "]));" }),
ActualQueryWithKustoParameters =
"Covid19
| where includeAllCountries or Country in(countryNames)
| where Timestamp > startTimep and Timestamp < endTimep
| summarize sum(Confirmed) by Country, bin(Timestamp, 30d)",
finalQuery = Text.Combine({KustoParametersDeclareQuery, ActualQueryWithKustoParameters}),
Source = AzureDataExplorer.Contents("help", "samples", finalQuery, [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Timestamp", "Date"}, {"sum_Confirmed", "Confirmed Cases"}})
in
#"Renamed Columns"
Report readers who can dynamically set the values for M query parameters might be able to access more data or trigger modifications to the source system by using injection attacks. This possibility depends on how you reference the parameters in the M query and what values you pass to the parameters.
For example, you have a parameterized Kusto query constructed as follows:
Products
| where Category == [Parameter inserted here] & HasReleased == 'True'
| project ReleaseDate, Name, Category, Region
There are no issues with a friendly user who passes an appropriate value for the parameter, for example, Games
:
| where Category == 'Games' & HasReleased == 'True'
However, an attacker might be able to pass a value that modifies the query to get access to more data, for example, 'Games'//
:
Products
| where Category == 'Games'// & HasReleased == 'True'
| project ReleaseDate, Name, Category, Region
In this example, the attacker can get access to information about games that haven't released yet by changing part of the query into a comment.
To mitigate the security risk, avoid string concatenation of M parameter values within the query. Instead, consume those parameter values in M operations that fold to the source query, so that the M engine and connector construct the final query.
If a data source supports importing stored procedures, consider storing your query logic there and invoking it in the M query. Alternatively, if available, use a parameter-passing mechanism that's built in to the source query language and connectors. For example, Azure Data Explorer has built-in query parameter capabilities that are designed to protect against injection attacks.
Here are some examples of these mitigations:
Example that uses the M query's filtering operations:
Table.SelectRows(Source, (r) => r[Columns] = Parameter)
Example that declares the parameter in the source query, or passes the parameter value as an input to a source query function:
declare query_parameters (Name of Parameter : Type of Parameter);
Example of directly calling a stored procedure:
let CustomerByProductFn = AzureDataExplorer.Contents("Help", "ContosoSales"){[Name="CustomerByProduct"]}[Data] in
CustomerByProductFn({1, 3, 5})
There are some considerations and limitations to take into account when you use dynamic M query parameters:
Parameter
to the end of the parameter name to help avoid this limitation.Date/Time
data type, you need to cast it within the M query as DateTime.Date(<YourDateParameter>)
.For more information about Power BI Desktop capabilities, check out the following resources:
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayTraining
Module
Configure Power BI report filters - Training
Report filtering is a complex topic because many techniques are available for filtering a Microsoft Power BI report. However, with complexity comes control, allowing you to design reports that meet requirements and expectations.
Certification
Microsoft Certified: Power BI Data Analyst Associate - Certifications
Demonstrate methods and best practices that align with business and technical requirements for modeling, visualizing, and analyzing data with Microsoft Power BI.