Using Power BI Dynamic M Parameters In DAX Queries

As I’m sure you’ve guessed by now I’m a big fan of dynamic M parameters in Power BI. They’re easy to use in Power BI Desktop but what if you want to use them in your own DAX queries? Documentation for this is coming soon, but in the meantime I thought it would be useful to show the additions to DAX query syntax to support them – something you can see for yourself if you take a look at the DAX queries generated by Power BI Desktop using Performance Analyzer.

Here’s an example of a query generated by Power BI Desktop where there are three dynamic M parameters defined: DateParameter, TextParameter and NumericParameter.

DEFINE
  MPARAMETER DateParameter = 
    DATE(2020, 1, 1)

  MPARAMETER TextParameter = 
    "January"

  MPARAMETER NumericParameter = 
    1

  VAR __DS0FilterTable = 
    TREATAS({DATE(2020, 1, 1)}, 'ParamValues'[DateP])

  VAR __DS0FilterTable2 = 
    TREATAS({"January"}, 'ParamValues'[MonthNameP])

  VAR __DS0FilterTable3 = 
    TREATAS({1}, 'ParamValues'[MonthNoP])

  VAR __DS0Core = 
    CALCULATETABLE(
      DISTINCT('DATE'[DATE]),
      KEEPFILTERS(__DS0FilterTable),
      KEEPFILTERS(__DS0FilterTable2),
      KEEPFILTERS(__DS0FilterTable3)
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(501, __DS0Core, 'DATE'[DATE], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  'DATE'[DATE]

The dynamic M parameters are set using a DEFINE statement and the new MParameter keyword. The name of the parameter here is the same as the name of the parameter defined in the Power Query Editor; one thing to point out is that if your M parameter name contains a space, it should be surrounded by single quotes.

If you have enabled multi-select on your dynamic M parameter, you will need to pass a table of values to it in your DAX query. Here’s an example of how to do this with a table constructor:

DEFINE
  MPARAMETER 'MonthParameter' = 
    {"January",
      "February"}

I’ll admit I haven’t tried this yet, but you should be able to do some really cool stuff with this in a paginated report connected to a DirectQuery dataset if it contains dynamic M parameters. Maybe in a future post…

11 responses

  1. Chris, thank you. It would be real helpful if you could say how to get the feature you are describing. ie which version of Excel etc. I find that our Enterprise MS Office takes a year or more to get new features. So if there is a DLL rather than the whole of Office that would enable the feature, that would be great to know as well.

  2. Pingback: Power BI Insights: Partition configurations; Dynamic M parameters; DirectQuery | MSDynamicsWorld.com

  3. Pingback: Using Power BI Dynamic M Parameters In DAX Queries | Pardaan.com

  4. Pingback: Power BI Insights: Partition configurations; Dynamic M parameters; DirectQuery ERP for Hong Kong SME

  5. This is very helpful information. Firstly an explanation of what I’m trying to achieve and haven’t succeeded (yet). I want a Power BI dashboard user to single select a value from a slicer and have this passed to Power Query as a parameter which is then added to a string for a JSON API call. In Power BI desktop when I use Transform data -> Edit parameters and put the value in the dialog, it works, therefore the passing of the parameter from Power BI to the parameter in Power Query is working. I put the following into DAX with the objective of having the parameter selected from the slicer. The parameter value seems to get correctly set (I tested by placing the value on a card) but does not seem to pass to Power Query:

    DEFINE MPARAMETER Postcode_District = ALLSELECTED(‘Postcode District'[Postcode District Value])

    The list of Postcode_District values are in the ‘Postcode District’ table. Any insight will be greatly appreciated; it feels I’m close and this will be a big breakthrough with the Power BI use case I’m developing for a client.

    • I’ve done some deep digging and research on this. I’ve concluded that it isn’t supported and doesn’t work. This would be a fantastic feature to have in Power BI/Power Query. I also explored an alternative with storing the data on SQL Server hosted in Azure. Also doesn’t work as this is also not supported. I’m concluding that M Query Parameters is a feature with great potential and limited reality right now. The limitation is that data sources that are most likely to be in place today are not supported. I learned many things from the research, therefore, it’s been valuable to have pursued it despite it not being able to meet my requirement at this time.

  6. Hi Chris,

    Really great articles regarding Power BI, thank you very much!

    In the Microsoft documentation is mentioned that there are limitation using dynamic M query parameters, e.g.:

    The feature is only supported for M based data sources. The following DirectQuery sources are not supported:
    – T-SQL based data sources: SQL Server, Azure SQL Database, Synapse SQL pools (aka Azure SQL Data Warehouse), and Synapse SQL OnDemand pools
    – Live connect data sources: Azure Analysis Services, SQL Server Analysis Services, Power BI Datasets.

    Which are actually the M based data sources?

    As described in the documentation, the data source should also support Direct Query mode in order for the dynamic parameters to work. The only option so far is to use Azure Data Explorer, but are there any alternatives? The Azure Databricks connection throws the same error like SQL Server, therefore I assume it is not an option.

    Thanks very much in advance for any insights or suggestions!

    Best regards,
    Tina

  7. This is really great option to dynamically select or filter the source content. I am using Azure Synapse and I know it is not supported over synapse. Will it support data present Power BI Dataflow using its Direct Query option?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: