Why I’m Excited About Dynamic M Parameters In Power BI

My favourite feature in the October 2020 release of Power BI is undoubtedly dynamic M parameters. However, before I explain why, I’m going to put my hands up and admit that we screwed up the communication around its launch and didn’t explain its limitations properly – which led to some understandable disappointment and confusion.

Although the blog post and the docs have now been updated to make it clear that this feature doesn’t work with many popular data sources like SQL Server, Synapse, Oracle, Teradata and SAP (see here for the full details), I thought I’d explain why. It’s because there are two sets of connector that Power BI uses for DirectQuery: an older set that was built some time ago for the data sources originally supported by Analysis Services Tabular, and a newer set that is built in Power Query’s M language. Dynamic M parameters only work for this newer set of connectors; if you want to see this feature extended so it works for the older connectors too then please vote on ideas.powerbi.com, talk to your Microsoft account team, call Satya or whatever your normally do.

OK, so why am I excited? Well, because dynamic M parameters let you do a whole bunch of cool things that weren’t possible before. My original plan for this blog post was to show how they can be used to change the dimension column used in a visual dynamically but Mim beat me to it with this blog post. Instead in this post I’m going to show you how you can use them to create something like dynamic calculated columns and I also want to make a more general point that will Blow Your Mind (hopefully).

For my example I’m going to use Azure Data Explorer (ADX) as my data source. There are a couple of reasons why: it’s something I’ve been interested to learn more about for a while, and the ADX team recently announced a bunch of new features which make me think it will be increasingly useful in BI scenarios; it will be important to know it if you’re using the new Azure Monitor integration for Power BI log data; and, crucially, it’s one of the few M-based connectors where you can write your own queries in DirectQuery mode (although this is coming to Snowflake and Redshift next year too).

My source data is from the UK Land Registry Price Paid dataset, and for my demo report I loaded a table containing around 790,000 real estate transactions into ADX. Each real estate transaction has the price paid for the property:

One obvious way to analyse this data is to build a histogram and show the number of transactions that occur in price bins such as £0-£100000, £100000-£200000, £200000-£300000 and so on. This is quite easy to do in Power BI, for example using calculated columns in Import mode, unless you want to allow your users to be able to change the bin sizes themselves. Dynamic M parameters give you an easy way to solve this problem though.

KQL, the query language for ADX, has a really nice way of generating bins for histograms using the summarize() operator and bin() function. For example, the following KQL query does something like a SQL group by and shows the number of real estate transactions for each county and bins based on price in £25000 increments:

pricepaid 
| summarize Transactions=count() 
by county, bin(price, 25000)

In Power BI I created a parameter called binsizes:

…and then created a parameterised version of the KQL query above to populate a DirectQuery table. Here’s the M code for this table:

let
    Source = 
     AzureDataExplorer.Contents(
      "https://mycluster.northeurope.kusto.windows.net", 
      "pricepaid", 
      "pricepaid | summarize Transactions=count() by county, bin(price, " 
      & Number.ToText(binsizes) & 
      ")", 
      [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null]
     )
in
    Source

Next I created a new table with the possible bin sizes that the user can choose using a DAX calculated table:

And then finally linked the column in this new table with the M parameter:

Here’s the final report, where you can see the number of transactions in each price bin and use slicers to change the price bins and change the county you’re looking at:

Apart from being a new solution to an old problem, what does this mean? Well, with a data source like ADX where M parameters are supported and you can write your own queries in DirectQuery mode, you can do whatever you want in the query so long as it always returns the same columns. As well as creating parameterised queries, in ADX you could also create a user-defined function (something like table-valued functions in SQL Server), pass parameters into it using M parameters, and have a totally dynamically generated fact table in your dataset. Either way, using dynamic M parameters in this way allows you to use KQL’s specialised functions for basket analysis or geospatial analysis to do things that are otherwise impossible in Power BI. I’m sure similar things are possible with other data sources too. And that, my friends, is why I’m so excited about dynamic M parameters.

14 responses

  1. Thanks for sharing this Chris. I have to admit, I am still struggling to understand the benefit of this feature, but if you are excited, then clearly I must be missing something. The reason I don’t get it is because my understanding is that when using direct query mode, the Power BI UI builds the query, the query is sent using query folding back to the source, the query is executed over the subset of data required, the numbers required to populate the visual are then returned to the report. I don’t understand what Dynamic M Parameters adds to make this process better. Can you help me understand?

    • The short answer is this: it’s the only way you can do the dynamic binning in this post. It’s not possible in Import mode, and it’s not possible in DirectQuery mode without dynamic M parameters.

      • we can dynamic bin in import mode with a disconnected tally table… did it several years ago for a client as part of a “tableau vs power bi bakeoff”… not pretty at all, but definitely possible

  2. Out of curiosity Chris, how many customers do we have using the M DirectQuery data sources compared to native to justify a rollout of such an useful feature for M data sources only?

    • I can’t go into the details of why this feature was only built for M connectors here, but I can assure you there’s a perfectly boring, rational explanation for it.

  3. Brilliant and interesting use case for sure. Thank you for sharing!

    Sorry it doesn’t support some common connection types, but it’s good to know the capability is there.

  4. Why not make this feature available for all data modes? Why wouldn’t import mode be the first in line instead of not mentioned at all? This is a major feature Tableau still dominates Power BI in. It’s hard to justify encouraging my enterprise’s Tableau users to migrate until Microsoft figures out these basics. Unfortunately it sounds like it’s not even on the backlog. *sigh*

  5. @Chris,
    Is it not ironical that the ability to pass parameters via a slicer was possible in PQ in Excel against any data source since 2010 gets people excited today after it gets partially implemented in PBI Desktop on a set of data sources that nobody cares about

  6. Hi Chris,

    Thank you for the article. I am trying to implement the same with Postgresql using the below code and its not allowing me to pass the parameters through the slicer. Is below the correct way to incorporate a dynamic M parameter in a query.
    let
    p_matter = P_MATTER,
    Source = PostgreSQL.Database(“host”, “database”,
    [Query=”SELECT * FROM schema.table WHERE ID ='” &p_matter& “‘ “,
    CommandTimeout=#duration(0, 0, 10, 0), CreateNavigationProperties=false])
    in
    Source

  7. Pingback: Chris Webb's BI Blog: Geospatial Analysis With Azure Data Explorer, Power BI And Dynamic M Parameters Chris Webb's BI Blog

  8. Pingback: Parámetros de consulta dinámica M - Power Query - dataXbi

  9. Thank you Chris. I think a good use case for this is toggling measure formats between magnitude ($0.0B, $0.0M, $0.0K) while changing Title with Measure FX.

  10. Pingback: Power BI in Brief–December 2020 - Microsoft Dynamics NAV Community

Leave a Reply

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

%d bloggers like this: