Forum Discussion

Wresen's avatar
Wresen
Post Patron
2 years 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
    2 years 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.

Wresen's avatar
Wresen
Post Patron
2 years ago

Hi lbendlin  and thx so much for your help here,

i really dont understand how i can use the code in my senario.
i might have done a bad explination
My data file , its a direct Querry to snowflake where i ask for all years.
I have made a paramter list with all the years also in transformed view called parameter1
i have a table in PBI desktop view called "More Years" where i have listed the years and linked the colum to parameter1 and set the select all value to 12 (saw that you mention that it was not good)
if i open the SQL querry to snowflake where the years are shown with Advance editor it looks like this:
let
SelectID = if Type.Is(Value.Type(test),List.Type) then Text.Combine({"'", Text.Combine(test,"','"),"'"}) else Text.Combine({"'", test, "'"}) ,

Source = Value.NativeQuery(Snowflake.Databas ********** and soo on
Where  YEAR in ( "&SelectID&" )

i am not sure how i should apply your suggestion here 

/Thanks for any help



lbendlin's avatar
lbendlin
Super User
2 years ago
let
SelectID = if Value.Is(test,List.Type) then test else {test},
Res = "'" & Text.Combine(test,"','")&"'" ,

Source = Value.NativeQuery(Snowflake.Databas ********** and soo on
" ... Where  YEAR in ( " & Res & " )"
  • Wresen's avatar
    Wresen
    Post Patron
    2 years ago

    Thx again lbendlin . 

    When i see the code from you now it makes sense .
    i am affraird that i get an error that i am trying to understand :
    Expression.Error: We cannot convert the value "2024" to type List.
    Details:
    Value=2024
    Type=[Type]

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      you are missing the curly brackets.

       

      else {test}
      • Wresen's avatar
        Wresen
        Post Patron
        2 years 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]