Canvas app delegation: patterns that scale past 2000 rows

Every Canvas maker eventually sees the yellow warning triangle: "Delegation warning. The '...' part of this formula might not work correctly on large data sets." Most makers ignore it. Some rewrite frantically. The right response is in between - understand what delegation actually is, and pattern-match to the small set of fixes that get you past the warning without rewriting the app.

Here is how we teach it on our team, and the patterns that handle 95% of real cases.

What delegation actually does

When Canvas binds a gallery to a Dataverse table, the naive interpretation is "the gallery shows rows from that table." The reality is: the client downloads rows, the Power Fx formula runs client-side, and the visible rows are whatever matches the formula.

Delegation changes this. Instead of downloading everything, Canvas translates the Power Fx formula into a query the data source can run server-side. The server returns only the matching rows. The client renders them.

The catch: not every Power Fx function translates to every data source. When Canvas cannot translate (e.g., the function is too complex, or the data source doesn't support it), it falls back to the non-delegated path - downloading up to the "non-delegable data row limit" (default 500, configurable up to 2000) and running the formula client-side. On a table with 10,000 rows, you miss 8,000 of them silently.

The warning triangle appears exactly when Canvas knows it has to fall back.

The quick reference

Delegable on Dataverse:

• Filter() with =, <>, >, <, >=, <= on single columns
• Filter() with And, Or, Not combining delegable expressions
• Sort() by a single column
• LookUp() with delegable expressions
• StartsWith(), IsBlank() in Filter
• CountRows(), Sum(), Average(), Min(), Max() - sometimes, check the current docs

Not delegable on Dataverse:

• Functions inside the filter expression: Filter(Table, Lower(Name) = "x") - the Lower() forces client-side
• Search() in Dataverse (was never delegable)
• Complex conditionals like If(Something, row.A = x, row.B = x)
• FirstN with dynamic N from a variable

This list changes as Microsoft adds delegation support. Check the current compatibility table before relying on a specific function.

Pattern 1: stop using Search() for Dataverse

Search() is delegable on SharePoint. It is not delegable on Dataverse. Teams that started on SharePoint, learned the Search() pattern, and migrated to Dataverse carry the habit and hit walls.

The replacement for Dataverse is Filter(Table, StartsWith(Name, searchText)) for prefix search, or Filter(Table, Name = searchText) for exact. If you need "contains anywhere in the string" - Dataverse doesn't delegate that, and the workaround is either:

• Accept the 2000-row limit (fine for small tables)
• Use Dataverse's built-in search service via Search.FullText() (newer Power Fx feature, check support in your tenant)
• Build a Dataverse view with the filter baked in, and bind the gallery to the view

Pattern 2: separate the UI state from the Dataverse query

A gallery showing "my active tickets" that the user can refine with a search box. The naive formula:

This mixes three filters, some of which depend on user input (SearchBox.Text). If any single component is non-delegable, the whole thing falls back.

The pattern we use: chain Filter calls where the first one is delegable and runs on the server, and later calls (if any) run on the client.

The inner Filter is strictly delegable and runs on the server, returning at most a few hundred tickets (the user's open set). The outer Filter runs client-side on those few hundred. The combined experience is fast and correct, even on a table with millions of tickets globally.

Pattern 3: load into a collection once, filter in memory

For the set of data a user works with across many screens - their open tickets, their recent orders - loading once into a collection and filtering against the collection is faster than re-querying Dataverse on every screen change.

Gallery on any screen:

The collection lives in memory. The secondary filter runs instantly. The trade-off: the collection is a snapshot; if tickets change while the user is in the app, the collection is stale. For tickets that change infrequently, this is fine; for a live call-center dashboard, it is not.

Pattern 4: pagination via FirstN plus offset

When the data set is too big even for the 2000-row client buffer, manual pagination is the answer. The user sees 50 rows at a time with Next/Previous buttons.

Both FirstN and Skip delegate to Dataverse in recent versions. The gallery only ever holds 50 rows, no matter how large the underlying table.

Caveat: this pattern works for simple pagination, not for "jump to page 37" without a total count. If the business needs "page X of Y," you need a separate delegable count - either CountRows on the same Filter, or a pre-computed rollup.

Pattern 5: when you can accept the 2000-row limit

Not every table is huge. A lookup of country codes has 200 rows. A list of departments has 30. A list of product categories has 50. For these, delegation does not matter - the whole table fits in the client buffer comfortably.

On startup, the team should explicitly identify which tables are "small" (under 1000 rows, growth capped) and which are "large" (unbounded). Small tables can use any Power Fx function without worry. Large tables need the delegation discipline.

A column in the project's table inventory: "Delegation concern: yes/no." If yes, the team reviews the formulas against it; if no, makers can build quickly without second-guessing.

Pattern 6: set the non-delegable limit to 2000 up front

Advanced settings → Data row limit for non-delegable queries: default 500, max 2000. Raise it to 2000 on day one.

Why not leave it at 500? Because development is faster when small tables fit without thought, and the limit affects only the non-delegated path. Once you have tables past 2000 rows, delegation is mandatory anyway - the limit does not save you.

Leaving it at 500 mainly serves as a nuisance warning for developers. Tools exist to nag you via ESLint-style rules more specifically; the 500 default just slows iteration.

The formula review we run

Before merging any Canvas app PR, the reviewer:

1. Opens the Power Apps Studio URL for the PR preview
2. Looks at every delegation warning in the app checker
3. For each warning, either:Rewrites the formula to delegate, orDocuments in a comment on the formula why delegation is unnecessary (table bounded, known small)

The second bullet is the discipline that pays off long-term. A maker six months from now sees the comment and understands the choice; without it, they flip a flag thinking they are fixing a warning and the app slows for thousands of users.

If the warnings are left unexplained, they accumulate. Every accumulated warning is a potential scale issue waiting for the table to grow past 2000 rows - which, for most business data, happens within a year.