Power Apps Filter Gallery With Multiple Dropdowns

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/power-apps-filter-gallery-with-multiple-dropdowns/. Author: Matthew Devaney.

Power Apps Filter Gallery With Multiple Dropdowns Posted by - Matthew Devaney on - September 20, 2020 22 Comments

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: TimeOffDate Employee TimeOffType Status 9/1/2020 Kelly Smith Personal Submitted 9/4/2020 Kelly Smith Vacation Approved 9/5/2020 Kelly Smith Vacation Approved 9/8/2020 Jennifer Houston Vacation Rejected 9/12/2020 Sarah Green Personal Submitted 9/13/2020 Jennifer Houston Vacation Approved 9/15/2020 Sarah Green Personal Rejected 9/18/2020 Kelly Smith Personal Rejected 9/19/2020 Sarah Green Sick Approved 9/21/2020 Jennifer Houston Personal Submitted

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" ] Code language: JSON / JSON with Comments ( json ) 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" ] Code language: JSON / JSON with Comments ( json )

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 " ) Code language: CSS ( css )

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 ) Code language: JavaScript ( javascript )

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 Did You Enjoy This Article? 😺

Subscribe to get new Copilot Studio articles sent to your inbox each week for FREE Enter your email address Sign Me Up 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. FILTER function Gallery Control Matthew Devaney M365 Copilot Power Apps

How To Create Copilot Custom UI Widgets In Power Apps