Power Apps Filter Gallery With Multiple Dropdowns

Filters are an important part of every Power Apps gallery design. They enable the user to select a few criteria and quickly show matching results on the screen. Knowing how to filter in a delegation-friendly way is important because its the only way to check an entire SharePoint list. By comparison the Search has will not return any results past the 2,000 row delegation limit.

In this article I will show you how to filter a Power Apps gallery with multiple dropdowns including an all option.

Table Of Contents:
Introduction: The Paid Time-Off App
Setup The SharePoint List
Creating a Gallery And Multiple Dropdowns
Adding Dropdown Values 
Filtering The Gallery By Multiple Dropdowns

Introduction: The Paid Time-Off App

The Paid Time-Off App is used by employees of a company to request a day-off from work. Managers can view the details of all requests made by their employees.

Setup The SharePoint List

Create a new SharePoint list called Paid Time Off and include the following columns:

• TimeOffDate (date only)
• TimeOffType (single-line text),
• Status (single-line text)
• Employee (single-line text)

Populate the SharePoint list with this sample data:

Creating A Gallery And Multiple Dropdowns

Open Power Apps Studio and create a new blank canvas app. Add a connection to the ‘Paid Time Off’ SharePoint list. Then insert a gallery onto the screen with ‘Paid Time Off’ as the datasource and display date, employee, time-off type and status in it.

Position a set of dropdowns to-the-right of the gallery. Give each dropdown a name: Type (drp_Type), Status (drp_Status) and Employee (drp_Employee). Once finished your app should look like the image shown below.

Adding Dropdown Values

Now that the app-design is complete we will focus on loading the dropdown menus with values. The Type dropdown has 4 options as shown below including a blank value in the top position.

There are only 3 possible Types so we can use this code in the Items property of the Type dropdown to populate it with values

[Blank(), "Personal", "Sick", "Vacation"]

The Status dropdown also has 4 choices.

Similarly, there are 3 possible Statuses included in the app-design so hardcode them into the Items property of Status dropdown as well.

[Blank(), "Submitted", "Approved", "Rejected"]

Currently the Employee dropdown shows 3 names but the company might add more people in the future.

Since the employees list will grow over time we can use this code in the Items property of the dropdown to show the unique names found in the Employee column of the SharePoint list. A clever reader of my blog named Ed Hansberry shared originally this method of adding a blank row to the top of a dropdown on Twitter.

Ungroup(
    Table(
        {MyTables: Table({Result: Blank()})},
        {MyTables: Distinct('Paid Time Off', Employee)
        }
    ),
    "MyTables"
)

Please note that because the employee dropdown code includes the DISTINCT function it is not-delegation friendly. If your SharePoint list will be greater than 2,000 rows I would suggest using this method to change the Employee data type to Person and use a People Picker ComboBox instead.

All of the dropdowns menus now have options when you click them.

Filtering The Gallery By Multiple Dropdowns

The final step is to filter the gallery by the current values selected in each dropdown as shown below.

We can accomplish this by using a single FILTER function in the Items property of the gallery. This coding pattern is delegation-friendly. When a dropdown has a value we filter the gallery based on the selection, otherwise, if the dropdown has a blank value we show all options for that column.

Filter(
    'Paid Time Off',
    drp_Type.Selected.Value=Blank() Or TimeOffType=drp_Type.Selected.Value,
    drp_Status.Selected.Value=Blank() Or Status=drp_Status.Selected.Value,
    drp_Employee.Selected.Value=Blank() Or Employee=drp_Employee.Selected.Value
)

Want To Read The Next Article In This Series?

My next article will show you how to add a Sharepoint-style filter menu with apply, clear and reset controls. Click the link to read Power Apps Filter Menu: Apply, Clear and Reset

Questions?

If you have any questions or feedback about Power Apps Filter Gallery With Multiple Dropdown please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.