Delegation and Its Limits

Analogy categoryAnalogies

What Delegation Actually Means

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.

🏏

Cricket analogy: Delegation is like asking Cricinfo's server to compute Virat Kohli's career average against every international innings he's played, versus manually downloading every scorecard PDF and adding them up yourself on a calculator.

Delegable Functions and Operators

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.

🏏

Cricket analogy: Different data sources supporting different delegable operations is like different cricket boards having different DRS technology available — some grounds have full Hawk-Eye and UltraEdge, others only have basic replay, changing what decisions can be reviewed accurately.

powerfx

// This looks like two delegable pieces combined, but always check the delegation warning triangle
Filter(
    Orders,
    Status = "Open" && StartsWith(CustomerName, "ACM")
)
// If flagged, consider a Dataverse calculated column, a SQL view, or narrowing scope with an indexed column instead

Practical Strategies for Non-Delegable Formulas

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.

🏏

Cricket analogy: Narrowing with a delegable pre-filter first is like a scout first filtering to only matches from the last IPL season (a fast, indexed query) before manually reviewing footage for a specific bowling technique, instead of reviewing every match ever played.

A common and safe workaround is to load a delegable subset into a Collection with ClearCollect(colSubset, Filter(LargeTable, DelegableCondition)) and then run non-delegable logic like Search() or a complex Sort against that smaller local collection — this works well as long as the pre-filter genuinely narrows the set to a manageable size.

Raising the app's Data row limit setting above the default 2,000 does not fix a delegation problem — it only raises the ceiling on how many rows are pulled locally before non-delegable processing happens, which can slow the app down and still silently truncate results once the true row count exceeds the new, higher limit.

Delegation pushes formula evaluation (Filter, Sort, Search, aggregates) down to the data source instead of pulling all rows into local memory.
Non-delegable formulas only process up to the app's Data row limit (default 2,000), which can produce silently incomplete results at scale.
Dataverse and SQL Server support the widest range of delegable functions; SharePoint is moderate; Excel is essentially non-delegable.
Even individually delegable functions can break delegation when combined in certain ways — always check the formula bar's warning triangle.
Fixes include server-side calculated columns/rollups, SQL views/stored procedures, and pre-filtering with a delegable condition before a non-delegable step.
Raising the Data row limit setting is not a real fix for delegation — it only raises the ceiling before silent truncation occurs.