Dynamic M query Parameter Error

[niko8011]

Hi all,

I followed the following tutorial to create a report with dynamic parameters:
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

I created a new Datetime parameter (StartDate) whith a default value

I create a new table data with a list of dates (StartTableDate)

I updated the DirectQuery to a SQLServer database, inserting the parameter in the where condition:

let
Param = DateTime.ToText(StartDate, "dd/MM/yyyy"),

Query = " SELECT * FROM TABLETEST where 1=1 ",
QueryParam = Text.Combine({" AND LOADDATE >= CONVERT(datetime, '", Param, "', 103) "}),
FinalQuery = Text.Combine({Query, QueryParam}),

Source = Sql.Database("myserver", "dmydb", [Query=FinalQuery, MultiSubnetFailover=true])
in
Source

the query extracts the data correctly using the parameter's default value.

I configured the StartDateTable table column by setting the Bind to Parameter with my "StartDate"

I added a Table to my report visualization adding all columns of my DirectQuery (all records are displayed)

But when i add a Filter data for StartDateTable my Table  visual object cannot be displayed:

"Query(2, 14) M Parameter 'StartDate' is not defined in model or parameter type is not valid"

I don't understand what the problem is and what caused it, can you help me?

[Anonymous]

Hi @niko8011 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[Anonymous · Accepted solution]

Hi @niko8011 ,

According to the official document, SQL Server is not supported for Dynamic M query:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

Wow! I'm having the same issue and find it rather shocking that they'd roll out a query feature without support for SQL. What are these "M based data sources" that are supported then?