Forum Discussion

Wresen's avatar
Wresen
Post Patron
1 year ago
Solved

Select all parameter

Hi and thanks for reading this, I am new with parameters and i have tried my way trough posts online. I have parameters working for singel and multi select. I have a year-list with paramaters in t...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    You're close.  Remember that you can represent a single value as a list too.  That way you don't need to cover multiple scenarios. 

     

     

     

    let
    Source = {"2024","2025"},
    RList = if Value.Is(Source, List.Type) then Source else {Source}
in
    Text.Combine(RList,",")

     

     

     

    I have set the select all value to 12 in the table (Pbi desktop view)

     Not recommended.  The "All"  scenario is automatically included if you use a regular filter.

lbendlin's avatar
lbendlin
Super User
1 year ago

you are missing the curly brackets.

 

else {test}
Wresen's avatar
Wresen
Post Patron
1 year ago

lbendlin 
i really appriciate your help and i hate to argue with you 🙂
This is how it looks,

let
SelectID = if Value.Is(test,List.Type) then test else {test},
Res = "'" & Text.Combine(test,"','")&"'" ,

Source = Value.NativeQuery(Snowflake.Databa ****** and so on

in (" & Res & ") ", null, [EnableFolding=true])
in
Source

and i get the error :
Expression.Error: We cannot convert the value "2025" to type List.
Details:
Value=2025
Type=[Type]

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Needs to be a text value, not a number.

     

    let
    Source = "2024",
    RList = if Value.Is(Source, List.Type) then Source else {Source}
    in
    Text.Combine(RList,",")

    • Wresen's avatar
      Wresen
      Post Patron
      1 year ago

      Thx lbendlin  
      i get it to run now with and work with multiple choices but when i click in "Select All" the SQL querry does not get any value to the variable to "in ('"& Res &"')" (or rather it gets the value that is set in the Select all box)

      let
      SelectID = if Value.Is(test,List.Type) then test else {test},
      Res = Text.Combine(SelectID,"','") ,
      ******

      in ('"& Res &"')

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

        I recommend against using "Select all". If you must, change your SQL to remove the WHERE clause in that case.