The blue underline and the yellow warning triangle: “Delegation warning. The ‘Filter’ part of this formula might not work correctly on large data sets.” Every Power Apps maker building on SharePoint hits it eventually, and it’s the number one cause of apps that work perfectly in testing and silently lose data in production.

TL;DR: Delegation means Power Apps sends the query to SharePoint and SharePoint does the filtering. When a formula can’t be delegated, Power Apps downloads only the first 500 rows (default, raisable to 2000) and evaluates the formula locally, silently ignoring everything beyond that. Nested formulas are evaluated inside-out: a delegable inner Filter runs on the server, and any non-delegable outer function (Search, CountRows, Sort on complex columns) runs locally on the rows the inner query returned — which are themselves capped at the row limit. Fix it by filtering on indexed, simple columns with operators that actually delegate on that column type: = works everywhere, </<=/>/>=/<> only on number and date columns, StartsWith on text. Design your lists with flat text/number “shadow” columns instead of relying on Choice, Lookup and Person columns for filtering.

On this page:

1. What delegation actually is
2. Reading the warning correctly
3. Inside-out: how nested formulas are evaluated — CountRows/Search around a delegable Filter
4. What delegates on SharePoint — and what doesn’t
5. Examples per column type
6. Workarounds that hold up in production
7. Designing the data so delegation never bites
8. Testing before it hurts
9. FAQ

What delegation actually is

Power Apps is a client. Your data lives on the server (SharePoint). For any formula that touches a data source, there are only two possible execution models:

🔸 Delegated: Power Apps translates your formula into a query the connector understands (for SharePoint: an OData/REST query) and sends it to the server. SharePoint filters its millions of rows and returns only the matches. Fast, complete, correct.

🔸 Not delegated (local): the connector can’t express your formula as a server query. Power Apps downloads the first N rows — where N is the data row limit, default 500, configurable up to 2000 in Settings → General → Data row limit — and runs the formula on that partial copy. Row 501 (or 2001) onwards does not exist as far as your formula is concerned.

That second case is what the delegation warning marks. It is not an error: the app runs, and on small lists it even returns correct results. That’s exactly what makes it dangerous — it fails only after the list grows past the limit, months after you shipped.

flowchart TD
    A["Formula touches SharePoint"] --> B{"Can the connector translate<br/>every function and operator<br/>into a server-side query?"}
    B -->|"Yes"| C["Delegated ✅<br/>SharePoint filters all rows,<br/>returns only matches"]
    B -->|"No"| D["Local evaluation ⚠️<br/>First 500/2000 rows downloaded,<br/>rest silently ignored"]

⚠️ Delegation limit ≠ list view threshold. The 500/2000 data row limit is a Power Apps client setting. SharePoint’s 5,000-item list view threshold is a separate server-side limit that affects unindexed queries on large lists. You can hit either one independently; indexed columns help with both.

Reading the warning correctly

🔸 The blue underline marks the exact part of the formula that won’t delegate — often it’s one sub-expression (for example a Search() inside an otherwise delegable Filter()), not the whole formula.

🔸 Within a single data source call (one Filter, one LookUp), one non-delegable condition poisons the whole call: if any predicate can’t be translated, Power Apps falls back to local evaluation for that entire query.

🔸 No warning ≠ safe. A plain gallery bound to MyList shows no warning but still pages data; and formulas on collections never warn because collections are always local — including any incompleteness they were loaded with.

🔸 Turn on Settings → Upcoming features → “Explicit column selection” and check the App checker (stethoscope icon) regularly: it lists every delegation issue in the app in one place.

Inside-out: how nested formulas are actually evaluated

This is the mental model that makes every delegation question answerable. Power Apps evaluates nested formulas from the innermost data source call outward:

1. The innermost function that touches the data source decides what gets delegated. If it’s delegable, SharePoint executes it and returns the matching rows — but the client retrieves at most the data row limit (500/2000) of them for anything that isn’t an auto-paging gallery.
2. Every function wrapped around it then operates locally, on that returned (possibly truncated) set. The outer functions never talk to SharePoint; they no longer can — they’re working on rows that already arrived in the app.

So the correctness question is always the same: is the inner, delegated result guaranteed to stay under the row limit? If yes, the outer non-delegable functions work on a complete subset and the result is correct. If no, the outer functions silently work on a truncated subset.

flowchart LR
    A["Inner Filter<br/>(delegable)"] -->|"server executes,<br/>client retrieves ≤ 500/2000 rows"| B["Local row set"]
    B --> C["Outer Search / CountRows /<br/>Sort / GroupBy run HERE,<br/>locally, on ≤ 500/2000 rows"]

Example 1: CountRows around a delegable Filter

CountRows(Filter(Projects, StatusText = "Active"))

🔸 The inner Filter delegates: SharePoint evaluates StatusText = "Active" across the entire list — all 40,000 rows if need be.

🔸 But CountRows is not delegable on SharePoint, so Power Apps must retrieve rows to count them — and it retrieves at most 500/2000.

🔸 Result: if 320 projects are active, you get the correct 320. If 4,700 are active, you get 500 (or 2000) — a plausible-looking, silently wrong number. That’s why counters that “freeze” at exactly 500 or 2000 are the classic delegation symptom.

🔸 Correct approaches for real counts: keep the delegated subset provably below the limit, maintain a counter column via Power Automate, or have a flow return the count (length() of a filtered Get items).

Example 2: Search around a delegable Filter — the good pattern

Search( Filter(Projects, AssignedToEmail = User().Email && IsOpen = true), txtSearch.Text, "Title" )

🔸 Inner Filter: delegable (text equality + boolean), SharePoint returns only my open projects.

🔸 Outer Search: local — but a person rarely has more than a few dozen open projects, so the local set is complete and the substring search is correct.

🔸 You’ll still see a delegation warning on the Search part. Here it’s a false alarm you have consciously verified: the delegated inner query guarantees the subset stays under the limit. Document that reasoning in a comment, because the formula alone can’t prove it.

Example 3: the same functions in the wrong order

Filter( Search(Projects, txtSearch.Text, "Title"), AssignedToEmail = User().Email )

🔸 Now the innermost data source call is Search — not delegable. Power Apps downloads the first 500/2000 rows of the entire list, searches those, then filters the remnant locally.

🔸 If your projects live at row 12,000, they don’t exist here. Same building blocks, opposite outcome. Nesting order decides everything: delegable on the inside, fancy on the outside.

Example 4: local Sort on a non-sortable column

Sort( Filter(Tickets, CategoryText = "Hardware"), // delegable ✅ AssignedTo.DisplayName // Person column: no server sort ❌ )

The filter delegates; the sort runs locally on the retrieved rows. Correct as long as “Hardware” tickets stay under the limit — another reason to keep an AssignedToName text column, which would make even the sort delegable.

Example 5: AddColumns / GroupBy shaping

GroupBy( Filter(Expenses, SubmitterEmail = User().Email && FiscalYear = 2026), "CategoryText", "byCategory" )

GroupBy (like AddColumns, ShowColumns, Distinct) is always local post-processing. Fine here: one person’s expenses in one year is a small, delegably-defined set. The same GroupBy directly on Expenses would group a truncated fraction of the list.

The three rules to remember

🔸 Rule 1 — inside decides: only the innermost data-source query can be delegated; everything around it is local post-processing.

🔸 Rule 2 — the cap applies at the boundary: the delegated query hands over at most 500/2000 rows to the local layer (galleries auto-page while scrolling; functions like CountRows, Search, Sort, ForAll don’t).

🔸 Rule 3 — safe means provably small: wrapping non-delegable functions around a delegable filter is a legitimate, recommended pattern iff you can guarantee the filtered subset stays under the limit (per user, per day, per status…). If you can’t guarantee it, redesign the data or move the work server-side.

What delegates on SharePoint — and what doesn’t

Delegation support is per connector. SharePoint supports less than Dataverse or SQL Server. The lists below follow Microsoft Learn’s official delegation table for the SharePoint connector (“Connect to SharePoint from a canvas app”, last updated March 2025) — re-check it before big architecture decisions, because support occasionally changes.

Delegates to SharePoint ✅

🔸 Filter, LookUp

🔸 = — the only operator that works on all column types

🔸 <, <=, >, >=, <> — only on number and date columns

🔸 And, Or (&&, ||)

🔸 StartsWith — on text columns and Person subfields (not on Choice/Lookup subfields)

🔸 Sort, SortByColumns — on number, text, Yes/No and date columns (not on complex columns)

🔸 ColumnName = Blank() — equality only; <> Blank() doesn’t delegate

Does NOT delegate ❌

🔸 Search and the in operator (substring or membership)

🔸 <, <=, >, >=, <> on text, Yes/No and ID columns

🔸 Not (!), EndsWith, IsBlank() (use = Blank() instead)

🔸 CountRows, CountIf, Count, CountA — and Sum, Average, Min, Max on the data source

🔸 GroupBy, AddColumns, ShowColumns, Distinct (always local post-processing)

🔸 First/FirstN/Last/LastN as query shapers

🔸 Functions applied to row values inside the predicate (Year(DueDate), Lower(Title), …) — constants like User().Email or Today() are fine

⚠️ SharePoint system fields generally don’t delegate — including Identifier, IsFolder, Path, FilenameWithExtension, ContentType, ModerationStatus, VersionNumber and similar.

Column types at a glance

SharePoint column type	Delegation behaviour in Power Apps
Single line of text	✅ =, StartsWith, sort — ❌ <>, <, > do not delegate on text
Number / Currency	✅ full comparison operators (=, <>, <, <=, >, >=), sort
Date and Time	✅ full comparison operators, sort; beware time-zone/precision edge cases
Yes/No	✅ equality only (= true / = false); Not/! doesn’t delegate
ID (built-in)	⚠️ equality (=) only. The ID looks like a number but is text underneath — <, > and ranges do not delegate
Choice	⚠️ only equality on .Value, and not with multi-select; no StartsWith, no sort
Lookup	⚠️ only equality on .Id/.Value; no StartsWith, no sort; multi-select not delegable
Person or Group	⚠️ only .Email and .DisplayName subfields delegate, equality (and StartsWith); no sort; multi-select not delegable
Multiple lines of text	❌ cannot be filtered or sorted server-side at all
Managed metadata	❌ not delegable
Calculated columns	❌ not delegable

Examples per column type

Every example marked ✅ delegates against SharePoint; every ❌ falls back to local evaluation on 500/2000 rows.

Single line of text

Filter(Projects, StatusText = "Active") // ✅ Filter(Projects, StartsWith(Title, txtSearch.Text)) // ✅ Filter(Projects, StatusText <> "Closed") // ❌ <> doesn't delegate on TEXT — use OR of = or a Yes/No flag Filter(Projects, txtSearch.Text in Title) // ❌ in = substring, local Filter(Projects, EndsWith(Title, "2026")) // ❌ EndsWith never delegates Filter(Projects, Len(Title) > 10) // ❌ function on a row value

Number / Currency

Filter(Orders, Amount >= 1000) // ✅ full comparisons on number columns Filter(Orders, Amount <> 0) // ✅ <> delegates on numbers Filter(Orders, SeqNo > 2000 && SeqNo <= 4000) // ✅ real number column: basis of chunk-loading Filter(Orders, Mod(Amount, 2) = 0) // ❌ function on a row value

ID (built-in)

LookUp(Orders, ID = varSelectedId) // ✅ equality on ID delegates — the gold standard Filter(Orders, ID > 2000 && ID <= 4000) // ❌ ID is text underneath: only = delegates, ranges DON'T // (for chunking, maintain your own number column)

Date and Time

Filter(Tasks, DueDate >= Today()) // ✅ Today() is a constant value Filter(Tasks, DueDate < DateAdd(Today(), 7, TimeUnit.Days)) // ✅ computed BEFORE the query Filter(Tasks, Year(DueDate) = 2026) // ❌ Year() applied per row Filter(Tasks, CompletedDate = Blank()) // ✅ = Blank() delegates (equality only) Filter(Tasks, IsBlank(CompletedDate)) // ❌ IsBlank() doesn't delegate — use = Blank() Filter(Tasks, CompletedDate <> Blank()) // ❌ <> Blank() doesn't delegate on SharePoint

Yes/No

Filter(Tasks, IsOpen = true) // ✅ explicit comparison Filter(Tasks, IsOpen) // ⚠️ write the = true explicitly Filter(Tasks, !IsDone) // ❌ Not/! doesn't delegate: rewrite as IsDone = false

Choice

Filter(Projects, Status.Value = "Active") // ✅ equality only Filter(Projects, Status.Value <> "Closed") // ❌ no inequality on Choice Filter(Projects, Status.Value = "A" || Status.Value = "B") // ✅ OR of equalities works Filter(Projects, StartsWith(Status.Value, "Act")) // ❌ no StartsWith on Choice subfields SortByColumns(Projects, "Status") // ❌ no sort on Choice

Lookup

Filter(Orders, Customer.Id = varCustomerId) // ✅ equality on .Id Filter(Orders, Customer.Value = "Contoso") // ✅ equality on .Value Filter(Orders, StartsWith(Customer.Value, "Con")) // ❌ no StartsWith on Lookup subfields

Person or Group

Filter(Tasks, AssignedTo.Email = User().Email) // ✅ equality on .Email Filter(Tasks, StartsWith(AssignedTo.DisplayName, "Mar")) // ✅ .Email/.DisplayName are the delegable subfields Filter(Tasks, AssignedTo.Email <> User().Email) // ❌ no inequality on Person SortByColumns(Tasks, "AssignedTo") // ❌ no sort on Person Filter(Tasks, User().Email in MultiAssignees.Email) // ❌ multi-select never delegates

Multiple lines of text / Calculated

Filter(Notes, StartsWith(Body, "Urgent")) // ❌ multiline: no server filter, ever Filter(Items, CalcStatus = "Late") // ❌ calculated column: never

Two practical consequences follow from this:

🔸 On SharePoint, = is the only operator you can rely on everywhere. Range comparisons need number or date columns; <> needs number or date columns; negations (Not, <> on text/Choice) should be modelled as OR-of-equalities or explicit Yes/No flags. Complex columns (Choice, Lookup, Person) give you equality and nothing else — no sort, no multi-select.

🔸 Almost every ❌ above has a ✅ twin: compute values before the query (With(), variables), compare column ↔ constant, and mirror complex columns into flat shadow columns (data design).

Workarounds that hold up in production

1. Rewrite the formula with delegable parts (first choice)

Most warnings disappear by restating the same intent with delegable building blocks.

Substring search → StartsWith:

// ❌ Not delegable: Search + in Search(Projects, txtSearch.Text, "Title") Filter(Projects, txtSearch.Text in Title) // ✅ Delegable: StartsWith on an indexed text column Filter(Projects, StartsWith(Title, txtSearch.Text))

Per-row functions → pre-computed constants:

// ❌ Not delegable: function applied to a row value Filter(Projects, Year(DueDate) = 2026) // ✅ Delegable: pre-computed comparison values on a DATE column With({varStart: Date(2026,1,1), varEnd: Date(2026,12,31)}, Filter(Projects, DueDate >= varStart && DueDate <= varEnd))

Negations → OR of equalities or a flag:

// ❌ Not delegable: inequality on a Choice column (or on text!) Filter(Projects, Status.Value <> "Closed") // ✅ Delegable: OR of equalities on the Choice column ... Filter(Projects, Status.Value = "Active" || Status.Value = "On Hold") // ✅ ... or an explicit IsOpen Yes/No column: Filter(Projects, IsOpen = true)

“This month” → month boundaries computed once:

// ❌ Not delegable: "this month" computed per row Filter(Invoices, Month(InvoiceDate) = Month(Today()) && Year(InvoiceDate) = Year(Today())) // ✅ Delegable: month boundaries computed once, outside the query With({m0: Date(Year(Today()), Month(Today()), 1), m1: DateAdd(Date(Year(Today()), Month(Today()), 1), 1, TimeUnit.Months)}, Filter(Invoices, InvoiceDate >= m0 && InvoiceDate < m1))

Dropdown “All” filter → decide outside the query:

// ❌ Not delegable: dropdown "All" handled with in / IsBlank tricks per row Filter(Projects, IsBlank(ddStatus.Selected.Value) || Status.Value = ddStatus.Selected.Value) // ✅ Delegable: decide OUTSIDE the query, keep each branch clean If(ddStatus.Selected.Value = "All", Filter(Projects, AssignedToEmail = User().Email), Filter(Projects, AssignedToEmail = User().Email && StatusText = ddStatus.Selected.Value) )

Rule of thumb: compute everything that can be computed outside the query first (into a With() scope or variable), so the query itself only compares column ↔ constant with operators that delegate on that column type.

2. Pre-filter server-side, refine locally

Delegation only has to get you under the row limit, not to the final result. Filter delegably down to a few hundred rows, then apply the non-delegable logic to that result (see inside-out for exactly why this works — and when it doesn’t):

// Inner Filter delegates and returns < 500 rows; // the outer Search then runs locally on a COMPLETE subset. Safe. Search( Filter(Projects, OwnerEmail = User().Email && IsOpen = true), txtSearch.Text, "Title" )

This is the single most useful pattern in real apps: delegate the narrowing, localise the fancy part. It stands or falls with the guarantee that the narrowed set stays under the limit.

3. Load into a collection — deliberately, not accidentally

ClearCollect(colProjects, Projects) is itself subject to the row limit: you collect at most 500/2000 rows. Collections are a valid tool when you know the relevant subset is small (reference/lookup data, “my items”), ideally pre-filtered delegably:

ClearCollect(colMyOpen, Filter(Projects, OwnerEmail = User().Email && IsOpen = true))

For genuinely large lists, the community “load everything in chunks” pattern technically works — but note it needs a real number column you maintain yourself (e.g. SeqNo), because range comparisons on the built-in ID don’t delegate on SharePoint. Treat it as a last resort either way: load time grows linearly with list size, data is stale the moment it’s loaded, and memory limits on mobile devices are real. If you find yourself building it, that’s usually the signal to move to Dataverse.

4. Delegate the query to Power Automate or SharePoint REST

A flow triggered from Power Apps can run Get items with an arbitrary OData filter (including columns Power Apps can’t delegate), aggregate server-side, and return a compact JSON result. Good for counts, sums and reports; the trade-offs are latency (seconds, not milliseconds) and one flow run per query, which matters for licensing/throughput.

5. Raise the row limit to 2000 — the honest assessment

Settings → General → Data row limit up from 500 to 2000 buys headroom, nothing more. It doesn’t fix the query, it delays the failure until row 2001. Set it to 2000 anyway (there is rarely a reason not to), but never call it a solution.

Designing the data so delegation never bites

This is the highest-leverage section of this guide: most delegation pain is a data design problem that was locked in before the first screen was built.

Column choice: prefer flat over fancy

🔸 Text column instead of Choice column for anything you will filter on. A single line of text Status column with values maintained by your app (or a Choice column plus a text shadow copy) gives you delegable = and StartsWith. Note that <> doesn’t delegate on text either — model exclusions as OR-of-equalities or a Yes/No flag. Keep the Choice column for forms/UX if you like — but query the text twin.

🔸 Store the key, not just the Lookup. Next to a Lookup column Customer, keep CustomerId (number) and/or CustomerName (text) as plain columns, written by your app or a flow on create/update. Filters hit the flat columns and delegate — the number column even gives you ranges and <>; the Lookup stays for navigation and display.

🔸 Person columns: keep the email as text. A AssignedToEmail single-line-of-text column beside the Person column makes Filter(list, AssignedToEmail = User().Email) bullet-proof and delegable — including sorting, which Person columns can’t do.

🔸 Yes/No flags instead of negations. You can’t delegate Status.Value <> "Closed" (nor <> on a text column, nor Not), but you can delegate IsOpen = true. Materialise the states you actually query as boolean flags.

🔸 Number columns are the range workhorse. <, >, <=, >=, <> delegate on numbers and dates — not on text, not on Yes/No, not on the built-in ID. If you need ranges, buckets or chunked loading, maintain a real number column (SeqNo, DueYear, AmountCHF).

🔸 Pre-compute what you’d otherwise calculate in the query. Calculated columns don’t delegate — but a real column filled at write time (by the app or a flow) does. Need to filter by year? Store DueYear as a number column. Need “overdue”? A scheduled daily flow can maintain an IsOverdue flag.

🔸 Avoid multiline text for anything queryable. Multiple lines of text can’t be filtered or sorted server-side, ever. Keep a single-line summary/keyword column beside it if you need to search.

A worked before/after list design

A typical “Projects” list, as most people first build it — and the delegation-proof version:

Purpose	❌ First attempt	✅ Delegation-proof design
Status	Choice Status only	Choice Status (forms) + text StatusText + Yes/No IsOpen (queries)
Owner	Person Owner only	Person Owner (display) + text OwnerEmail (filter/sort), indexed
Customer	Lookup Customer only	Lookup Customer + number CustomerId + text CustomerName, indexed
Year reporting	filter on Year(DueDate)	number column DueYear, written on save
Ranges / chunking	ranges on built-in ID	number column SeqNo (ID only delegates =)
Overdue view	calculated column IsLate	Yes/No IsOverdue, maintained by a daily flow
Free-text notes	filter on multiline Notes	multiline Notes + single-line Keywords for search

The shadow columns are written once, in the app’s Patch/form OnSuccess (or a flow on create/update) — a few minutes of work that buys you fully delegable filters on every screen.

List design

🔸 Index every column you filter or sort on (List settings → Indexed columns, up to 20 per list). This is what keeps delegated queries working once the list passes SharePoint’s 5,000-item view threshold. Do it before the list grows: on very large lists, adding an index can be blocked.

🔸 Keep lists narrow and purposeful. One process, one list. Split archives into a separate list (a yearly flow can move closed items), so the hot list stays small and every query gets faster.

🔸 Plan realistic volume, then choose the platform. SharePoint lists technically hold 30 million items, but as a relational app backend they get uncomfortable long before that. If you expect tens of thousands of frequently-queried rows, complex relationships, or row-level security, start on Dataverse — its delegation support is dramatically broader (including the in operator, aggregates and more).

Decision guide

flowchart TD
    A["New app on SharePoint:<br/>will this list exceed ~500 rows?"] -->|"No, and it never will"| B["Build freely —<br/>delegation can't hurt you"]
    A -->|"Yes / probably"| C{"Can every filter & sort be expressed<br/>with = / StartsWith on text,<br/>or ranges on number & date columns?"}
    C -->|"Yes"| D["SharePoint is fine:<br/>flat shadow columns + indexes,<br/>row limit to 2000"]
    C -->|"No — needs full-text search,<br/>aggregates, complex security"| E["Consider Dataverse<br/>or a Power Automate<br/>query layer"]

Testing before it hurts

🔸 Test with more rows than the limit. Fill a dev list with 3,000+ dummy rows (a simple flow or the SharePoint REST batch API does this in minutes). If your app shows correct data at 3,000 rows, delegation is genuinely working — at 50 test rows you know nothing.

🔸 Temporarily set the data row limit to 1 while testing: any non-delegated query becomes instantly, visibly wrong instead of subtly wrong.

🔸 Check the App checker (stethoscope) for the full list of delegation warnings before every release, and use Monitor to inspect the actual getRows calls — you can see whether a filter went into the OData query (delegated) or not.

🛠️ FAQ

Does the delegation warning mean my app is broken? Not necessarily. It means the marked expression is evaluated on at most 500/2000 locally downloaded rows. If the underlying data source (or the delegably pre-filtered subset) is guaranteed to stay below the limit, the result is correct. The risk is that the guarantee silently expires as data grows.

What happens if I wrap CountRows or Search around a delegable Filter? The inner Filter is delegated and executes on the server across the whole list; the outer function then runs locally on the returned rows — of which the client retrieves at most 500/2000. If the filtered subset fits under the limit, the count/search result is complete and correct (and the remaining warning is a verified false alarm). If it doesn’t fit, CountRows caps at exactly 500/2000 and Search quietly misses matches. See inside-out for worked examples.

Why does my counter freeze at exactly 500 or 2000? That’s the data row limit showing through a non-delegated aggregate: CountRows (or Sum/Average) counted only the rows the client retrieved. Keep the delegated subset smaller than the limit, maintain a counter column via a flow, or have Power Automate return the aggregate.

Why does my app work in the studio but miss records in production? Almost always delegation: the production list has grown past the data row limit, and a non-delegated filter only sees the first 500/2000 rows. Test with 3,000+ rows or set the row limit to 1 to reproduce it.

Is setting the data row limit to 2000 a fix? No. It quadruples the headroom for non-delegated queries and slightly increases data transfer, nothing else. Use it, but fix the formulas and data design anyway.

Can I filter by ID ranges (ID > 1000) on SharePoint? No — per Microsoft Learn, the built-in ID column is text underneath and only supports = for delegation; relational operators on ID don’t delegate. Use LookUp(list, ID = x) for single records, and maintain your own number column if you genuinely need ranges or chunked loading.

Can I make Search() or the in operator work on SharePoint? Not delegably — the SharePoint connector doesn’t support them server-side. Either narrow first with a delegable Filter and run Search on the small result, use StartsWith on an indexed text column instead, or move the data to Dataverse, where substring search scenarios are far better supported.

Are Choice, Lookup and Person columns bad? They’re fine for display and forms. For filtering and sorting they are limited (equality only on .Value/.Id/.Email/.DisplayName, no multi-select, no sort) — so mirror the values you query into plain text/number columns and filter on those.

How is the 5,000-item list view threshold different from delegation? Delegation limits are client-side (what Power Apps evaluates locally). The list view threshold is SharePoint’s server-side cap on unindexed query operations in large lists. A perfectly delegable query can still fail on a 6,000-item list if the filtered column isn’t indexed. Index your filter/sort columns and both problems shrink.

When should I stop fighting and move to Dataverse? When you need full-text search, aggregates (Sum, CountRows) over large sets, in/membership filters, complex relationships, or row-level security — or when you’re building chunked-collection loaders just to see your own data. Dataverse delegates nearly everything SharePoint can’t; see the Dataverse vs SharePoint comparison.

🔗 Related

🔸 Dataverse vs SharePoint

🔸 Power Fx variables deep dive

🔸 All SharePoint tips · All Power Apps tips

🔸 Knowledge base overview

Delegation support verified against Microsoft Learn’s SharePoint connector documentation (“Connect to SharePoint from a canvas app”, last updated March 2025) and the delegation overview. Microsoft occasionally changes delegation support — re-check the official docs before making architecture decisions.