Filtering SharePoint Lists in Power Apps: Practical Examples and Patterns

The Power Apps Filter function is your go-to tool for retrieving a subset of records from a SharePoint list based on one or more logical conditions. When you build canvas apps connected to SharePoint, you frequently need to show only the data that matters—like assets assigned to a specific person, items above a price threshold, or tasks due within a date range.

This article walks through realistic filter patterns using a custom Asset Tracking scenario. Each example covers a different SharePoint column type and includes delegation notes so you know whether the query will scale beyond the default data row limit (500 or 2000 records, depending on your environment).

The Scenario: Managing IT Assets

Imagine a SharePoint list called AssetTracking that your organization uses to track hardware. The list contains these columns:

A second list called Categories holds the possible lookup values: Laptop, Monitor, Printer, Peripheral.

Filtering Text Columns

Exact Match

To find all assets located in a specific building, use the equals operator:

Filter(AssetTracking, Location = "Building A – Floor 3")

This condition delegates well. If the Location column contains more than one word, enclose the entire string in double quotes.

Starts With

To retrieve assets where the location name starts with “Building A”, use StartsWith:

Filter(AssetTracking, StartsWith(Location, "Building A"))

StartsWith delegates when used with a SharePoint text column. It is case-insensitive by default.

Is Blank

To find assets that have no location recorded:

Filter(AssetTracking, IsBlank(Location))

Be aware that checking for blank values may not delegate on all SharePoint connections. For large lists, consider using a separate “NoLocation” flag or test thoroughly.

Filtering Number Columns

Greater Than / Less Than

To show only assets that cost more than $1,000:

Filter(AssetTracking, Cost > 1000)

To find assets costing $750 or less:

Filter(AssetTracking, Cost <= 750)

Both > and <= delegate for SharePoint number columns.

Between Two Values

To get assets with a cost between $500 and $1,500:

Filter(AssetTracking, Cost >= 500 And Cost <= 1500)

The And operator delegates when both sides are delegable individually.

Not Equal

To exclude assets that cost exactly $0 (e.g., uncategorized items):

Filter(AssetTracking, Cost <> 0)

<> delegates for number columns.

Filtering Date Columns

Date Equals Today

To see assets purchased today:

Filter(AssetTracking, PurchaseDate = Today())

Today() returns a date-only value, so it aligns with the date part of a Date/Time column.

Date Equals a Specific Date

To find assets purchased on March 15, 2025:

Filter(AssetTracking, PurchaseDate = Date(2025, 3, 15))

Avoid string conversion; the Date(year, month, day) function is delegable and safer.

Date Between Two Values

To retrieve assets purchased within the first quarter of 2025:

Filter(AssetTracking, PurchaseDate >= Date(2025, 1, 1) And PurchaseDate <= Date(2025, 3, 31))

Again, both sides of And delegate.

Date Range Contains Another Date

If you need to check whether a record’s start-to-end date range contains a specific date (e.g., warranty covers a certain day), use two comparisons:

Filter(AssetTracking, WarrantyExpiry >= Date(2025, 6, 1) And WarrantyExpiry <= Date(2025, 6, 30))

This pattern works for any pair of date columns.

Filtering Yes/No Columns

To display only critical assets:

Filter(AssetTracking, IsCritical = true)

For non-critical assets:

Filter(AssetTracking, IsCritical = false)

Yes/No columns are stored as Boolean values. Use the keywords true and false (case-insensitive). These conditions delegate.

Filtering Choice Columns

To show only assets with a status of “Active”:

Filter(AssetTracking, Status.Value = "Active")

Choice columns in SharePoint store an object with a .Value property. Always access .Value for delegation. Never compare directly with Status = "Active" because that would compare against the full object.

To find assets that are not “Active”:

Filter(AssetTracking, Status.Value <> "Active")

The = and <> operators delegate for choice columns when combined with .Value.

Filtering Lookup Columns

Using the Lookup Value

To find all assets in the “Laptop” category:

Filter(AssetTracking, Category.Value = "Laptop")

The .Value property returns the display name of the looked-up item.

Using the Lookup ID

To filter by the internal ID of the looked-up item (for example, if you have the ID from another source):

Filter(AssetTracking, Category.ID = 2)

Both patterns delegate, but filtering by ID is slightly more performant. Use .Value when you want to match based on a user-readable string, and .ID when you have the numeric identifier.

Filtering Person Columns

Person Equals Current User

To show assets assigned to the signed-in user:

Filter(AssetTracking, AssignedTo.Email = User().Email)

The User() function returns details of the logged-on user. Comparing email addresses is reliable and delegates.

Person Equals a Specific User

To filter by a specific person (e.g., a manager’s email stored in a variable):

Filter(AssetTracking, AssignedTo.Email = varManagerEmail)

You can also compare by AssignedTo.DisplayName or AssignedTo.Claims, but the Email property is usually the best choice for delegation.

Combining Multiple Conditions

You can chain conditions with And (or &&) and Or (or ||).

AND Example

Show active, critical assets assigned to the current user:

Filter(AssetTracking, Status.Value = "Active" && IsCritical = true && AssignedTo.Email = User().Email)

OR Example

Show assets that are either under repair or retired:

Filter(AssetTracking, Status.Value = "Under Repair" || Status.Value = "Retired")

When mixing And and Or, use parentheses to clarify precedence:

Filter(AssetTracking, (Status.Value = "Active" || Status.Value = "Under Repair") && Cost > 500)

All the examples above delegate as long as each individual condition delegates.

Delegation Considerations

The Filter function delegates to SharePoint most commonly used operators for text, number, date, Yes/No, choice, lookup, and person columns. The following operators typically delegate:

• =, <>, <, >, <=, >=
• And, Or (with delegable subexpressions)
• StartsWith
• IsBlank() (may not delegate on some connectors; test on your list before relying on it)
• Choice/Lookup column Value and ID

Operators that do not delegate include:

• Not (use <> instead)
• EndsWith
• In
• Most functions applied to the result (e.g., First, Last)
• IsBlank on text columns paired with an Or condition in complex ways

Whenever a non-delegable condition is used, Power Apps retrieves only the first 500 records (or 2000, depending on the environment) and applies the filter client-side. This leads to incomplete results for large lists.

Common Mistakes and Troubleshooting

• Forgetting .Value for Choice columns – Always use Status.Value = "Active", not Status = "Active".
• Using Text() on date columns inside Filter – Text() is not delegable. Compare dates with Date() or Today().
• Assuming Today() includes time – Today() is date-only. If your column includes time, use DateValue() to extract the date, but that may not delegate. Better: store dates as Date only in SharePoint.
• Not handling blank dates – Use Or with IsBlank if blanks are valid, but be aware of delegation limits.
• Mixing data types in comparisons – Comparing a text column to a number will fail. Ensure types match.
• Hard-coding IDs – Lookup IDs can change if the source list is repopulated. Prefer filtering by .Value when the display name is stable.

Troubleshooting steps:

1. Add a Label to your app and set its Text property to CountRows(YourFilteredTable). This quickly shows how many records are returned.
2. Use the Monitor tool to see the actual query sent to SharePoint.
3. Reduce the number of columns returned by using ShowColumns() if you only need a few fields.
4. For non-delegable scenarios, consider using ClearCollect() with a delegable outer filter to pull a manageable subset into a local collection. The data row limit (500 by default, up to 2,000 in app settings) applies to this fetch, so pair it with a delegable condition to keep the collection small and fast.

Final Recommendation

The Filter function is the foundation for retrieving data from SharePoint in Power Apps. By choosing delegable operators and understanding the limitations of choice and person columns, you can build apps that perform well even with tens of thousands of records.

Best practices summary:

• Always favor delegable operators (=, <>, <, >, <=, >=, StartsWith, And, Or).
• Use .Value for choice columns and .Email for person columns.
• Keep date comparisons simple using Today() and Date().
• Combine conditions only with And / Or and ensure each subcondition delegates.
• Test with a representative dataset (including row counts above the default limit) to verify delegation.

When you encounter a filter requirement that can’t be made fully delegable, consider these alternatives:

• Pre-filter the data in a SharePoint view and connect Power Apps to that view.
• Use Power Automate to run server-side queries and return results.
• Use Search function for simple text lookups across multiple columns (delegates when used with SharePoint).

By following these patterns, you’ll write efficient, maintainable formulas that scale with your data.

References

• Original source article for this topic: 23 Power Apps Filter Function Examples For SharePoint by Matthew Devaney.
• Microsoft documentation for the Filter function: Filter function in Power Apps (learn.microsoft.com).
• Delegation cheat sheet: SharePoint delegation cheat sheet for Power Apps (external resource).