Dynamic M query parameters in Power BI Desktop - Power BI (2023)

  • Article
  • 8 minutes to read

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.

Prerequisites

To work through these procedures, you must have a valid M query that uses one or more DirectQuery tables.

Create and use dynamic parameters

The following example passes a single value through to a parameter dynamically.

Add parameters

  1. In Power BI Desktop, select Home > Transform data > Transform data to open the Power Query Editor.

  2. In the Power Query Editor, select New Parameters under Manage Parameters in the ribbon.

    Dynamic M query parameters in Power BI Desktop - Power BI (1)

  3. In the Manage Parameters window, fill out the information about the parameter. For more information, see Create a parameter.

    Dynamic M query parameters in Power BI Desktop - Power BI (2)

  4. Select New to add more parameters.

    (Video) Revisiting a Power BI solution with Dynamic M Parameters

    Dynamic M query parameters in Power BI Desktop - Power BI (3)

  5. When you're done adding parameters, select OK.

Reference the parameters in the M query

  1. 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.

    Dynamic M query parameters in Power BI Desktop - Power BI (4)

  2. Reference the parameters in the M query, as highlighted in yellow in the following image:

  3. 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.

  1. In the Power BI Desktop ribbon, under Modeling, select New Table.

    Dynamic M query parameters in Power BI Desktop - Power BI (6)

  2. Create a table for the values of the StartTime parameter, for example:

    StartDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))

    Dynamic M query parameters in Power BI Desktop - Power BI (7)

  3. Create a second table for the values of the EndTime parameter, for example:

    EndDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))

    Dynamic M query parameters in Power BI Desktop - Power BI (8)

    (Video) Create Dynamic Query Parameters in Power BI Desktop - Power BI Tips & Tricks #47

    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.

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.

  1. 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.

  2. Select the dropdown under Bind to parameter and select the parameter that you want to bind to the field:

    Dynamic M query parameters in Power BI Desktop - Power BI (10)

    Since this example is for setting the parameter to a single value, keep Multi-select set to No, which is the default:

    Dynamic M query parameters in Power BI Desktop - Power BI (11)

    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:

    (Video) Power BI Quick Tip: Using Dynamic M Query Parameters with SQL​​

  3. Repeat these steps if you have other fields to bind to other parameters.

    Dynamic M query parameters in Power BI Desktop - Power BI (13)

You can now reference this field in a slicer or as a filter:

Dynamic M query parameters in Power BI Desktop - Power BI (14)

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:

Dynamic M query parameters in Power BI Desktop - Power BI (15)

To enable Select all for Country:

  1. 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.

    Dynamic M query parameters in Power BI Desktop - Power BI (16)

    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.

  2. 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.

    Dynamic M query parameters in Power BI Desktop - Power BI (17)

  3. 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:

    Dynamic M query parameters in Power BI Desktop - Power BI (18)

  4. 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.

    Dynamic M query parameters in Power BI Desktop - Power BI (19)

    (Video) Populate a Power BI parameter list using a query

  5. 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.

    Dynamic M query parameters in Power BI Desktop - Power BI (20)

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"

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] inCustomerByProductFn({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.

Unsupported out-of-box parameter types

  • Any
  • 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

  • And
  • 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:

(Video) How to update M query parameters based on filters and slicers

  • 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

FAQs

How do you pass multiple parameters in Power BI? ›

What you're asking for is certainly possible, but requires some extra steps.
  1. Add a parameter as text.
  2. Instruct users that they can enter multiple values by using `;` as a separator and no spaces.
  3. create a new query, using Text.Split(parameter_name, ";")

How do you use parameters in Power BI Power Query? ›

You can use a parameter in a query as a substitute value for the different folder.
  1. Select Home > Manage Parameters > New Parameter.
  2. Enter the following information in the Manage Parameter dialog box: Name. CSVFileDrop. Description. Alternate file drop location. Required. Yes. Type. Text. Suggested Values. Any value. ...
  3. Select OK.

How do I create a dynamic filter in Power BI? ›

How to Create a Dynamic Filter in Power BI
  1. Set your values for your Power BI dynamic filter.
  2. Create a new table in Power BI to hold data for the dynamic filter.
  3. Label the user selected display.
  4. Put the user's selection into context with the switch function.
  5. Create a filter for the user's selection.
Oct 4, 2022

How do I create a dynamic column in Power BI? ›

Power BI – Dynamic Columns in a Table
  1. Duplicate the table : Right click on the table and duplicate the table. ...
  2. Right click on column to Unpivot: Right click on the column you want the data to be pivoted & click on unpivot Other Columns. ...
  3. Select Unpivot Other Column : ...
  4. Close and apply changes :
Jun 29, 2022

What are the five methods of parameter passing? ›

Macro Expansion. Call-by-value, call-by-reference, and call-by-copy-restore all use eager evaluation: The arguments of a function call are evaluated immediately, that is, even before the body of the function is executed.

What are the two methods of passing parameters? ›

There are two ways to pass parameters in C: Pass by Value, Pass by Reference.
  • Pass by Value. Pass by Value, means that a copy of the data is made and stored by way of the name of the parameter. ...
  • Pass by Reference. A reference parameter "refers" to the original data in the calling function.

How do you use dynamic parameters? ›

Imitate the Functionality of Dynamic Parameters
  1. Create and format the default dates that will be shown when the workbook is opened.
  2. Set up parameters to give the user control over the time period selection.
  3. Create date filters to filter the charts to the needed time period.
  4. Include these elements on a dashboard.
Dec 3, 2021

What are the four types of parameters? ›

Supported parameter types are string, integer, Boolean, and array.

How do you pass parameters to a query? ›

To pass in parameter values, simply append them to the query string at the end of the base URL. In the above example, the view parameter script name is viewParameter1.

What is the difference between M and DAX? ›

DAX is a formula language. M Language is a query language. DAX is more powerful than M Language. For example, DAX can create KPIs and measures, while M cannot do that yet (though it might come in the future).

What field types can you use as dynamic filters? ›

Dynamic filters can be applied to any text/string field.

Is filter function static or dynamic? ›

The results of the Excel FILTER function are dynamic, meaning they update automatically when values in the original data set change.

How do you make a source dynamic in power query? ›

Dynamic Data Source in Power BI
  1. Step 1 – Connect to SQL Server and get Employee Table Data from DEV Database. ...
  2. Step 2: Create a Parameter in Power Query Editor for Environment Definition. ...
  3. Step 3: Connecting Parameters to the Source Table. ...
  4. Step 4: Changing the parameter value.
Aug 24, 2022

What are dynamic columns? ›

Dynamic columns is a feature that allows one to store different sets of columns for each row in a table. It works by storing a set of columns in a blob and having a small set of functions to manipulate it.

Can calculated column be dynamic? ›

The calculated column cant be dynamic. 2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result. Try using a calculated measure and see if that suits you.

What are 3 modes of parameters? ›

PL/SQL procedure parameters can have one of three possible modes: IN, OUT, or IN OUT.

What is parameter Passing example? ›

Parameter passing involves passing input parameters into a module (a function in C and a function and procedure in Pascal) and receiving output parameters back from the module. For example a quadratic equation module requires three parameters to be passed to it, these would be a, b and c.

What are the six parameters? ›

The Fluke ProSim 8 provides pre-sets for the six most commonly used parameters: • Electrocardiograph (ECG) • Non-invasive blood pressure (NIBP) • Invasive blood pressure (IBP) • Oxygen saturation (SpO2) • Respiration • Temperature When initially powered-on, the ProSim 8 defaults to a “Normal” pre-set for these six ...

How will you use params & dynamic in method parameters? ›

Out Parameters. Default Parameters or Optional Arguments (C# 4.0 and above) Dynamic parameter (dynamic keyword).
...
E.g.
  • private void Add(int w, int x = 10, int y = 0, int z = 5)
  • {
  • //do something.
  • }
  • Program p = new Program();
  • p.Add(15);
  • // pass 10 for y.
  • p. Add(15, 10); //but it will consider it as value for x not for y.
Nov 9, 2015

What are the two commonly used parameters? ›

In the normal distribution, there are two parameters that can characterize a distribution - the mean and standard deviation.

What are the two common parameters? ›

Most Common Parameters
  1. Mean. The mean is also referred to as the average, and it is the most commonly used among the three measures of central tendency. ...
  2. Median. The median is used to calculate variables that are measured with ordinal, interval, or ratio scales.
Dec 1, 2022

What are dynamic parameters? ›

Dynamic parameters are special types of parameters. Dynamic parameter value is recalculated each time you assess the parameter; i.e., this parameter acts as a function.

How do you know if a parameter is static or dynamic? ›

right. 1) for the parameters which can be altered by "SCOPE=BOTH" are the Dynamic parameters right, 2)for the parameters which can be altered by "SCOPE=SPFILE" or "SCOPE=PFILE" are the static parameters.

How do you use parameters in DAX query? ›

The DAX query syntax allows you to insert a parameter by using the @ symbol.
  1. EVALUATE.
  2. 'Product Subcategory',
  3. 'Product Category'[Product Category Name] = @Category )
  4. EVALUATE.
  5. CALCULATETABLE(
  6. 'Product Subcategory',
  7. 'Product Category'[Product Category Name] = @Category )
  8. = “EVALUATE CALCULATETABLE( 'Product Subcategory',
Jan 5, 2012

What are the three important parameters? ›

The three important parameters of big data are:
  • volume - refers to the amount of data.
  • variety - refers to the number of types of data.
  • velocity - refers to the speed of data processing.
Sep 28, 2020

How many parameters are in a method? ›

Parameters are specified after the method name, inside the parentheses. You can add as many parameters as you want, just separate them with a comma.

Why do we need parameters? ›

A parameter is a special kind of variable used in a function to refer to one of the pieces of data provided as input to the function. These pieces of data are the values of the arguments with which the function is going to be called/invoked.

How do you pass multiple parameters in query? ›

Passing Multiple Parameters for QueryString

To pass multiple parameters, we will use “&” symbol to separate the other field and value combinations.

Is there a limit on query parameters? ›

Although officially there is no limit specified by RFC 2616, many security protocols and recommendations state that maxQueryStrings on a server should be set to a maximum character limit of 1024. While the entire URL, including the querystring, should be set to a max of 2048 characters.

How do you pass a parameter to a variable? ›

Variable Passing. Parameters are passed to IDL system and user-written procedures and functions by value or by reference. It is important to recognize the distinction between these two methods. IDL will create variables s and t with the assigned values.

Which is faster M or DAX? ›

So the answer is; there is no best language between these two. The type of usage identifies which one is best. Normally any changes to prepare the data for the model is best to be done in M, and any analysis calculation on top of the model is best to be done in DAX.

Which is faster DAX or Power Query? ›

PQ calculation slows down data load, where as DAX calculated column will slow down visual update on interaction with slicer etc. Most cases, if you do data load during after hours (or early in the morning) once. It's more desirable to have better performance during interaction with report.

Is M the same as Power Query? ›

A core capability of Power Query is to filter and combine, that is, to "mash-up" data from one or more of a rich collection of supported data sources. Any such data mashup is expressed using the Power Query Formula Language (informally known as "M").

What are the four types of filters? ›

The four primary types of filters include the low-pass filter, the high-pass filter, the band-pass filter, and the notch filter (or the band-reject or band-stop filter).

What is dynamic content filtering? ›

Dynamic filtering analyzes the web content and looks for category specific content to identify the category or type of content found within the web page rather than of referencing a catalogue of websites.

Which three methods are used to generate filters? ›

Three methods – init(), doFilter(), destroy().

What is dynamic function example? ›

Dynamic function is a way of dynamically invoking a function call. The compiler will have limited knowledge of what you are up to so you will get run time errors if you don't use correct inputs and outputs. One example that runs different functions depending on user input: DEFINE VARIABLE iFunc AS INTEGER NO-UNDO.

Which method is not supported for dynamic types? ›

Hence Dynamic polymorphism is not supported for static methods.

How do you tell if a filter is active or passive? ›

Active filters require a power source to operate, whereas passive filters don't. The output of passive filters changes with the load, whereas active filters maintain their performance irrespective of the load connected. Passive filters cannot apply additional gain to the signal, whereas active filters can.

How do you pass multiple values in a parameter? ›

In the General tab, select Allow multiple values to allow a user to select more than one value for the parameter. (Optionally) In the Available values tab, specify a list of available values to display to the user.

Is it possible to pass multiple arguments to a function? ›

Functions can accept more than one argument. When calling a function, you're able to pass multiple arguments to the function; each argument gets stored in a separate parameter and used as a discrete variable within the function.

How do I pass multiple parameters in a data page? ›

Pass the parameter values by putting them in quotes and comma separated. For Ex: you need to get details for Address with HOME and OFFICE as values then pass "HOME","OFFICE" as values to parameter Address.

How many parameters can be passed to a function? ›

Except for functions with variable-length argument lists, the number of arguments in a function call must be the same as the number of parameters in the function definition. This number can be zero. The maximum number of arguments (and corresponding parameters) is 253 for a single function.

How do you pass a value in a query parameter? ›

To pass in parameter values, simply append them to the query string at the end of the base URL. In the above example, the view parameter script name is viewParameter1.

How do you pass parameters by value and reference? ›

Pass-by-reference means to pass the reference of an argument in the calling function to the corresponding formal parameter of the called function. The called function can modify the value of the argument by using its reference passed in. The following example shows how arguments are passed by reference.

How many parameters for a function is too many? ›

Functions with three arguments (triadic function) should be avoided if possible. More than three arguments (polyadic function) are only for very specific cases and then shouldn't be used anyway.

How many can max number of arguments? ›

How many can max number of arguments present in function in the c99 compiler? Explanation: C99 allows to pass a maximum of 127 arguments in a function.

Videos

1. Power Bi : Huge Update!! Dynamic Parameters for Transact SQL
(Dapper Dash)
2. Dynamic M Query parameters explained: How it works, security concerns and limitations
(Curbal)
3. Power Bi : Setting up Dynamic Parameters
(Dapper Dash)
4. How to Use Power BI Parameters to Create Dynamic Power BI Reports
(Avi Singh - PowerBIPro)
5. Dynamic Power BI reports using Parameters
(Guy in a Cube)
6. Making data source parameters easy in Power BI Desktop
(Guy in a Cube)
Top Articles
Latest Posts
Article information

Author: Lidia Grady

Last Updated: 10/14/2022

Views: 6152

Rating: 4.4 / 5 (65 voted)

Reviews: 80% of readers found this page helpful

Author information

Name: Lidia Grady

Birthday: 1992-01-22

Address: Suite 493 356 Dale Fall, New Wanda, RI 52485

Phone: +29914464387516

Job: Customer Engineer

Hobby: Cryptography, Writing, Dowsing, Stand-up comedy, Calligraphy, Web surfing, Ghost hunting

Introduction: My name is Lidia Grady, I am a thankful, fine, glamorous, lucky, lively, pleasant, shiny person who loves writing and wants to share my knowledge and understanding with you.