cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SGobbiHeredia
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

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
TesDA
Responsive Resident
Responsive Resident

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

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.

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors
Users online (2,897)