Canvas App Delegation Deep Dive: Rewriting Non-Delegable Queries with Named Formulas, Explicit Column Selection, and Server-Side Filtering Patterns

Introduction

You've built a canvas app that works perfectly in testing. Your gallery loads fast, your search filters correctly, and everything feels snappy. Then you push it to production, your data grows past 500 records, and suddenly the app starts returning wrong results — filtering out data that clearly exists, or missing records entirely. You check your formula, it looks fine, and yet the app is lying to you.

Welcome to delegation. More specifically, welcome to the silent failure mode that catches nearly every Power Apps developer off guard at least once. Delegation is the mechanism that determines whether Power Apps sends a query to the data source and lets the server do the filtering, or whether it pulls data down to the client and filters locally. When your formula isn't delegable, Power Apps quietly caps the result at 500 rows (or 2,000 if you bump the limit in settings), applies your filter to that local subset, and returns whatever it finds. No error. No warning beyond a faint yellow triangle that's easy to ignore. Just wrong answers at scale.

By the end of this lesson, you'll know exactly how to diagnose delegation failures, why certain formulas can't be delegated and what to do about it, and how to systematically rewrite non-delegable queries using named formulas, explicit column selection, and server-side filtering patterns. You'll walk away with a toolkit of techniques that actually hold up in production.

What you'll learn:

• How to read delegation warnings and understand which operations can and can't be delegated for common connectors like SharePoint and Dataverse
• How to use Named Formulas in Power Apps to write cleaner, more performant server-side queries
• How to apply explicit column selection with ShowColumns and DropColumns to reduce payload size and avoid delegation pitfalls
• Practical server-side filtering patterns including compound predicates, lookup-based filtering, and date range queries
• How to architect multi-step data retrieval for scenarios where a single delegable formula isn't possible

---

Prerequisites

You should already be comfortable with:

• Writing basic Power Apps formulas (Filter, Search, LookUp, Gallery bindings)
• Connecting to SharePoint lists or Dataverse tables
• Understanding that delegation exists (even if you've been burned by it)

You don't need to be a Power Platform architect. This lesson is for practitioners who are past the basics and ready to write production-quality formulas.

---

Understanding What Delegation Actually Does

Before we fix delegation problems, let's be precise about what's happening. When your gallery's Items property contains something like:

Filter(ProjectTasks, Status = "Active" And AssignedTo = CurrentUser().Email)

Power Apps evaluates this formula and has to make a decision: can it translate this into a server-side query that the data source can execute natively, or does it need to retrieve records and filter locally?

For a SharePoint list, a simple equality filter on a text column like Status = "Active" is delegable. The connector knows how to generate a CAML query or OData $filter clause for that. But CurrentUser().Email against a Person column in SharePoint? That's not delegable — Power Apps can't translate the comparison of a Person field against an email string into a native server query the SharePoint connector supports.

The result: Power Apps fetches the first 500 (or 2,000) rows from the list and then applies your filter locally against that subset. If your list has 10,000 rows, you're filtering 2,000 of them and missing 8,000. Your app doesn't crash. It just returns incomplete data.

Critical distinction: Delegation limits aren't about display limits. A gallery with Items = ProjectTasks shows only 100 rows at a time by default — but it retrieves all delegable records from the source. It's the filtering and sorting operations that are subject to delegation, not pagination of results.

The delegation limit setting (found under Settings → App Settings → Advanced Settings → Data row limit) controls how many rows Power Apps retrieves locally when a non-delegable operation forces client-side processing. Setting it to 2,000 is the maximum and gives you a larger local dataset to filter against, but it's a band-aid, not a fix.

Reading the Yellow Triangle

When you write a non-delegable formula, Power Apps underlines the problematic part in blue and shows a yellow warning triangle in the formula bar. Hovering over it gives you a message like: "This part of your formula might not work correctly for large data sets."

The key word is might. If your list has 300 records and you never grow beyond that, you'll never see wrong results. This is exactly why delegation bugs are insidious — they're latent until scale exposes them.

To see a full delegation report for your app, go to View → Data sources (or check the delegation warning in the formula bar). For each data source, Power Apps can show you which operations are delegable. You can also check the official Microsoft delegation documentation for each connector — the rules differ meaningfully between SharePoint, Dataverse, SQL Server, and others.

---

The Delegation Rules Map: SharePoint vs. Dataverse

Understanding which operations delegate is connector-specific knowledge you need to internalize. Let me give you the practical map.

SharePoint

Delegable operations:

• Filter with =, <>, <, >, <=, >= on most column types
• Filter with And (multiple conditions)
• Sort and SortByColumns on indexed columns
• Search on text columns (maps to SharePoint full-text search)
• CountRows when used with a delegable filter

Not delegable in SharePoint:

• Or conditions — this is the one that surprises people most
• Search combined with Filter in the same expression
• In operator for checking collection membership
• StartsWith and EndsWith — StartsWith is delegable, EndsWith is not
• String functions: Upper, Lower, Len, Left, Right, Mid inside filters
• Comparing against calculated values or functions like Today() inside certain contexts
• Filtering on Person/Group columns by email string directly
• IsBlank on certain column types

Dataverse

Dataverse generally has better delegation support because it's purpose-built for Power Platform. Most comparison operators delegate, Or is supported, and more complex predicates work server-side. Key non-delegable exceptions include:

• StartsWith on non-indexed columns
• Complex nested Filter calls that reference collection values
• Some date arithmetic functions inside filter predicates

Practical rule of thumb: Dataverse is the more delegation-friendly choice for high-volume data. If you're building something that will scale to tens of thousands of records, strongly consider Dataverse over SharePoint.

---

Named Formulas: The Underused Superpower

Named Formulas were introduced in Power Apps as a way to define reusable formula expressions — similar to named ranges in Excel, but more powerful. They live in the App.Formulas property and are evaluated lazily (only when referenced) rather than eagerly.

Here's why they matter for delegation: Named Formulas let you break a complex, non-delegable monolith into smaller, server-friendly pieces that you can reference throughout your app.

Setting Up Named Formulas

In the Power Apps Studio, click on the App object in the Tree View and select the Formulas property. This is where you define your named formulas.

A Named Formula uses this syntax:

FormulaName = Expression;

Multiple formulas are separated by semicolons. Here's a realistic example for a project management app connecting to a Dataverse table called cr_ProjectTasks:

// App.Formulas

ActiveTasks = 
    Filter(
        cr_ProjectTasks,
        cr_status = "Active",
        cr_duedate >= Today()
    );

CurrentUserTasks = 
    Filter(
        cr_ProjectTasks,
        cr_status = "Active",
        cr_assignedto_email = User().Email
    );

OverdueTasks = 
    Filter(
        cr_ProjectTasks,
        cr_status = "Active",
        cr_duedate < Today()
    );

Now in any gallery, you simply set Items = ActiveTasks. No repeated formula logic. No risk of one screen's gallery using slightly different filter logic than another.

Why Named Formulas Improve Delegation

Named Formulas don't change which operations are delegable — that's still determined by the connector. What they do is:

1. Force you to write explicit, reusable queries rather than building complex logic inline where it's tempting to pile on non-delegable functions
2. Eliminate recalculation — a Named Formula is calculated once and its result is referenced by all consumers, rather than each gallery independently evaluating the same expensive query
3. Make delegation warnings visible in one place — if ActiveTasks has a delegation warning, you'll see it in App.Formulas, fix it once, and all consumers inherit the fix

Here's a common mistake to avoid: don't use Named Formulas to cache collections. If you write ActiveTasks = Filter(cr_ProjectTasks, ...), this remains a live query — not a local collection. Every time ActiveTasks is referenced, it re-queries the source. This is good for data freshness but means you shouldn't use it as a substitute for Collect when you need client-side manipulation.

Named Formula vs. Variable: Use Named Formulas for query definitions you want to reuse. Use Set() or Collect() into variables when you explicitly need to bring data to the client for manipulation, offline scenarios, or complex multi-step operations.

---

Explicit Column Selection: Cutting Payload and Avoiding Pitfalls

Every column your query returns gets transmitted from the server to the client. In a SharePoint list with 50 columns, pulling all 50 when your gallery only displays 5 is wasteful — and it can actually trigger delegation issues in some scenarios where columns with complex types force client-side evaluation.

Using ShowColumns to Be Explicit

ShowColumns takes a table and a list of column names, returning only those columns:

Filter(
    ShowColumns(
        SalesOpportunities,
        "Title",
        "CloseDate", 
        "EstimatedValue",
        "Stage",
        "AccountName"
    ),
    Stage <> "Closed Lost",
    CloseDate >= Today()
)

Wait — this actually has a problem. When you nest ShowColumns inside Filter, you're applying ShowColumns first, which means your filter only has access to the columns you selected. If you need to filter on IsArchived but don't want to display it, you'd need to either include it in your column selection or restructure the query.

The correct pattern for filtering on columns you don't want to display:

ShowColumns(
    Filter(
        SalesOpportunities,
        Stage <> "Closed Lost",
        CloseDate >= Today(),
        IsArchived = false
    ),
    "Title",
    "CloseDate",
    "EstimatedValue", 
    "Stage",
    "AccountName"
)

Filter first. Select columns after. This way the filter has full access to the table schema, and you only trim down the payload after filtering is complete.

The Dataverse Explicit Column Selection Feature

Dataverse specifically supports explicit column selection as a delegation-aware feature. In your data source settings for a Dataverse connection, you can enable explicit column selection, which tells the connector to generate $select OData parameters on the query — meaning the server only returns the columns you reference.

To take advantage of this, reference only the columns you need in your formula. Power Apps tracks which columns are accessed and can generate a minimal $select. This has a real performance impact at scale: a record with 40 columns at 10,000 rows is meaningfully slower than the same record with 6 columns.

DropColumns as an Alternative

DropColumns lets you specify which columns to exclude rather than include. It's useful when you want most columns but need to remove a few problematic ones:

DropColumns(
    Filter(ProjectTasks, Status = "Active"),
    "InternalNotes",
    "FinancialData",
    "LegacyID"
)

Use DropColumns when you need the majority of columns. Use ShowColumns when you need a minority of them. The explicit positive selection of ShowColumns is generally clearer and safer for production code.

---

Server-Side Filtering Patterns That Actually Work

Now let's get into the patterns that let you push filtering logic to the server even when your initial instinct would produce a non-delegable formula.

Pattern 1: Replacing OR with Multiple Queries + Concat

Or conditions don't delegate in SharePoint. If you need tasks that are either "Active" OR "In Review", you can't use:

// NON-DELEGABLE in SharePoint
Filter(ProjectTasks, Status = "Active" Or Status = "In Review")

The server-friendly pattern is to run two delegable queries and combine them client-side using Ungroup or simply accept the table union:

// Evaluates both filters on the server, combines results client-side
Sort(
    Distinct(
        Ungroup(
            Table(
                {Results: Filter(ProjectTasks, Status = "Active")},
                {Results: Filter(ProjectTasks, Status = "In Review")}
            ),
            "Results"
        ),
        ID
    ),
    Title,
    SortOrder.Ascending
)

This is verbose, but each Filter call is individually delegable. Both server queries run with full dataset access. The Distinct on ID deduplicates in case any record appears in both sets (unlikely here but good practice). The final Sort is client-side but operates on the combined result, which is already fully filtered.

A cleaner production pattern for this specific case: if you control the SharePoint list schema, add a calculated column or Choice column that maps your OR condition to a single filterble value. Push the logic into the data model rather than the query.

Pattern 2: Handling User Context Correctly

Person columns in SharePoint are a classic delegation problem. Filtering by the current user's identity requires the right approach:

// NON-DELEGABLE: comparing Person field to email string
Filter(ProjectTasks, AssignedTo.Email = User().Email)

// DELEGABLE (in SharePoint): use the person's display name  
// Still not always delegable depending on SharePoint connector version

// Best pattern: Store a lookup value you can actually filter on
Filter(ProjectTasks, AssignedToEmail = User().Email)

The most reliable approach is to denormalize: when a task is created or assigned, store the assignee's email as a plain text column (AssignedToEmail) alongside the Person column. Then Filter(ProjectTasks, AssignedToEmail = User().Email) is a simple text equality comparison that delegates cleanly.

This feels like extra work, but it solves a class of problems: filtering by email, by display name, by Azure AD Object ID — all of these become trivial text comparisons instead of complex Person field operations.

Pattern 3: Date Range Filtering

Date filtering is where developers often introduce non-delegable logic accidentally:

// NON-DELEGABLE: DateAdd inside a filter predicate
Filter(ProjectTasks, DueDate <= DateAdd(Today(), 7, TimeUnit.Days))

// DELEGABLE: pre-compute the date boundary outside the filter
Set(varDeadline, DateAdd(Today(), 7, TimeUnit.Days));
Filter(ProjectTasks, DueDate <= varDeadline)

The critical insight: DateAdd and similar date functions aren't delegable when they appear inside a Filter predicate. But if you compute the value first and store it in a variable, the filter predicate becomes a simple date comparison against a literal value, which is delegable.

Put the variable assignment in App.OnStart or the relevant screen's OnVisible:

// Screen.OnVisible
Set(varSevenDaysOut, DateAdd(Today(), 7, TimeUnit.Days));
Set(varStartOfMonth, Date(Year(Today()), Month(Today()), 1));
Set(varEndOfMonth, Date(Year(Today()), Month(Today()) + 1, 1) - 1);

Then in your Named Formula:

// App.Formulas
UpcomingDeadlines = 
    Filter(
        ProjectTasks,
        Status = "Active",
        DueDate >= Today(),
        DueDate <= varSevenDaysOut
    );

Today() itself is delegable for most connectors when used as a comparison boundary. It's the arithmetic around it that breaks delegation.

Pattern 4: Search Combined with Filter

The Search function is convenient for user-driven text search, but you can't combine Search and Filter in a single delegable expression for SharePoint:

// NON-DELEGABLE in SharePoint
Filter(
    Search(ProjectTasks, SearchInput.Text, "Title", "Description"),
    Status = "Active"
)

The pattern that works: use Filter for the delegable status criteria, and let Search operate on the result client-side — accepting that the search portion is local, but at least you've constrained the dataset server-side first:

Search(
    Filter(ProjectTasks, Status = "Active"),
    SearchInput.Text,
    "Title",
    "Description"
)

Here, Filter(ProjectTasks, Status = "Active") delegates completely — the server returns only Active records. Then Search runs client-side against that already-filtered dataset. If "Active" represents 20% of your records, you've cut the local processing burden by 80%.

The more disciplined version uses a variable to control which results Search operates on:

// On app load or screen navigate:
ClearCollect(colActiveTasksCache, Filter(ProjectTasks, Status = "Active"));

// Gallery Items:
Search(colActiveTasksCache, SearchInput.Text, "Title", "Description")

This is an explicit cache pattern — you're acknowledging that colActiveTasksCache is a local snapshot. Add a refresh button that re-runs the ClearCollect so users can get updated data.

Pattern 5: Filtering by Collection Membership

A common scenario: you have a collection of IDs (maybe from a previous selection step) and you want to filter your main table to only records whose ID appears in that collection. The In operator doesn't delegate.

// NON-DELEGABLE
Filter(ProjectTasks, ID in colSelectedIDs)

The scalable pattern here depends on your data volume. If the collection is small (a few dozen items), you can accept client-side filtering and live with the 2,000 row local limit — just make sure your base filter is as restrictive as possible server-side:

// Delegable server filter first, then client-side membership check
Filter(
    Filter(ProjectTasks, Status = "Active", AssignedToEmail = User().Email),
    ID in colSelectedIDs
)

The outer filter is non-delegable, but the inner filter has already constrained the dataset on the server. You're doing a membership check on a manageable local set.

If the collection membership check is the primary query (large table, unknown size), the architectural answer is to move the list of IDs into the data source. Store a junction table in Dataverse, or add a relationship, so the server can perform the join natively.

---

Multi-Step Data Retrieval Architecture

Some business scenarios genuinely can't be expressed as a single delegable query. When you hit that wall, the answer isn't to give up — it's to architect a two-step retrieval.

The Cascade Pattern

Step 1: Run a fully delegable query to get a constrained set of IDs or keys. Step 2: Run a second query that uses those keys.

For example, imagine you need all Tasks belonging to Projects owned by the current user. Projects and Tasks are separate SharePoint lists.

// Step 1: Get current user's project IDs (delegable - simple text filter)
ClearCollect(
    colMyProjectIDs,
    ShowColumns(
        Filter(Projects, OwnerEmail = User().Email),
        "ID"
    )
);

// Step 2: This is where it gets tricky - we can't use In for delegation
// Better pattern: filter tasks by a denormalized field
ClearCollect(
    colMyTasks,
    Filter(Tasks, ProjectOwnerEmail = User().Email, Status <> "Completed")
);

The cascade pattern works cleanest when you denormalize data at write time. Rather than trying to join at query time (which Power Apps formulas aren't designed for), store the parent's key attributes on the child record when the relationship is created.

Using Concurrent for Parallel Queries

When you need multiple independent server queries, use Concurrent to run them in parallel rather than sequentially:

// Screen.OnVisible
Concurrent(
    ClearCollect(colMyActiveTasks, 
        Filter(Tasks, AssignedToEmail = User().Email, Status = "Active")),
    ClearCollect(colMyProjects, 
        Filter(Projects, OwnerEmail = User().Email, IsArchived = false)),
    ClearCollect(colMyNotifications,
        Filter(Notifications, RecipientEmail = User().Email, IsRead = false))
);

Three server queries run simultaneously. On a cold load, this can cut load time by 60-70% compared to sequential ClearCollect calls. Each individual query must be delegable — Concurrent doesn't help with delegation, only with parallelism.

---

Hands-On Exercise: Rewriting a Production-Scale Task Tracker

Let's put this all together. You have a SharePoint list called SupportTickets with these columns:

• Title (text)
• Status (choice: Open, In Progress, Resolved, Closed)
• Priority (choice: Low, Medium, High, Critical)
• AssignedTo (Person)
• AssignedToEmail (text — denormalized email, you've already added this)
• CreatedDate (date)
• DueDate (date)
• Department (text)
• TicketNotes (long text)
• InternalComments (long text)
• ResolutionCode (text)
• 25 more columns you don't need for this screen

The screen shows a filterable gallery with a search box, a status dropdown filter, and a "My Tickets" toggle.

Step 1: Set up date boundary variables in App.OnStart

// App.OnStart
Concurrent(
    Set(varToday, Today()),
    Set(varOverdueThreshold, Today()),
    Set(varCurrentUserEmail, User().Email)
);

Step 2: Define Named Formulas in App.Formulas

// App.Formulas

// Base query with column selection - filter then project
TicketsBaseSet = 
    ShowColumns(
        Filter(
            SupportTickets,
            Status <> "Closed"
        ),
        "ID",
        "Title", 
        "Status",
        "Priority",
        "AssignedToEmail",
        "DueDate",
        "Department",
        "CreatedDate"
    );

OverdueTickets = 
    ShowColumns(
        Filter(
            SupportTickets,
            Status <> "Closed",
            Status <> "Resolved",
            DueDate < varToday
        ),
        "ID",
        "Title",
        "Status", 
        "Priority",
        "AssignedToEmail",
        "DueDate",
        "Department"
    );

Step 3: Cache on screen load

In TicketScreen.OnVisible:

ClearCollect(
    colTicketCache,
    TicketsBaseSet
);

Step 4: Wire up the gallery with client-side search and filter

The gallery Items property:

Sort(
    Filter(
        Search(
            colTicketCache,
            SearchBox.Text,
            "Title",
            "Department"
        ),
        // Status filter - "All" shows everything, otherwise filter by selection
        StatusDropdown.Selected.Value = "All" 
            Or Status = StatusDropdown.Selected.Value,
        // My Tickets toggle
        !MyTicketsToggle.Value Or AssignedToEmail = varCurrentUserEmail
    ),
    // Sort overdue tickets to top, then by DueDate
    DueDate,
    SortOrder.Ascending
)

The key architecture here: all the heavy delegation work happens in TicketsBaseSet (server-side) and ClearCollect (populates the cache). The gallery formula does Search, Filter, and Sort against the local cache — which is fine because the cache is the already-server-filtered result.

Step 5: Add a refresh mechanism

Add a refresh icon button with this OnSelect:

ClearCollect(
    colTicketCache,
    TicketsBaseSet
);

Users control when they pull fresh data. Set a label showing "Last refreshed: " & Text(Now(), "[$-en-US]h:mm AM/PM") so they know how stale the cache is.

---

Common Mistakes & Troubleshooting

Mistake 1: Ignoring the yellow triangle because the app "works in testing"

Your test SharePoint list has 47 rows. Of course it works. Add 600 records and retest before every significant deployment. Make it a habit.

Mistake 2: Using ClearCollect as a delegation workaround without understanding the ceiling

// This does NOT solve delegation
ClearCollect(colAllTasks, ProjectTasks)

ClearCollect with an undelegated source still hits the 2,000 row limit. You haven't fixed delegation — you've just moved the truncated data into a collection. The fix is to ClearCollect against a delegable Filter, not against the raw table.

Mistake 3: Putting DateAdd inside Filter predicates

We covered this above, but it's worth repeating because it's so common:

// Wrong - DateAdd not delegable inside Filter
Filter(Tasks, DueDate < DateAdd(Today(), -7, TimeUnit.Days))

// Right - pre-compute
Set(varSevenDaysAgo, DateAdd(Today(), -7, TimeUnit.Days));
Filter(Tasks, DueDate < varSevenDaysAgo)

Mistake 4: Assuming Dataverse delegates everything

Dataverse is more capable than SharePoint for delegation, but it's not unlimited. Complex nested filters, certain computed column references, and non-indexed column searches can still fail to delegate. Always check delegation warnings even on Dataverse.

Mistake 5: Overusing Named Formulas for everything including UI state

Named Formulas are for data query definitions, not UI state management. Don't put SelectedColor = If(Toggle.Value, Blue, Gray) in App.Formulas — that belongs inline or in a variable. Use Named Formulas for data access patterns that need to be consistent and reusable across the app.

Debugging Delegation: The Practical Checklist

When you see wrong results at scale, work through this checklist:

1. Check every yellow triangle in your formula. Hover each one. Read the exact message.
2. Isolate the formula. Create a temporary label and paste just the Filter portion. Does it still have warnings?
3. Test with data volume. Set your delegation limit to 500 temporarily and load enough records to exceed it. Does your result count drop?
4. Check your connector's delegation table. Microsoft's documentation lists what each connector supports. SharePoint's list changed between connector versions.
5. Trace column types. Is the column you're filtering on the type you think it is? A Person column behaves differently than a Text column named "AssignedTo".

---

Summary & Next Steps

Delegation isn't a quirk to work around — it's the fundamental architecture of how Power Apps accesses data at scale. Once you internalize the model (server executes what it can; client handles the rest; the boundary is the connector's capability), you can write formulas that are predictable at any data volume.

The techniques from this lesson work together as a system:

• Named Formulas centralize your server-side queries, making delegation warnings visible and fixes reachable
• Explicit column selection with ShowColumns reduces payload and keeps your intent clear
• Date boundary variables pre-compute dynamic values so filter predicates stay as simple comparisons
• The cache pattern (ClearCollect against a delegable filter, then operate on the collection) gives you full local flexibility while keeping the server query honest
• Multi-step retrieval with Concurrent handles complex scenarios without sacrificing performance

The next area to tackle after mastering delegation is performance profiling — Power Apps Monitor (accessible via Advanced Tools in the Power Apps portal) shows you the actual network requests your app generates, including OData query strings and response sizes. Watching your delegation fixes translate into smaller, faster server queries in Monitor is both validating and educational.

From there, explore Dataverse views as a delegation pattern: define complex filter criteria server-side as a Dataverse view, then reference the view from your canvas app. The view executes entirely on the server, and your app just binds to it. It's the most server-side you can push filtering logic without writing custom connectors.

You now have the tools to audit every formula in your canvas apps and make evidence-based decisions about what runs on the server versus the client. Use them before you go to production, not after your users start complaining about missing data.