Avoid CountRows on Large Lists

The Problem

CountRows() is not delegable when used with SharePoint, SQL Server, or Dataverse (in some expressions). Power Apps applies the delegation limit (default: 500 rows) and counts only what it retrieves, without any warning at runtime.

If your list has 2,000 items, CountRows(Filter(Orders, Status = "Pending")) will return a number between 0 and 500. It looks correct. It is not.

Bad Approach

// This silently caps at 500 rows
Set(varOrderCount, CountRows(Filter(Orders, Status = "Pending")));

// Displayed in a label -- looks fine, is wrong
Label.Text = varOrderCount

The formula compiles without errors. The app runs without warnings. Your count is just... wrong.

Good Approach

Option A - Pre-computed count column (recommended for SharePoint):

Create a separate "Counters" list with one row per status. Use a Power Automate flow triggered on item creation/modification to increment or decrement the count.

// Read a single, always-accurate row
Set(
    varOrderCount,
    LookUp(Counters, Category = "PendingOrders").Count
);

Option B - SharePoint REST API via Power Automate:

// Flow HTTP action
GET _api/web/lists/getbytitle('Orders')/ItemCount

// Or with a filter (uses server-side count)
GET _api/web/lists/getbytitle('Orders')/items?$filter=Status eq 'Pending'&$top=5000&$select=Id

Then store the result in a collection or variable via a flow response.

Key Takeaway

Never trust CountRows() on large external data sources. Pre-compute counts server-side or use API calls that support server-side aggregation.