Dynamic M Query Parameter with REST API in PowerBI

[pawlowski6132]

I understand how to configure Dynamic M Query Parameters with SQL and DirectQueries. 

I'd like to do the same with REST API.

I can build parameterized REST API queries in PowerBI but the default storage mode is Import so, binding to the parameter doesn't work.

Has anyone done this successfully or, is there an alternate approach?

The end product should allow the report user to choose an item from a filter on the page (prepopulated from a query/table), have the value of the filter selection bound to a parameter, which in turn the value of that parameter is passed to the REST API URL which is run and data is returned to the report.

This process works great with direct query SQL queries.

[lg01]

Have you found a solution to your issue? I am actually having to work through the same scenario and we have not made it work.  

[pawlowski6132]

No. Not looking good.

1. By default, the Web Connector ONLY works as IMPORT storage mode. Dynamic M parameter requries Direct Query Mode.

2. Building a Custom Connector for REST or ODATA is easily possible. However, to include Direct Query option for that Custom Connector is technically feasible, building the required ODBC wrapper is beyond our skillset capability. Requires C++ dev, etc.

3. Even with successful Custom Connector with Direct Query ability, I believe API Key web.contents config is requried to avoid having to use Personal Gateway. I'm a little unclear here but see this constraint discussed often in this area of Custom Connector - web discussions. 

[lbendlin]

Only works with Direct Query data sources. Your data source isn't.

Convert your report users to report developers.

[pawlowski6132]

Yes, that's what I've concluded also. Converting to developers is not an option in this situation.

Hoping for alternate solutions or ways around this constraint if available.