Delegation in Power Apps: Real Queries Explained

Updated 5 November 2026 · Standard formula, shown below

Delegation in Power Apps is the process of offloading data operations to the data source (like SharePoint, SQL Server, or Dataverse) instead of pulling all records to the device and filtering locally. When you write a formula like Filter(MyList, Status = "Active"), delegation determines whether the server does the filtering or whether Power Apps fetches every row and filters on your phone or browser. Understanding delegation is critical because non-delegable queries can hit the 500-record default data row limit, silently returning incomplete results.

What Is Delegation and Why It Matters

Power Apps is a low-code platform for building canvas apps that connect to various data sources. When your app requests data, there are two places the work can happen:

On the server (delegated): The query is translated into the source’s native language (e.g., SQL, OData) and executed there. Only the matching records are sent to the app.
On the client (non-delegated): Power Apps retrieves a chunk of data (up to the data row limit, typically 500 records) and then applies the filter or sort locally. This is slower and can miss records beyond that limit.

Delegation matters because it directly impacts performance, scalability, and correctness. For example, a gallery showing all tasks where Completed = false will behave differently depending on whether the data source supports delegation for that filter.

How Delegation Works Under the Hood

When you write a Power Apps formula, the app’s engine analyzes it and tries to translate it into an OData query (for SharePoint, SQL, Dataverse, etc.). If the translation succeeds, the query is delegated. If not, Power Apps falls back to a two-step process: it fetches the first 500 records (configurable up to 2000 via the app setting) and then applies the rest of the logic locally.

Key points about the mechanics:

Delegation is per-operator, not per-whole-formula. A formula like Filter(MyList, FirstName = "John" && LastName = "Doe") might delegate the FirstName part but not the LastName part if the latter uses a non-delegable function.
Sorting also delegates. Sort(MyList, Created) can be delegated if the data source supports it.
Aggregations like Sum, Average, Min, Max are delegable for numeric columns on supported sources, but CountRows is only delegable on Dataverse (and some connectors).

Which Functions and Operators Are Delegable

Not every function is delegable. Here’s a practical breakdown for the most common connectors:

SharePoint and SQL Server

Delegable: Filter, Sort, Search (on text columns), Sum, Average, Min, Max, First, FirstN.
Not delegable: CountRows (on SharePoint), Last, LastN, Concat, StartsWith (on SharePoint), Not operator, IsBlank, Today() in filters (on SharePoint), and any use of Text, Value, DateValue inside filters.

Dataverse (Common Data Service)

Dataverse is more generous: CountRows is delegable, StartsWith is delegable, and many functions that fail elsewhere work here.
Still not delegable: IsBlank (in some cases), Not, and any function that requires client-side evaluation like Text.

Real-World Query Examples

Let’s look at concrete examples to see the difference.

Example 1: Filter by exact match (delegable)

Filter(Orders, Status = "Shipped")

This delegates on SharePoint, SQL, and Dataverse because = is a supported operator.

Example 2: Filter by partial text (non-delegable on SharePoint)

Filter(Customers, StartsWith(FullName, "Jo"))

On SharePoint, StartsWith is not delegable, so this will only search the first 500 records. On Dataverse, it is delegable.

Example 3: Filter with a date (delegable on SQL, non-delegable on SharePoint)

Filter(Events, DateValue(StartDate) >= Today())

Using DateValue or Today() inside a filter makes it non-delegable on SharePoint. On SQL Server, you can compare a date column directly to Today() and it will delegate.

Example 4: Sort by a column (delegable on most)

Sort(Products, Price, Descending)

This delegates on all major connectors.

How to Choose a Delegable Data Source

If you’re designing a new app and delegation is a priority, consider these factors:

Dataverse is the most delegation-friendly, supporting a wide range of functions and aggregations.
SQL Server is also strong, but you need to avoid functions like StartsWith and use Like via Filter with Text functions? Actually, you can use Filter(Table, Column like "Jo%") but Power Apps doesn’t support like directly; you use StartsWith which is not delegable on SQL either. So stick to exact matches or use Search which is delegable on SQL.
SharePoint is the most limited. Avoid CountRows, StartsWith, and date functions in filters.
If you must use a non-delegable function, consider moving the logic to a flow or using a different data source.

Common Mistakes and How to Avoid Them

Here are the most frequent pitfalls developers hit:

Ignoring the 500-record limit: Even if you only have 1,000 records, a non-delegable query will only return the first 500. You might not notice until your data grows.
Using CountRows on SharePoint: It’s not delegable, so it will count only the first 500 records. Use CountIf with a delegable filter instead, or use a calculated column.
Assuming all operators are equal: ="John" is delegable, but In is not. Use = or Search instead.
Forgetting to check the delegation warning: Power Apps shows a blue underline and a warning when a formula is not delegable. Always hover over it to see the reason.

Limits and Performance Trade-offs

Even when a query is delegable, there are limits. The data row limit (default 500, max 2000) applies to non-delegated queries. Delegated queries can return more, but the connector itself may have its own limits (e.g., SharePoint list view threshold is 5,000 items). Also, delegating a query doesn’t mean it’s instantaneous; a complex filter on a huge table can still be slow.

Performance tips:

Use Filter early to reduce data transfer.
Avoid Search on large text columns if possible; use Filter with StartsWith on Dataverse.
Consider adding indexes on columns you filter by.

Frequently Asked Questions

Why does my gallery show only 500 records?

Because your query is non-delegable. Power Apps fetches the first 500 records (the default data row limit) and then applies the filter locally. To fix this, change your formula to use delegable functions, or increase the data row limit in the app settings (up to 2000).

Can I make `CountRows` work on SharePoint?

No, CountRows is not delegable on SharePoint. Instead, use CountIf(MyList, Condition) where the condition is delegable, or create a calculated column in SharePoint that counts items. For SQL or Dataverse, CountRows is delegable.

What is the difference between `Filter` and `Search`?

Filter is for structured queries with operators like =, >, and And. Search is for text search across one or more columns and is delegable on SharePoint and SQL for text columns. Search is more flexible for partial matches but may be slower.

Does delegation affect performance on small datasets?

Not much. If you have fewer than 500 records, a non-delegable query will still return all records, so you might not notice a difference. But as your data grows, the impact becomes severe. It’s best to design for delegation from the start.

How do I know if a formula is delegable?

Power Apps shows a blue underline on the function name and a tooltip warning when a delegation issue is detected. You can also check the official documentation for a list of delegable functions per connector. The warning is your best real-time indicator.

Can I use `Today()` in a delegable filter?

On SQL Server and Dataverse, yes, you can compare a date column to Today() and it will delegate. On SharePoint, Today() is not delegable, so you need to use a different approach, like a calculated column that stores the date as a number.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.