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 (which has state as one its fields) based on value in this State List (parameter). Please note if there are no state codes in the state list then all rows (no filter) should be returned from this other table otherwise only those rows that match each state-code from the list of state-codes should be returned from this other table. I tried multipe options - native query (could not get the syntax for referencing parameters) and PowerQuery without any success. Please help!

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.

     

     

     

     

6 Replies

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