top of page
Antares Studio Games Icon_Small.png

The Antares Alien

Power BI   |  Game Development

  • Brent Jones

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: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters.


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.

Support my Indie game!
Steam

Itch.Io

Cover Art - Main_Medium_With_Title_Cropp
  • Facebook
  • Twitter
  • Instagram

Support my Indie game!
Steam

Itch.Io

Cover Art - Main_Large_With_Title.png
bottom of page