Delegation and Its Limits

Delegation is the mechanism by which Power Apps translates a Power Fx formula into a query that the underlying data source executes itself — a SQL WHERE clause, an OData $filter parameter, or a Dataverse FetchXML query — instead of pulling every row into the app's local memory and filtering there. This distinction matters enormously at scale: a delegable formula like Filter(Orders, Status = "Shipped") runs against the full table on the server regardless of size, while a non-delegable formula silently processes only the first page of records (governed by the app's Data row limit setting, which defaults to 2,000 and can be raised to a documented maximum), producing results that look correct in testing but are quietly wrong in production once the table exceeds that threshold.

Delegation support varies significantly by data source and by function: Dataverse and SQL Server support the widest range, including Filter, LookUp, Sort/SortByColumns, Search, and aggregate functions like Sum, Average, Min, Max, and CountRows, combined with And, Or, and Not; SharePoint supports a narrower subset, notably struggling with certain Choice and Lookup column comparisons and with some string functions when combined in complex ways; Excel supports essentially none. Even within a delegable data source, specific function combinations can break delegation — for example, delegable individually, Filter(Orders, Status = "Open" && Left(CustomerName, 3) = "ACM") may still fail to fully delegate depending on the connector's support for the Left function in a filter predicate, which is why the Power Apps Studio formula bar's delegation warning is the authoritative source of truth, not general rules of thumb.

When a formula can't be delegated, several practical fixes exist depending on the data source: for Dataverse, moving the logic into a server-side calculated or rollup column shifts the computation to a place where it can be indexed and filtered efficiently; for SQL Server, wrapping the logic in a view or stored procedure achieves the same effect by pre-computing the result on the database side before Power Apps ever sees it; and as a general technique across sources, narrowing the query with a delegable pre-filter first (for example, filtering by a delegable date range before applying a non-delegable text search) reduces the working set enough that the non-delegable portion, applied afterward with something like Search() on a Collection, becomes practically safe even though it's technically still capped.