Dynamic M query parameters in Power BI Desktop

With Dynamic M Query Parameters, model authors can let report viewers to use filters or slicers to set the value(s) for an M Query Parameter, which can be especially useful for query performance optimizations. With Dynamic M Query Parameters, model authors have additional control over how filter selections get incorporated into DirectQuery source queries.

 

From a Business perspective, it is mostly useful when you deal with big data loads, and you want to control exactly the Query generated at the data source level.

 

To enable and create Dynamic M query parameters in Power BI Desktop!

 

Interested? Please click in the following link to get the detailed documentation about it:

https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/

Also take a look at this easily explained guided video from youtube:

https://www.youtube.com/watch?v=c1mezIySFf8&list=PLDz00l_jz6zzttb28XH8GHZNL6vvpBlkQ&index=5

Unfortunately, the "Dynamic M Query Parameters" is only supported for M-based data sources, and not the Native SQL-based data sources (e.g. Teradata, Oracle, SQL Server, etc.), so the potential for usage is quite limited at the moment.

Refer to the "Considerations and Limitations" section of the Microsoft doc:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters#considerations-and-limitations

 

This blog below provides additional insights about the M-based and Native Connectors:

"Although there is no indication, Power BI has two types of connectors: native and M-based.

Native connectors target most popular relational data sources: TSQL (Azure Database, SQL Server, Synapse), PLSQL (Oracle), Teradata and relational SAP Hana.

Dynamic query parameters won’t work with native connectors. The rest (Microsoft provided and custom) are M-based. If an M connector supports DirectQuery, it should support dynamic query parameters too. For example, besides Azure Data Explorer, other M-based data source that supports DirectQuery and therefore dynamic parameters are Amazon Redshift, Showflake, and Google BigQuery."

 

[Blog] Power BI Dynamic M Query Parameters – Another Opportunity Missed for DirectQuery Users:

https://prologika.com/power-bi-dynamic-m-query-parameters-another-opportunity-missed-for-directquery-users/