Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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.
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.
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
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 & " )"
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]
you are missing the curly brackets.
else {test}
@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]
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,",")
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 &"')
I recommend against using "Select all". If you must, change your SQL to remove the WHERE clause in that case.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
35 | |
17 | |
12 | |
11 | |
9 |
User | Count |
---|---|
45 | |
27 | |
16 | |
14 | |
14 |