How to Avoid CountIf Delegation Issue in Power Apps

I have come across many use cases in my apps where I have to display the conditional count of the items, for example: number of invoices pending approval, number of open orders etc. Power Apps provides a conditional count function for this use caseCountIf(Table,LogicalFormula).

Issue

The countIf function in power apps is however non-delegable, meaning your app is not able to delegate the data processing to the data source (SQL, SharePoint etc). The function will start giving out wrong counts when the number of rows in the data source exceeds the data row limits for non-delegable functions (Understand delegation in a canvas app). This happens because the if condition applies only to the first n fetched rows. This limit can be changed in the app setting at the expense of app performance (PowerApps data row limit for non-delegable queries) with a maximum limit of around 2000 rows.

Note*: Use of non delegable function is usually indicated in the formula bar with a blue underline and yellow warning on the object. However in this particular scenario the warning is not shown and hence can be misleading.

Solution

This issue can be overcome using the combination of sum forAlland filter functions as shown below.

Sum(ForAll(Filter('YourDataSource',FilterCondition),1),Value)

Sources

Power Apps Formula reference

Functions, signals, and enumerations - Power Apps

Power apps Community

Power Apps Community