Forum Discussion

niko8011's avatar
niko8011
New Member
5 years ago
Solved

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?

  • Anonymous's avatar
    Anonymous
    5 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

  • Anonymous's avatar
    Anonymous
    Not 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.

    • AlexisOlson's avatar
      AlexisOlson
      Super 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?

  • Anonymous's avatar
    Anonymous
    Not 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