Jan 25, 2023

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.

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.