An HR team managing leave requests often starts with one SharePoint list and a simple Canvas app. At first, everyone can scan the request gallery easily. Then the list grows to hundreds of records, managers want only pending requests, and employees need to find leave entries from their department.

I recently built this type of HR leave management app for a 200-employee organization. A few well-planned filters changed a crowded request screen into a clean working tool. The key was using a Power Apps dropdown control to limit records without making the app difficult to maintain.

This step-by-step Power Apps tutorial shows how to filter dropdown values and gallery records using a SharePoint-based Leave Requests list.

What Does a Dropdown Filter Do?

A Dropdown is an input control that lets a user select one value from a defined list. In a Canvas app, you can use that selected value to filter another control, such as a Gallery, form, table, or another dropdown.

For this example, we will use a SharePoint list called Leave Requests. The list stores employee leave requests and contains these columns:

The goal is simple. A manager selects “IT” in a Department dropdown, and the Gallery shows only leave requests from IT. The manager can then select “Pending” in another dropdown to focus on records that still need action.

This approach works especially well in a Canvas app, where you control every screen, formula, and user interaction. A Model-driven app also supports filtering, but Canvas apps give you far more control over the layout and filtering experience.

Before you build the app, make sure the SharePoint list has clean column names and consistent values. If the list setup needs work, review this guide on creating and managing a SharePoint list.

Pro Tip: I always create the SharePoint choices first instead of manually typing department names into Power Apps. When the business adds a department later, the dropdown updates from the list settings instead of forcing an app change.

Power Apps Dropdown Control Setup

Start by creating a blank Canvas app or opening an existing HR leave management app.

Add your SharePoint list as a data source:

1. Select Data from the left navigation.
2. Select Add data.
3. Search for SharePoint.
4. Choose the correct SharePoint site.
5. Select the Leave Requests list.
6. Select Connect.

Now add a vertical gallery:

1. Select Insert from the top menu.
2. Select Gallery.
3. Choose Vertical gallery.
4. Rename the gallery to galLeaveRequests.
5. Set its Items property to:

'Leave Requests'

The gallery now displays every record from the SharePoint list. Add labels inside the gallery template and connect them to the record fields:

ThisItem.Title

ThisItem.Department.Value

ThisItem.Status.Value

Text(ThisItem.StartDate, "dd-mmm-yyyy") & " - " & Text(ThisItem.EndDate, "dd-mmm-yyyy")

A SharePoint Choice column returns a record, not plain text. That is why the formula uses .Value after Department, Leave Type, and Status.

If your gallery appears empty even though the list has records, check the data source, the app connection, and the gallery’s Items property. This guide on fixing no item to display in Power Apps preview can help you diagnose that issue.

Filter Power Apps Dropdown Control by Choice Column

Now let’s add the first filter. We will use the Department choice column to filter the Gallery.

Insert a Dropdown control above the Gallery and rename it to drpDepartment. Select the dropdown and set its Items property to:

Choices('Leave Requests'.Department)

The Choices function reads the available values from the Department choice column. In our list, the dropdown may display IT, HR, Finance, and Sales.

Next, select galLeaveRequests and replace its Items property with this formula:

Filter(
    'Leave Requests',
    Department.Value = drpDepartment.Selected.Value
)

This formula has three parts:

• Filter tells Power Apps to return matching records.
• 'Leave Requests' is the SharePoint list that contains the data.
• Department.Value = drpDepartment.Selected.Value compares each record’s Department choice value against the value selected in the dropdown.

When the manager selects IT, Power Apps checks each leave request. It only returns records where the Department equals IT.

A common mistake is writing this formula instead:

Filter(
    'Leave Requests',
    Department = drpDepartment.Selected.Value
)

That formula compares a SharePoint choice record with a text value. Power Apps treats those as different data types, so you may receive an error. Use .Value to compare the actual text stored inside the choice record.

If you need help with this type of issue, read how to resolve the expected record value error in Power Apps.

Add an All Option to Power Apps Dropdown

The basic filter works, but it creates a user experience problem. What happens when a manager wants to view every department again?

You could ask them to clear the dropdown. That works, but it is not obvious to every user. I prefer adding an All Departments option at the top of the list.

Select the screen that contains the dropdown, such as scrLeaveDashboard. Set the screen’s OnVisible property to:

ClearCollect(
    colDepartments,
    {Value: "All Departments"},
    Choices([@'Leave Requests'].Department)
)

This formula creates a local collection named colDepartments.

• ClearCollect clears the old collection and adds fresh records.
• {Value: "All Departments"} creates the first custom dropdown option.
• Choices([@'Leave Requests'].Department) adds the SharePoint Department choices after it.

Now update the drpDepartment Items property:

colDepartments

Set the dropdown’s Default property to:

"All Departments"

Finally, update the Gallery’s Items property:

Filter(
    'Leave Requests',
    drpDepartment.Selected.Value = "All Departments" ||
    Department.Value = drpDepartment.Selected.Value
)

The || operator means “or.” The formula returns every leave request when the user selects All Departments. Otherwise, it returns only the records that match the selected department.

Pro Tip: In my experience, custom values like “All Departments” should never match a real SharePoint choice. Pick a label that users understand, but do not create the same value in the actual Department column.

You can use the same approach when you build filters from a collection. For more collection patterns, see how to create a Power Apps collection using a SharePoint list.

Filter Power Apps Gallery With Multiple Dropdowns

Most business apps need more than one filter. In the HR leave app, the manager may need Department, Leave Type, and Status dropdowns.

Add two more Dropdown controls:

• drpLeaveType
• drpStatus

Set the Items property of drpLeaveType:

ClearCollect(
    colLeaveTypes,
    {Value: "All Leave Types"},
    Choices([@'Leave Requests'].'Leave Type')
)

Because this is a formula that builds a collection, place it in the screen’s OnVisible property after the Department collection formula. Then set drpLeaveType.Items to:

colLeaveTypes

Set the Items property of drpStatus in the same way:

ClearCollect(
    colStatuses,
    {Value: "All Statuses"},
    Choices([@'Leave Requests'].Status)
)

Then set drpStatus.Items to:

colStatuses

Your complete screen OnVisible formula can look like this:

ClearCollect(
    colDepartments,
    {Value: "All Departments"},
    Choices([@'Leave Requests'].Department)
);
ClearCollect(
    colLeaveTypes,
    {Value: "All Leave Types"},
    Choices([@'Leave Requests'].'Leave Type')
);
ClearCollect(
    colStatuses,
    {Value: "All Statuses"},
    Choices([@'Leave Requests'].Status)
)

Now update the Items property of galLeaveRequests:

Filter(
    'Leave Requests',
    drpDepartment.Selected.Value = "All Departments" ||
    Department.Value = drpDepartment.Selected.Value,
    
    drpLeaveType.Selected.Value = "All Leave Types" ||
    'Leave Type'.Value = drpLeaveType.Selected.Value,
    
    drpStatus.Selected.Value = "All Statuses" ||
    Status.Value = drpStatus.Selected.Value
)

Power Apps treats the comma-separated conditions inside Filter as “and” conditions. That means:

• Department must match, unless All Departments is selected.
• Leave Type must match, unless All Leave Types is selected.
• Status must match, unless All Statuses is selected.

For example, a manager could choose:

• Department: IT
• Leave Type: Sick
• Status: Pending

The Gallery would then show only pending sick leave requests from the IT department.

This formula remains readable because each filter condition follows the same structure. Keep that pattern as you add more controls. A future developer will understand the app much faster.

If you want an alternative filter interface for short option lists, you can also learn how to filter a Power Apps gallery by radio button.

Filter a Power Apps Dropdown Control With Distinct Values

Sometimes you do not want to show SharePoint choice values. You may need a dropdown that displays unique values from a text column instead.

For example, suppose your Leave Requests list uses a text column named Employee Email. You want a manager to choose an employee and see that person’s leave requests.

Add a Dropdown named drpEmployee. Set its Items property to:

Sort(
    Distinct(
        'Leave Requests',
        'Employee Email'
    ),
    Value
)

The Distinct function returns unique values from the Employee Email column. The Sort function then arranges them alphabetically.

Depending on your Power Apps version, the output column from Distinct may be named Value. If Power Apps shows a different result column, select the dropdown and inspect the available fields in the formula bar.

Now add an All Employees option by building a collection:

ClearCollect(
    colEmployees,
    {Value: "All Employees"}
);
Collect(
    colEmployees,
    Sort(
        Distinct(
            'Leave Requests',
            'Employee Email'
        ),
        Value
    )
)

Set drpEmployee.Items to:

colEmployees

Add this condition to the Gallery filter:

drpEmployee.Selected.Value = "All Employees" ||
'Employee Email' = drpEmployee.Selected.Value

The full Gallery formula now filters by Department, Leave Type, Status, and Employee Email.

Be careful with Distinct on a large SharePoint list. It may trigger a delegation warning. Delegation means Power Apps sends the filtering work to the data source rather than downloading a limited number of records to the device. When a formula does not support delegation, Power Apps may only evaluate the first 500 or 2,000 records, based on your app settings.

For small HR lists, Distinct may work well. For larger lists, use a separate Employees list or Microsoft 365 users data source instead. A separate list gives you better control, cleaner values, and fewer performance problems.

Pro Tip: I avoid using a Distinct formula against a large transaction list for production apps. A dedicated reference list for departments, locations, or employees keeps dropdowns quicker and avoids unpredictable results after the list grows.

Filter Dropdown Values Based on Another Dropdown

A cascading dropdown displays values based on another selection. In the leave request app, you may want users to choose a Department first, then show only employees from that department.

For this design, create a separate SharePoint list named Employees with these columns:

• Title — employee display name
• Employee Email — single line of text
• Department — choice column
• IsActive — Yes/No column

Add an initial dropdown called drpDepartmentFilter. Set its Items property:

Choices([@Employees].Department)

Next, add a second dropdown named drpEmployeeFilter. Set its Items property:

SortByColumns(
    Filter(
        Employees,
        Department.Value = drpDepartmentFilter.Selected.Value,
        IsActive = true
    ),
    "Title"
)

This formula filters the Employees list first and then sorts the available employees by name. The user sees only active employees in the selected department.

To display the employee names correctly, set the drpEmployeeFilter Value property to:

"Title"

This setup is useful because it prevents invalid selections. A manager cannot select an IT employee after choosing Finance. It also makes forms faster to complete because users see fewer options.

For a detailed walkthrough of this pattern, read how to create a Power Apps cascading dropdown control.

Combine Search and Dropdown Filters

A dropdown works best when you combine it with a search box. The dropdown narrows the record set, while the search box finds a specific request quickly.

Add a Text input control and rename it txtSearchRequest. Set its HintText property to:

"Search leave request title"

Then update the Gallery Items formula:

Filter(
    'Leave Requests',
    
    drpDepartment.Selected.Value = "All Departments" ||
    Department.Value = drpDepartment.Selected.Value,
    
    drpStatus.Selected.Value = "All Statuses" ||
    Status.Value = drpStatus.Selected.Value,
    
    IsBlank(txtSearchRequest.Text) ||
    StartsWith(Title, txtSearchRequest.Text)
)

The StartsWith function checks whether the Title begins with the typed text. For example, if a manager enters “Need,” Power Apps can find a request titled “Need to go on a Vacation.”

I prefer StartsWith for SharePoint text searching because it often works better with delegation than more complex text searches. Keep the search field focused on one simple text column whenever possible.

You can also add a Clear Filters button. Set the button’s OnSelect property:

Reset(drpDepartment);
Reset(drpLeaveType);
Reset(drpStatus);
Reset(drpEmployee);
Reset(txtSearchRequest)

This lets the manager return to the default view with one click.

Save Filtered Leave Requests

Dropdown filtering often sits beside an edit form. A manager may filter pending requests, select one record in the Gallery, and then approve or reject it.

Set the Gallery’s OnSelect property:

Set(
    varSelectedLeaveRequest,
    ThisItem
);
Navigate(
    scrLeaveDetails,
    ScreenTransition.Fade
)

This formula stores the selected record in a variable named varSelectedLeaveRequest and opens a details screen.

Add an Edit form named frmLeaveRequest to scrLeaveDetails. Set its Item property:

varSelectedLeaveRequest

To approve a leave request with a button, set the button’s OnSelect property:

Patch(
    'Leave Requests',
    varSelectedLeaveRequest,
    {
        Status: {
            Value: "Approved"
        }
    }
);
Back()

The Patch function updates the selected SharePoint record instead of creating a new record. Since Status is a Choice column, the formula sends a record containing Value: "Approved".

You can then connect this process to a Power Automate approval flow if requests need manager, HR, and finance approval stages.

Things to Keep in Mind

• Choice column values: Use .Value when filtering SharePoint Choice columns. Comparing the full record to text causes type mismatch errors.
• Delegation limits: Test every dropdown filter with a large SharePoint list. A delegation warning can make the app ignore records beyond the configured data row limit.
• All option labels: Use values such as “All Departments” and “All Statuses” that do not exist in the actual SharePoint choices.
• Separate reference data: Use dedicated lists for employees, departments, or locations when dropdown options grow. Do not rely on Distinct against a large transaction list.
• Security rules: A gallery filter only controls what users see in the app. Apply proper SharePoint list permissions so users cannot access confidential leave data outside the app.
• Control names: Use clear names such as drpDepartment, drpStatus, and galLeaveRequests. Good names make formulas easier to debug and support.

Frequently Asked Questions

You may also like:

• Create a form in Power Apps
• Power Apps Combo Box With Office 365 Users
• Add an item to a SharePoint list from Power Apps
• Use the Power Apps display mode property
• Create multiple tabs in a Power Apps form
• Send approval requests through Outlook and Teams with Power Automate

A filtered Power Apps dropdown control helps users find relevant SharePoint records quickly while keeping HR leave screens clean and focused. Start with one dropdown, use predictable Filter formulas, and add cascading or multi-filter logic only when users need it. I hope you found this article helpful.

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.