DirectQuery · Dynamic M Parameters · M · Power BI · Power Query

Passing Any Arbitrary Value From A Power BI Report To A Dynamic M Parameter

Dynamic M parameters are incredibly useful in Power BI when using DirectQuery mode because they allow you to pass values from your report direct to your data source query, bypassing Power BI’s own SQL (or whatever query language your source uses) generation layer. However it isn’t obvious how to pass any value you want: dynamic M parameters must be bound to slicers or filters, and they must be bound to tables in your dataset, which means that at first glance it seems like you can only pass values that are already present somewhere in your dataset (for example in a dimension table) to a dynamic M parameter. This isn’t true though: there is a way to allow passing of any value your users enter to a dynamic M parameter and in this post I’ll show you how.

First of all, why is this useful? There are two reasons why you might want to allow passing of any value to a dynamic M parameter rather than binding to a slicer or filter in the normal way:

  1. Displaying the list of possible values in a slicer or filter can be expensive, especially if the column you’re slicing on contains thousands or millions of values. It can slow down your report and lead to extra queries being run on your DirectQuery source, which can lead to performance problems.
  2. Sometimes the values you want to pass in don’t exist in your DirectQuery source. The scenarios where this is useful are very similar to the scenarios where you’d want to use what-if parameters, but the big limitation of what-if parameters is that you have to pre-calculate all the values that a user might ever want to select and store them in a table. What happens if you don’t want to, or can’t, pre-calculate all the input values?

Let’s see an example of how you can pass any value you want to a dynamic M parameter.

The AdventureWorksDW2017 sample SQL Server database has a table called FactInternetSales with a column called SalesOrderNumber, and let’s say you want to build a Power BI report where an end user can enter whatever SalesOrderNumbers they want and filter the table by them. Let’s also assume that we have to use dynamic M parameters to do this efficiently (which isn’t the case with AdventureWorksDW2017 but could well be in the real world). Here’s what the data looks like:

To set things up in the Power Query Editor you need an M parameter to hold the values entered by the end user. In this example the parameter is called SalesOrderNumber:

Note that the Current Value property is set to a value that is not a valid sales order number, so when no input has been received from the users then no rows will be returned by the report.

Next you need a query that filters the FactInternetSales table by this parameter. Here’s the M code:

let
  Source = Sql.Database(
    "localhost",
    "AdventureWorksDW2017"
  ),
  dbo_FactInternetSales = Source
    {
      [
        Schema = "dbo",
        Item   = "FactInternetSales"
      ]
    }
    [Data],
  #"Removed Other Columns"
    = Table.SelectColumns(
    dbo_FactInternetSales,
    {
      "OrderDateKey",
      "SalesOrderNumber",
      "SalesOrderLineNumber",
      "SalesAmount"
    }
  ),
  FilterList =
    if Type.Is(
      Value.Type(SalesOrderNumber),
      List.Type
    )
    then
      SalesOrderNumber
    else
      {SalesOrderNumber},
  #"Filtered Rows" = Table.SelectRows(
    #"Removed Other Columns",
    each List.Contains(
      FilterList,
      [SalesOrderNumber]
    )
  )
in
  #"Filtered Rows"

There are two interesting things to notice here:

  • This code handles the cases where a user enters a single value, in which case the SalesOrderNumber M parameter will be of type text, or when the user enters multiple values in which case the SalesOrderNumber M parameter will be of type list. For more details on handling multi-select in dynamic M parameters see this post.
  • The actual filtering is done using the List.Contains M function, which does fold on SQL Server-related data sources. If you’re using other sources you should check if query folding happens for List.Contains for your source.

Thirdly, you need a dummy dimension table with a single column for the dynamic M parameter to be bound to in the report. The dimension table shouldn’t contain any data; here’s the M code to use (the query is called DimSalesOrderNumber):

let
  Source = #table(
    type table [SalesOrderNumber = text],
    {}
  )
in
  Source

This query returns a table with a single text column called SalesOrderNumber and no rows:

Once you’ve left the Power Query Editor the next thing to do is to bind the SalesOrderNumber M parameter to the SalesOrderNumber column of the DimSalesOrderNumber table:

Note that the Multi-select property has been enabled. Binding the dynamic M parameter to a table with no rows in means there’s no way a regular slicer could be used with it, because there are no values in the table for the slicer to display.

The last problem to solve is the important one: how do you allow end users to enter any value they want? There are two ways I know of. One is to use the filter pane and the “is” filter type under “Advanced filter”:

The filter pane is great because it’s built-in but it only allows the user to enter one or two (if they use the “Or” option) values to filter on. Remember also that not all filter types are available when you’re using dynamic M parameters.

A better approach, if you’re ok with using custom visuals, is to use the Filter By List custom visual which allows the end user to enter – or even copy/paste in – a list of values:

19 thoughts on “Passing Any Arbitrary Value From A Power BI Report To A Dynamic M Parameter

  1. I’ve been wondering if i can do this for ages, I’m not sure why it wasn’t clear to me at first that you need to place the value from the “dummy dimension table” into the filter pane but i got there eventually.

  2. Thanks Chris, that’s great! Are there also ways to do this from an excel-based report connected to the model within pivot tables, using CUBE functions, and/or via Power Query?

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      No, as far as I know there’s no way of using Dynamic M Parameters in MDX

  3. the dropdown “Bind to Parameter” is not shown in “Field” – “Properties” – “Advanced” section,
    I have done all the necessary configurations, I have the latest version of the Power BU desktop, the fact table is in Direct Query, the parameter and the column to be bound to in the report have the same type
    I find this strange, could you suggest a solution plz?

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      What data type are you using for your M parameter? There is a bug in the current version of Power BI Desktop that means the configuration disappears if you are using a numeric type.

  4. It seems you can only apply the parameter to one query and not multiple, is that correct? Would there be a workaround for this (instead of creating multiple parameters).
    I’m trying to apply filters to multiple (direct) queries at the same time (all the same value).

    It also seems that if you create a relationship between the m-parameter filtered table and another and when you have both in separate visuals, the filter isn’t automatically passed on to the other “related” visual (that would have resolved the first statement 🙂 ).

  5. How would i use date flags as parameters to pass , for ex I wana design a report for last 4 weeks and change it later to last 8 weeks. How can this be done ?

  6. Thanks for this, before i go down the rabbit hole. The question i have is can you use the parameters to perform a calculation within the SQL query as well as use a parameter to filter the initial dataset.

    What i want to do is apply different parameters, one on “item type”, this limits the base data to a couple of types. I then want to use a different parameter on one item type to perform a multiplication, and a third parameter that mimics the calculation but is a different value.

    Basically a what if type analysis, if i have x amount of product a or y amount of product b and be able to display the numbers side by side in Power BI.

  7. Hi, Is Dynamic Query Parameters supported in Power Bi Report Server for the end users? If so how is this achievable please, as far as i understand it you can only use parameters in Power Bi Desktop App?

  8. The approach with the empty parameter column is ingenious. This is a game changer for scenarios where random user input is required for scenario calculations. Marvellous!

    1. A couple more comments:
      – no need to create an empty table in Power Query. Any disconnected table will do, including things like Table1 = ROW(“YM1″,”202212”) or Table2 = UNION(ROW(“YM2″,”202212”),ROW(“.”,”202301″)) . Advanced filtering allows to override any existing value.
      – I also tried to be clever and “Enable Load” for the M parameter (to obviate the need for any table at all, and for the binding process). Sadly that doesn’t work – the parameter value change is not transported back from Power BI to Power Query,

  9. Hello Chris,

    Great work!!

    I did the same way as you have explained but table visual is still displaying as empty.

    Is it possible to send the power bi report to my emails. Thanks

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.