Select all parameter

[Wresen]

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   

[lbendlin · Accepted solution]

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]

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]

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]

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]

you are missing the curly brackets.

else {test}

[Wresen]

@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]

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]

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]

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