LowCodeStacks
Sign in

Delegation cheat sheet: what works on SharePoint, Dataverse and SQL Server

Which Power Apps functions and operators run on the data source, and which quietly stop at 500 rows, for SharePoint, Dataverse and SQL Server, with the delegable rewrite for each common trap.

Power AppsLook it up · Quick reference7 min read

Checked against Microsoft Learn · 6 sourcesHow we write guides

A canvas app only sees every row when the data source does the work. When a formula can't be handed to the source (it isn't delegable), Power Apps fetches the first 500 rows (2,000 at most) and works on those. Anything past that is silently missing. This page is the lookup table: what each source accepts, and how to rewrite the formulas that don't fit.

For the why and a worked example, read Why your gallery stops at 500 rows first.

Note

Checked against Microsoft Learn on 6 October 2026. Microsoft extends delegation support over time, so trust the warning in Power Apps Studio over any table, including this one.

The rules that apply everywhere

  • All or nothing. If any part of a query can't be delegated, none of it is: the whole query runs on the first 500 or 2,000 rows.
  • The warning. A non-delegable formula over a delegable source shows a yellow triangle and a blue underline. No warning on Excel, collections or variables doesn't mean "fine". Those sources never delegate, because the data is already on the device.
  • Constants are free. Anything that's the same for every row, such as Today(), User().Email, a variable or a control's value, is sent to the source as a value and never blocks delegation.
  • The column goes on the left. Write Status = varStatus, not varStatus = Status, especially when one side is a lookup.
  • Lookups. At most two levels of lookup in one query (one when offline), and up to 20 joined tables.
  • Usually delegable (if the source supports it): Filter, LookUp, Search, First, Sort, SortByColumns; inside them, And, Or, Not, in (on base columns), =, <>, <, >, <=, >=, +, -, StartsWith, EndsWith, IsBlank, TrimEnds.
  • Never delegable: If inside a filter, *, /, Mod, Text(), Value(), & and Concatenate, Lower, Upper, Left, Mid, Len (with source-specific exceptions below), FirstN, Last, LastN, Choices, Concat, Distinct, GroupBy, Ungroup, Collect, ClearCollect.

SharePoint

SharePoint delegates the least. The surprises are text comparisons and search.

NumberTextYes/NoDateChoice, Lookup, Person
=YesYesYesYesYes, on the subfield
< > <= >= <>YesNoNoYesDepends on the subfield
StartsWithYesNo on Choice or Lookup subfields
IsBlankNoNo
Search, in (substring)No
Sort, SortByColumnsYesYesYesYesNo
NotNoNoNoNoNo

SharePoint traps:

  • ID only supports =. It looks like a number in Power Apps but is text underneath, so ID > 100 won't delegate.
  • Person columns: only Email and DisplayName delegate.
  • System fields don't delegate. That includes Name, Path, FullPath, Content Type, Version, Is Checked Out, Moderation Status and the other built-in columns.
  • UpdateIf and RemoveIf run on the device for SharePoint and only change up to the 500/2,000 limit per run. For bulk changes, use a flow.

Dataverse

Dataverse delegates the most. If you're choosing a data source for a large app, this table is the argument.

NumberTextChoiceDateUnique ID
= <>YesYesYesYesYes
< > <= >=YesYesNoYes
And Or NotYesYesYesYesYes
in (is one of)YesYesYesYesYes
in (text contains)Yes
SearchYes
StartsWithYes
IsBlankYesYesNoYesYes
Sum Min Max AverageYesNo
CountRows CountIfYesYesYesYesYes

Dataverse traps:

  • Arithmetic on a column (Amount + 10 > 100) doesn't delegate. Move the arithmetic to the other side: Amount > 90.
  • No Len or TrimEnds. Left, Mid, Upper, Lower, Replace and Substitute are supported; casting with Text(column) isn't.
  • Now() and Today() don't delegate against date columns. Put them in a variable first.
  • Counting: CountRows and CountIf delegate once Enhanced delegation for Microsoft Dataverse is on (Settings → Upcoming features → Preview).
    • With a filter, counts stop at 50,000.
    • CountRows(Table) with no filter uses a cached count that updates periodically, so it may lag slightly. For an exact number under the limit, use CountIf(Table, true).

SQL Server

NumberTextYes/NoDateUnique ID
= <>YesYesYesYesYes
< > <= >=YesNoNoYes
+ - * /YesNo
StartsWith, EndsWithYes
Search, in (text contains), LenYes
IsBlankNoNoNoNoNo
Sum AverageYes
Min MaxYesNo

Unlike SharePoint, SQL Server delegates Not, Search and arithmetic. Like SharePoint, it doesn't delegate IsBlank.

Rewrites for the common traps

You wroteProblemDelegable instead
Filter(List, IsBlank(Customer)) on SharePointIsBlank doesn't delegateFilter(List, Customer = Blank()). It doesn't treat an empty string "" as blank, which is usually what you want; works with =, not <>
Search(List, txtSearch.Text, Title) on SharePointSearch doesn't delegateFilter(List, StartsWith(Title, txtSearch.Text)). It matches from the start of the text, not anywhere in it
Filter(List, ID > 100) on SharePointID only supports =Filter on a real number or date column, such as Created
Filter(Orders, Total * 1.05 > 1000)Arithmetic on the columnFilter(Orders, Total > 1000 / 1.05)
Filter(Tasks, Due < Today()) on DataverseToday() against a date columnA named formula varToday = Today(); in App.Formulas (or Set(varToday, Today()) in OnStart), then Filter(Tasks, Due < varToday)
Filter(List, Not(Done)) on SharePointNot doesn't delegateFilter(List, Done = false)
Distinct(Orders, Region) for a dropdownDistinct never delegatesA separate Regions list or table, or a Dataverse choice column
CountRows(Filter(...)) for a dashboardMay stop at 50,000, or at 500 if the filter isn't delegableKeep the filter delegable; for big totals, use a Power BI tile or a stored rollup column

Test that it really delegates

  • Set the row limit to 1 while you build. In Settings → General → Data row limit, choose 1. Any formula that isn't delegated now returns at most one row, so you'll notice straight away. Put it back before you publish.
  • Use Monitor to confirm. Advanced tools → Monitor shows the request each formula sends and how many rows come back. Use it when there's no warning but the numbers look short.
  • Test with more than 2,000 rows. Delegation problems never show on a list of 50 test items.

Sources