Forum Discussion
Dynamic filtering of rows using a parameter
Thanks for the explanation. As far as I know, it is not possible to select multiple parameter values from the list.
This is also confirmed in this topic.Aren't you looking for a slicer visual with the option to select all items?
In that case you don't need a separate statelist, but you can just use the "State" column from your data.
Thank you for the quick response. Please note in my case StateList is a parameter which can be empty or have multiple values (list). Your solution works when StateList is a List. Can you please update the solution to work when StateList is a parameter?
I get the following error when StateList is a parameter -
Expression.Error: We cannot convert the value null to type List.
Details:
Value=
Type=Type
Please see the extract of my Powerquery below where State is the parameter and can be empty or have multiple values in it.
let2
SelectionTable = Table.FromColumns({State}),
Source = Sql.Databases("myazuredatasourcename"),
MyDB = Source{[Name="MyDB"]}[Data],
dbo_tblClinics = MyDB{[Schema="dbo",Item="tblClinics"]}[Data],
#"Merged Queries" = Table.NestedJoin(dbo_tblClinics,{"State"},SelectionTable,{"Column1"},"Selection",JoinKind.Inner),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Selection"}),
Custom1 = if List.IsEmpty(State) then dbo_tblClinics else #"Removed Columns"
in
Custom1
Community ChampionYou may try and adjust Custom1 step as follows:
Custom1 = if State is null then dbo_tblClinics else #"Removed Columns"
It is strange that you have a parameter that may have a list as a value: this is not something that can be defined via the User Interface.
This code:
{"State1","State2"} meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]
Looks like this in the User Interface:
How did you define parameter State?