top of page
Antares Studio Games Icon_Small.png

The Antares Alien

Power BI   |  DAX

Performance of Dynamic M Query Parameters

Hint: Do not use dynamic M query parameters if you are expecting a performance boost.

Now that Microsoft allows us to use dynamic M query parameters with more sources like SQL server, I finally had a reason to test them out. But what kind of performance can you expect?


If you are unsure what dynamic M query parameters are, please see the documentation here:

Essentially they allow you to pass values from your report to power query to be used as a parameter (only for Direct Query mode).


Slow Performance

Initially I thought this could improve the report latency/speed for reports using direct query mode. After all, Microsoft themselves say: "Dynamic M query parameters can be especially useful for query performance optimization."

This, however, is misleading. After doing a number of speed tests, I found that using dynamic M parameters is about 26% slower than not using them.

Dynamic M Parameters are 26% slower

To run the tests, I used the Contoso sample dataset which contains 3,406,089 records. I created two measures; one for sales and one for sales last year. I put these on a data table so that it would be pretty expensive (time-wise) to execute.

What is the Use Case?

So, what is the use case then? From what I've seen, dynamic M parameters are a good option when you want to mimic behavior of something like a dynamic calculated column. In Power BI, you my know that calculated columns only calculate on a refresh, so developers have limited options for customizing reports. This is where dynamic M parameters can help. The following are two examples of this:

  • For example, in Chris Webb's blog post, he shows how you can set up dynamic binning using dynamic M parameters.

  • In this Curbal video, Ruth explains how you can switch the categories using dynamic M parameters.

So, in summary, you shouldn't use dynamic M parameters if you are looking to improve your report's latency or performance. Only use it if you have a business use-case as described above.


  • Facebook
  • Twitter
  • Instagram
bottom of page