'bind to parameter' option is not showing even when I used DirectQuery to get data.

[gsrikar33]

Hi All,

I am trying to create parameters for Year and WeekOfYear in Power Bi report.

Though I can create a calendar table in the powerquery itself, I am using Directquery and fetching the values for 'Year' to supply it for my 'Year' parameter.

I used below code to get year data from MySQL database.

let
    Source = MySQL.Database(DBServer, Database, [ReturnSingleDatabase=true, Query="SELECT  distinct#(lf)        IFNULL(YEAR(t.createdAt), '-') AS 'Year'#(lf)FROM v_customers as c#(lf)LEFT OUTER JOIN v_trips AS t ON t.customerId = c.customerId AND (t.tripStatus='Confirmed' || t.tripStatus='Cancelled')#(lf)GROUP BY YEAR(t.createdAt), WEEKOFYEAR(t.createdAt)#(lf)ORDER BY YEAR(t.createdAt), WEEKOFYEAR(t.createdAt) ASC;"])
in
    Source

I am still not able to see 'Bind to Parameter' Option.

Please help!

[gsrikar33 · Accepted solution]

Thanks everyone for the assistance!
Checked this link.
MySQL is not mentioned in that link.
'Bind to parameter' option will appear only for 'Direct Query' storage mode only.

For MySQL, 'Direct Query' menthod can only be used with MariaDB's ODBC driver. But we will not be able to write queries with this driver. We can only browser the database and select tables.
Workaround to write queries with this driver is to create a view in the database and use it.

Upon further testing I could see that this Maria DB driver is not great and I couldnt develop atleast one report with DIrectQuery method using this.

Where as I could do direct query with SQL server database source. This works perfect though.

[gsrikar33]

Hello @Anonymous 

Thank you for the response.
I have been following all these limitations.

Does this work with only Microsoft Databases like SQL, Azure SQL and etc.,?

I am using MySQL and I dont see this option to bind parameters in any of my PBI reports.

[Anonymous]

HI @gsrikar33,

I'd like to suggest you check the consideration and limitations about this feature usage at first to confirm if your scenarios are meet to that list:

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng