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 transformed view and in pbi desktip i have a table linked to this year-list with more values and i use this table for a slicer.

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

i have the SQL querry like this (in the end) : YEAR in ( "&SelectID&" )

My Code looks like
let
SelectID = if Type.Is(Value.Type(Parameter1),List.Type) then Text.Combine({"'", Text.Combine(Parameter1,"','"),"'"}) else Text.Combine({"'", Parameter1, "'"}) ,
ALLid = if List.Contains(Parameter1, "12") then SelectID = Text.Combine({"'", Text.Combine(Parameter1,"','"),"'"}) else end ,

The first row is working (SelectID) and what i am trying to do on ALLid is:
if "Select All" is used in the slicer it gets the value 12
and if Select all is used then all the other boxes are ticked and combine all the values (all the years) 
(my Thought is that when Select All is use all list values should be active just like if i click on then)

Is this even possbile or am i totaly wrong here.

/Thanks   

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.

9 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    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.

    • Wresen's avatar
      Wresen
      Post Patron
      1 year 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
        1 year 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 & " )"