How to pass multiple Value to a dynamic Variable in Dashboard underlying SQL

[Linglin]

select         {{user_defined_variable}} as my_var,

                   count(*) as cnt

from            my_table

where         {{user_defined_variable}} = {{value}}

for user_defined_variable, I use query based dropdown list to get a column_name I'd like in the selection. 

for user_defined_value, I can only use text to pass one value

Since the value selection is based on my selection of user_defined_variable, I can't use dropdown list to predefine value. I tried to nest variable in the query used for user_defined_variable. It doesn't work. 

I also tried {{user_defined_variable}} in ({{value}}), doesn't work as well. 

I wonder if anyone knows a solution. thanks!

[Linglin]

Thank you very much for your reply. I'm not sure if my databricks environment is MySQL. SET statement does't work for me. (I tried both dbx notebooks(SQL enviroment, I think it's spark sql) and in sql editor). In SQL editor, it shows PowerBI on the right corner and it supports dashboard rendering. 

For me {{}} works well as variable setter. It worksWHERE {{my_var}} = {{some_value}}/where {{my_var}} in ({{some_value}}) when I pass in one value for {{some_value}} in both statements, but returns error message when I try to return multiple values. I wonder in your statement, if it works for multiple values. 

[YuliyanBogdanov]

It seems to work, try and create all your variables as a Drop Down List, with single value allowed and only the filter i.e. msp in my example as a multiple values allowed, no quotations in the quotation drop down.

Let me know if that helped.