Answered

Best way to filter a gallery on multiple criteria

(0) Share

Report

Posted on by Mattw112b

1,088

I have 5 dropdowns. Things like city, type, region, manager, willing to relocate... but basically 5 options, based on choice columns in SharePoint.

I want to filter a gallery based on any combinations of those 5 things.

So everything from all 5 of those have items selected, to no dropdowns have items selected to 2 of the dropdowns have items selected, etc.

Is the best way to do this with nested if's? That seems like a lot of nested if's.... switch doesn't seem to work with dropdown choices from SharePoint. Or maybe I'm wrong.

Any other way to do this so I don't have to do a 30+ layered nested if statement?

Thanks,

Terry

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (1)

Sort by

Joel CustomerEffective 3,224 on at

Like (1)

Report
Copy link

Link copied!
for your drop-down lists, create a collection with the items that should populate your drop down list, and add an "All" option. Like they do here: https://powerusers.microsoft.com/t5/Building-Power-Apps/All-option-in-DropDown-Filter/td-p/159991

Make "all" the default option for your drop downs

You can then use the following formula to filter your gallery so that it will return all items by default:
Spoiler (Highlight to read)
Filter(company_List,City=If(Dropdown1.Selected.Name = "All",City,Dropdown1.Selected.Name)
Filter(company_List,City=If(Dropdown1.Selected.Name = "All",City,Dropdown1.Selected.Name)
This basically says that if the dropdown = all, filter the gallery by itself.

You can then nest this filter for each drop-down condition

Filter(Filter(company_List,City=If(Dropdown1.Selected.Name = "All",City,Dropdown1.Selected.Name)),Office=(If(Dropdown2.Selected.Name="All",Office, Dropdown2.Selected.Name)))

the result is that is one or all of the filters have a selected value other than all or none of the filters have the "all" option, it will work.
Note that this is not delegable, so you can't use it for large data sets.

@Mattw112IG if this answers your question, please mark it a solution

Was this reply helpful? Yes No
Mattw112b 1,088 on at

Like (0)

Report
Copy link

Link copied!

Hi,
Thanks, that still puts me in the business of creating numerous nested if's right? No way around that? with 5 dropdowns I need like 32 nested ifs I think?

Thanks,
Terry

Was this reply helpful? Yes No
Joel CustomerEffective 3,224 on at

Like (0)

Report
Copy link

Link copied!

No with my approach you need one main if with 4 nested ifs. Because each of is self contained. You are basically filtering and filter of a filter, but it works in any combination

Was this reply helpful? Yes No
Joel CustomerEffective 3,224 on at

Like (0)

Report
Copy link

Link copied!

I have another way of doing it , will update after I test it out

Was this reply helpful? Yes No
Verified answer

Joel CustomerEffective 3,224 on at

Like (8)

Report
Copy link

Link copied!
@Mattw112IG here is how to do it without delegation issues:

Filter( company_List, Dropdown1.Selected.Name = "All" || State = Dropdown1.Selected.Name, Dropdown2.Selected.Name = "All" || City = Dropdown2.Selected.Name, Dropdown3.Selected.Name = "All" || Office = Dropdown3.Selected.Name, Dropdown4.Selected.Name = "All" || Industry = Dropdown4.Selected.Name, Dropdown5.Selected.Name = "All" || Market = Dropdown5.Selected.Name )
The important thing to make this work is adding the "All" option in each drop down. My drop-down has one column called Name, so replace the Name reference with whatever your column name is in the drop down

You don't need to think of every possible filter combination because if the filter is left to the "all" option it will not affect the results.

@Mattw112IG if this answers your question please mark as a solution

Was this reply helpful? Yes No
Mattw112b 1,088 on at

Like (0)

Report
Copy link

Link copied!

I'll give it a try.
Thanks,
Terry

Was this reply helpful? Yes No
Mattw112b 1,088 on at

Like (2)

Report
Copy link

Link copied!
Awesome, that seems to work. I had to modify slightly with my code, but it seems to be working. I need to run a few more tests with more data though and make sure I can edit the items and have that reflected immediately though. I assume I need to refresh the collections after each editform submit to reflect that. Do I need to redo the clearCollects too?

Here's how I modified FYI
Filter( Sponsor_1, Dropdown1.Selected.Result = "All" || 'Regional Manager'.DisplayName = Dropdown1.Selected.Result, Dropdown1_1.Selected.Result = "All" || Status.Value = Dropdown1_1.Selected.Result, Dropdown1_2.Selected.Result = "All" || 'Role to Sponsor For'.Value = Dropdown1_2.Selected.Result, Dropdown1_3.Selected.Result = "All" || Office = Dropdown1_3.Selected.Result, Dropdown1_4.Selected.Result = "All" || 'Open to Relocation'.Value = Dropdown1_4.Selected.Result )

So pretty much the same, but just in case someone comes along later...

Thanks,
Terry

Was this reply helpful? Yes No
Joel CustomerEffective 3,224 on at

Like (0)

Report
Copy link

Link copied!

you only need to redo the clear collects if the items in your drop downs change

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

thanks for helping!!!
😁

Was this reply helpful? Yes No
Hung_Pham 11 on at

Like (0)

Report
Copy link

Link copied!

Thank you!!!

Was this reply helpful? Yes No