Dynamic M parameter as Databricks custom SQL schema name

[ycavazin]

Hi,

Is there a way to set as a parameter the schema of a database that lies in a list with all possible schemas and let the user select which one to see in the visual?

I'm having problems on making it work as dynamic parameter.

Steps that I made:

• I have a list called lschemas in PowerQuery that has some schemas in my databricks database.
• I created a PowerQuery parameter called pschemas as
  • Type Text
  • Suggested Values from a Query making reference to the lschemas list
  • Current Value as one of the values available there
• I created a custom query (called fact_data) using the databricks connector with Direct Query mode and edited it in advanced mode to use the pschemas as parameter to my query. One of the fields output in this query is the schema name (same as from the parameter).
• The query works fine with the Current Value as set in the parameter
• Applied the changes succesfully
• In the DAX model, I select the fact_data table schema and the field schema and bind it to the pschema parameter
• I created a table visual with some columns from the fact_data
• I created a filter from the lschemas list and tried to use it on the fact_data, but then it doesn't work.

Final goal is to let the user select the schema he wants to see, which is dynamic and more are added every week.

[Anonymous]

Hi @ycavazin 

In order to better help you, I need to check with you about your needs. Are you trying to display different tables through the slicer? Can you show some key steps that you made in screenshots?

Here's what I've suggested so far.

Make sure the parameters are referenced correctly in the list. From your description, the settings seem to be correct, but it's worth double-checking that they are. For more information, you can read the related documentation link: Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.