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 Create a parameter .

Select New to add more parameters.

When you're done adding parameters, select OK .

Reference the parameters in the M query

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 tables of values

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))

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.

Bind the fields to the parameters

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 .

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:

Enable Select all

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:

Potential security risk

Report readers who can dynamically set the values for M query parameters may 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 may 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.
Mitigate the risk
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})
Considerations and limitations
There are some considerations and limitations when you use dynamic M query parameters:
A single parameter can't be bound to multiple fields nor vice-versa.
Dynamic M query parameters don't support aggregations.
Dynamic M query parameters don't support row-level security (RLS).
Parameter names can't be Data Analysis Expressions (DAX) reserved words nor contain spaces. You can append Parameter to the end of the parameter name to help avoid this limitation.
Table names can't contain spaces or special characters.
If your parameter is the Date/Time data type, you need to cast it within the M query as DateTime.Date(<YourDateParameter>).
If you use SQL sources, you might get a confirmation dialog every time the parameter value changes. This dialog is due to a security setting: Require user approval for new native database queries. You can find and turn off this setting in the Security section of the Power BI Desktop Options.
Dynamic M query parameters may not work when accessing a dataset in Excel.
Dynamic M query parameters are not supported on Power BI Report Server.
Unsupported out-of-box parameter types
Duration
True/False
Binary
Unsupported filters
Relative time slicer or filter
Relative date
Hierarchy slicer
Multifield include filter
Exclude filters / Not filters
Cross-highlighting
Drilldown filter
Cross drill filter
Top N filter
Unsupported operations
Contains
Less than
Greater than
Starts with
Does not start with
Is not
Does not contain
Is blank
Is not blank
Next steps
For more information about Power BI Desktop capabilities, check out the following resources:
DirectQuery in Power BI Desktop
What is Power BI Desktop?
Query overview with Power BI Desktop
Data types in Power BI Desktop
Shape and combine data with Power BI Desktop
Common query tasks in Power BI Desktop