Forum Discussion

diamond's avatar
diamond
Regular Visitor
8 years ago
Solved

Dynamic filtering of rows using a parameter

I have defined a parameter called State which may contain no values or may contain a list of state codes (one or more state codes) based on user selection. I want to filter rows from another table (w...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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.

     

     

     

     

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

The following 3 queries work:

 

StateList:

 

= {"State1","State3"}

 

States:

 

= #table(type table[State = text],{{"State1"},{"State2"},{"State3"},{"State4"}})

 

Result:

 

let
    SelectionTable = Table.FromColumns({StateList}),
    #"Merged Queries" = Table.NestedJoin(States,{"State"},SelectionTable,{"Column1"},"Selection",JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Selection"}),
    Custom1 = if List.IsEmpty(StateList) then States else #"Removed Columns"
in
    Custom1
  • diamond's avatar
    diamond
    Regular Visitor
    8 years ago

    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

     

     

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      8 years ago

      You 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?

      • diamond's avatar
        diamond
        Regular Visitor
        8 years ago

        Here is my "State" parameter (screenshot below). The 2nd screenshot refers to the "StateList" which is derived from a database query.

         

         

        Essentially the user should be able to choose one or more states or not choose a state from the state parameter list. This will drive  (cascading dropdown) the database query (clinics applicable for given state) for my 2nd parameter which will eventually drive my final query. If the user does not choose any parameter values for state and/or clinic then there is no filter applied to my final query and all rows are returned. I hope this helps explain my problem situation. Thanks again for your help.