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…
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.
This isn’t in Excel, it’s in Power BI
Finally some love for DAX 😀
Hi Chris W!
Thanks for the content.
Is there a way to get date picker to work with a dynamic query parameter?
I see it’s a limitation in the doc but is there a work around?
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.
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
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?
Did you find option were you able to connect from direct query dataflow to parameter
It will not be possible, at least at the moment
Not able to connect to dataflow, please let me know if its possible for same
Not seeing the point given it’s not supported by any common data sources i.e. SQL