Skip to main content
Power Apps

Filter a Power Apps gallery with multiple dropdowns

By October 18, 2020August 23rd, 2022No Comments

In a Power Apps Canvas app, we want to filter items from a SharePoint list with multiple filter conditions.

We have a gallery that is connected to a SharePoint list. The SP list contains sites with metadata that tells for which company and which department the site is.

We want to filter the gallery items on company and on department. We want two filters.

Our end result will be a filterable gallery:

Creating the data source: A SharePoint list

Before creating a Power App, we need a data source for this Power App.

Our SharePoint list looks like this:

For the two columns that we want to filter on in the Power app gallery, we also add an “All” value to these choice columns. If we click on “All” in the filters, we want to show all items.

  • If we select “All” in the companies filter, we want to see all records for that company
  • If we select “All” in the department filter, we want to see all records for that department.
  • These two filters are linked, if we select “All” in both filters, we want to see everything. If we select “All” in one of the two and another in the other filter, we also want it to filter correctly.

Creating the Power App

Let’s create a Power App for this SP list.

Add a “List box” to the app for the first filter on “Company”:

We display all choice column values, sorted alphabetically.

Sort(Choices(Sites.Company), Value, Ascending)

Add a second “List box” for the “Department” filter:

Sort(Choices(Sites.Department), Value, Ascending)

Add the Gallery with filtering formula

Last step is to add a Gallery and configure the formula to use the selected values of the filter list boxes.

The “Company” and “Department” column are showed in the gallery items to easily test when our Power App is ready.

Filtering the gallery

Thanks to an alert visitor that contacted me, I updated the formula to the one below. The older “complex filtering” formula can still be found a bit lower in the article.

I recommend to use the easy way as it’s also delegable. Being delegable means that if you have large lists with over 2000 items, you don’t run into delegation issues.

Filter(
    Sites,
    lstbCompany.Selected.Value = "All" Or Company.Value = lstbCompany.Selected.Value,
    lstbDepartment.Selected.Value = "All" Or Department.Value = lstbDepartment.Selected.Value
)

The complex filtering way

A more complex way to achieve the same result can be found below. I don’t recommend it, because the query is not delegable. However, it might be useful to check how this complex formula is built to understand how the results from the easy formula are filtered.

If(
IsEmpty(lstbDepartment.SelectedItems) && IsEmpty(lstbCompany.SelectedItems), Sites, 
Filter(Filter(Sites, If(lstbCompany.Selected.Value="All", true, lstbCompany.Selected.Value in Company.Value)), If(lstbDepartment.Selected.Value="All", true, lstbDepartment.Selected.Value in Department.Value))
)

If the two IsEmpty conditions are true, we just show all values from the list.

If one of the two or both are not empty, we execute the green and yellow part of the formula. The yellow part is a nested Filter function in the green Filter function.

In every Filter, there is another If condition when the “All” value is selected.

Testing the app

Let’s do a test, for the “Company” filter, select “All”. For the “Department filter, select “HR”. As we have two “HR” sites and selected to show sites for all departments, we see two records:

Leave a Reply