Forum Discussion
Dynamic M query Parameter Error
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?
- Anonymous5 years ago
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.
3 Replies
- AnonymousNot applicable
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.- AlexisOlsonSuper User
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?
- AnonymousNot applicable
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