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.
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
True, and I can imagine a m2m solution too, but you’re right neither are particularly elegant
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.
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.
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*
@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
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
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.
Silly question but, I assume this will work in Power BI service not just with Desktop correct?
Yes, it does work in the Service
This would be a really useful feature. I have wasted an astonishing amount of time trying to make it work. I have tried to follow instructions here: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters#considerations-and-limitations and here: https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/. I’m doing something wrong, but can find no clues to what.
I have to use DirectQuery. My source is an Excel file; this page tells me that should be OK https://docs.microsoft.com/en-us/power-bi/connect-data/service-excel-workbook-files but I have no option to upload rather than import.
The upshot is that I get no option to bind my column to the selection parameter.
What’s annoying about all this is the struggles I’ve had to find out what’s going wrong – failing to find out. I’m not a naive user but PowerBI is a minor part of my life. I just want to be able to use it and not have to bring to mind all of its private language and special ways of doing things. If instructions about how to use something require some background knowledge then they should point that out at the start. (I’m looking at you, docs.microsoft.com.)
The reason you can’t make it work is that dynamic M parameters can only be used in DirectQuery mode and you can’t connect to an Excel workbook in DirectQuery mode. You can only use Import mode with Excel.
Hey Chris,
I can’t seem to do a simple binding to a Kusto query. It is giving me an error that “DirectQuery may not be used with this data source – please consider moving to a supported data source or upgrading the SQL Server data source to the latest available version.”
I am not using SQL. I have one DQ query to Kusto passing in the 1 parameter and as soon as I bind the parameter to a column in a static table it errors out the visual with the error above.
Any tips?
Maja
I think I just figured it out. The table NAMES cannot have spaces in them. Oh Microsoft.
I have spent more time than I care to admit looking for help on this topic. Most posts explain HOW to design this but not what the advantages are. In general where I work we assume import is needed due to direct query being too slow, but I thought *maybe* this feature would provide more efficient DQ queries plus I hate to see us not try to address this first giving DQ a real try.
Use Case: many reports are group supports will always filter out of the gate to a single warehouse only (based on prompt value selection), then pass that into the query of our Warehouse Dimension which joins to the fact-like table which has the high volume data. Users would not go back in most cases and change the selected warehouse value since they only care about the warehouse they work in and there are no corporate users needing to these report. They are warehouse-specific- users.
Potential factors here: 1. The selected value does not tie to a column that resides in the fact table. Dunno if that matters since there is a join in the dataset. 2. Our source is Azure Synapse so it looks like not supported (which seems unusual if true) …but either way I’d love to hear an explanation on why this feature could benefit this pattern. 3. I’m newer to Power BI. I have designed some Report Builder reports. I’m accustomed to passing selected parameter values into queries.
Since query folding is available there’s an argument I saw that no real benefit to doing dynamic query parameter. But I know PBI creates some crazy SQL at times so I could see having direct control over the sql could help some. But does it go further than that benefit-wise? Also, my goal was that only the visual for the Warehouse slicer/dropdown be selectable when report is *opened* by users on the service (plus one other slicer potentially), THEN after the values are selected, that the other visual queries are fired, with the filter value applied before querying the database. But I was told this can’t be done unless I use a landing page…which seems like a workaround solution to me. But I’m open to it and barely scratched the surface. Basically my goal here is to ensure the most efficient SQL is passed into visuals queries when the report is opened, and also when users change other slicers or controls inline on the report page (if that plays into this topic). Plus, I need to be able to expose my warehouse dropdown list slicer on the report, which I think I can do but I also saw videos where person showed it being done on desktop from menu, which I hope is just another way to get to this. Most our users are read only access and licensing in power bi and accessing on the service. They don’t have PBI desktop.
Thanks and apologies for the message length. I’ve tried to find answers but got desperate. 🙂
Hi Chris,
Do you know if there any limitations left after the February 2022 update?