cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Filtering data table or gallery using many dropdowns easily

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Highlighted
Helper V
Helper V

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

Highlighted

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.

Highlighted

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (8,934)