parameter

[danno]

I'm trying to set up a parameterised directquery to an Azure SQL database (for D365 finance and operations), but when I create the source query to bring a list of values to feed into the parameter query, PowerBI says that the query contains steps not supported in a live connection.  The step that causes the issue is the Convert to List step which createas the list of values to use in the parameter query.  Surely such a simple step shouldn't be excluded from directquery?  Are parameterised queries possible with Directquery using a list? 

[v-yuezhe-msft]

@danno,

What type of parameter do you define in Power BI Desktop? Take table name parameter for example, in your scenario, all the tables including the query that providing list of values  that you want to import into Power BI Desktop should come from a single database.



In my scenario, par1 and Table_1 come from a single database, and we also need to disable "Enable load" option for the list query. In this case, the error should go away.

Another option is to set static values for the parameter instead of using list to provide parameter values.



Regards,
Lydia

[danno]

Thanks Lydia for getting back to me so quickly, I have disabled the load for the list and the error goes away on that step.  However when I want to use the parameter as a filter on another query, it flags the same issue, Formula.Firewall: Query 'Filtered Vendors' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.  I have modified the query to include the parameter 'VendorSelector' [Query="SELECT * FROM PurchaseCube_Vendors#(lf)WHERE VENDORSNAME = "&VendorSelector]

I need to be able to use a query as the list input as there are too many values to manage as a built-in list in PowerBI. 

[v-yuezhe-msft]

---

@danno wrote:

Formula.Firewall: Query 'Filtered Vendors' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.  I have modified the query to include the parameter 'VendorSelector' [Query="SELECT * FROM PurchaseCube_Vendors#(lf)WHERE VENDORSNAME = "&VendorSelector]

I need to be able to use a query as the list input as there are too many values to manage as a built-in list in PowerBI. 

---

@danno,

Please check the following blog to solve the above issue.

https://blog.crossjoin.co.uk/2017/06/26/data-privacy-settings-in-power-bipower-query-part-3-the-form...

Regards,
Lydia

[v-yuezhe-msft]

@danno,

Could you please describe more details about "when I want to use the parameter as a filter on another query"?

Regards,
Lydia

[danno]

so the VendorSelector is a parameter, it is populated from the list of vendors (a separate hidden query). I want to use the VendorSelector parameter in a separate query to bring back only the selected invoices for the selected vendor.  it's when I create the new query that the error is flagged up - it suggests I'm doing something incompatible with DirectQuery.