Filtering data table or gallery using many dropdowns easily

[SGobbiHeredia]

Hi everyone,

I already know how to filter a table or gallery using more than one dropdown, and adding to those an "Select All" option for each field with a dropdown. I do this by using If clauses. However, when the number of dropdowns increases, the amount of required ifs skyrockets (If I'm not mistaken, you need 2 to the power of "N" if clauses, where "N" is the number of dropdowns).

If I have a table with 10 or more columns that I would like to filter using a dropdown for each one (like you would in a spreadsheet, for example) is there a way to do this without having to write hundreds of ifs clauses, which is highly inefficient?

Thanks in advance to anyone that can help!

[TesDA]

Sure, use the or/ and logic: For Or use || for And use && This would look like: If(textbox.value = "Yes" || textbox.value = "Maybe", result true, result false)

If you need to be more selective you can add additional parathesis: if((text.value = "a" || text.value = "b") && dropdown.SelectedText.Result = "Yes" ,  result true, result false)

More on logic || &&:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-logicals

[SGobbiHeredia]

Hi TesDA, thanks for the quick reply,

To better examplify my situation, imagine you have a table with two rows: Street Name and City. You add two dropdowns to filter the table using these two fields. Each one of them has many different entries and many are repeated. So the Items attribute in the Dropdown is not set directly to the original list column, but to a collection, where you use the distinct funcion to get all the values and then you add an "Select All" option to this collection.

Now, you want to filter the table or gallery in your app using these two dropdowns, but you have 4 possible combinations:

1. Both dropdowns are set to "Select All" 

2. The city dropdown is set to "Select All" and the other one to an specific value

3. The Street Name dropdown is set to "Select All" and the other one to an specific city

4. Both dropdowns are set to specific values.

For each case I'm already using logical operators. Example:

Filter(DataSource;
       Ifs(ddCity.Selected.Value = "Select All" && ddStreet.Selected.Value = "Select All";
          DataSource;
       ddCity.Selected.Value = City.Name && ddStreet.Selected.Value = Street.Name;
          City.Name = ddCity.Selected.Value && Street.Name = ddStreet.Value;

       ...

Technically you only have one If clause, but with many conditions. The condition in the If clause is to filter the DataSource if the case 1 is happens, the second If covers case 4, and you get the idea, you would need to write the fours conditions. This is only for two drop downs, and the number of clauses increases exponentially with each dropdown you add, and it becomes a HUGE pain to do all those clauses. I wanted to know if there's a way to do that more efficiently.

[v-yutliu-msft · Accepted solution]

Hi @SGobbiHeredia ,

Do you want to use less if statement to make your app work more  efficiently?

If so, actually for 2 drop downs, you do not use 4 if statements.

One drop down only need one if statement.

For example, you could modify the formula that you provided like this:

Filter(DataSource;
       If(ddCity.Selected.Value = "Select All" ;
           true;
           ddCity.Selected.Value = City.Name
          );
       If(ddStreet.Selected.Value = "Select All" ;
           true;
           ddStreet.Selected.Value =Street.Name
          )
)

If ddCity.Selected.Value = "Select All", then you do not need to filter based on any rule, so you could use "true" to represent.

If ddCity.Selected.Value != "Select All", then you need to filter based on this drop down.

To sum up, you just need to use N if statements for N drop downs.

Just like this:

Filter(DataSource;
       If(ddCity.Selected.Value = "Select All" ;
           true;
           ddCity.Selected.Value = City.Name
          );
       If(ddStreet.Selected.Value = "Select All" ;
           true;
           ddStreet.Selected.Value =Street.Name
          );
       If(dropdown3.Selected.Value = "Select All" ;
           true;
           dropdown3.Selected.Value =....
          );
       If(dropdown4.Selected.Value = "Select All" ;
           true;
           dropdown4.Selected.Value =....
          );
.....
)

Best regards,