[Power Apps] SortByColumns Delegation Warning Guide: How to handle dynamic sorting limitations

In this article, I investigate the delegation limitations of “Sort” and “SortByColumns” functions in Power Apps, specifically focusing on dynamic sorting scenarios.

Delegation and Data Row Limit
Advance preparations
Case where delegation is NOT possible with “SortByColumns”
1. Put a formula in the 2nd argument
2. Put a formula in the 3rd argument
Case where delegation is NOT possible with “Sort”
1. Include a formula in the second argument
2. Include object type properties in the formula
Conclusion
Extras 1：It’s possible to delegate but the data limit influences the result when using “With”
Extras 2：Combination with non-delegable functions like “AddColumns”
Related Articles

Delegation and Data Row Limit

Before we move on, let’s briefly review “Delegation” and “Data Row Limit”.

Delegation means “leaving the processing such as ‘Sort’ and ‘Filter’ to the data source”.

The idea is that it’s more efficient to have the data source handle the processing rather than retrieving all data into Power Apps first.

The “Data Row Limit” is the maximum number of records that Power Apps retrieves from the data source, capped at 2000.

No more than this limit will be loaded. Therefore, if you assign a large dataset to a variable or combo box, only data up to the set limit is retrieved.

However, “Galleries” and “Data tables” are exceptions: they can display more data than the limit if the query consists only of delegable functions.

※More detail here.

Power Apps 委任とデータ行の制限（500件～2000件）の関係を徹底解説｜委任とデータ行の制限は分けて考える

これまで委任とデータ行の制限（既定は500件）が絡まると、いまいち理解がこんがらがってしまっていたけど、改めて図にしてみるとわかりやすかったのでメモ。前提前提として今回はこんなリストがあって、このリストを、わかりやすいようにデータ行の制限を...

Advance preparations

Preparation is the same as before, with a SharePoint list like this,

and a Dataverse table as below.

To make it easier to see whether these data will be delegated, set the Power Apps “Data Row Limit” to 5.

Case where delegation is NOT possible with “SortByColumns”

Let’s see the result with SortByColumns function which is NOT able to delegate to data source.

Put a formula in the 2nd argument

First, “SortByColumns” cannot delegate if the second argument contains a formula.
※The gallery function cannot be used due to the non-delegable formula, and the “FFFF” item which should appear at the top of the list is not displayed because it hits the Data Row Limit.

Unfortunately… the same goes for Dataverse.

That’s too bad… It doesn’t work even if you pre-calculate it into a text-type variable with “With”.

So, “when you want to change the sort column dynamically based on conditions while maintaining delegation”, it seems that the only way is to nest multiple “SortByColumns” functions.

Well, it’s fine to write two or so… but when there are more conditions, it’ll be hard to read and write.

By the way, it is possible to put an expression in the third argument (ascending or descending order).

Put a formula in the 3rd argument

Then, the method I introduced in the previous article, “Specifying a table as the third argument” cannot be delegated either.
※Records with “Num” column value 6 won’t be displayed because the sorting is performed locally after retrieving the data.

This also applies to Dataverse.

I know it is a rare case to pass a table, but you should be careful that there is no delegation warning.

Case where delegation is NOT possible with “Sort”

Next, we’ll see the case that is NOT possible to be delegated with “Sort”

Include a formula in the second argument

As I mentioned in the previous article, “Sort” also cannot be delegated when the second argument contains a formula.

The same cannot be delegated in Dataverse.

According to the official document below, it says “Delegation is no longer possible when operators or functions are used.”

Understand delegation in a canvas app - Power Apps

Learn about how to use delegation to process large data sets efficiently in a canvas app.

When you want to delegate the processing to the data source and still want to use functions, “Sort” by itself can only specify one column, similar to “SortByColumns”.

For your information, when specifying multiple columns, you can “sort by multiple columns while delegating” by nesting two “Sort” functions.

Include object type properties in the formula

Also, accessing object properties in the second argument of the Sort function does not allow delegation (The example below shows accessing a Choice column in SharePoint).
※Probably because “.” is considered an operator that accesses the property.

Conclusion

So, when you want to sort while maintaining delegation, both “Sort” and “SortByColumns” are influenced by restrictions.

When you need sorting to handle large datasets, note that it may lead to unexpected results if delegation is ignored.

※There may be other issues or cases caused by delegation.

Extras 1：It’s possible to delegate but the data limit influences the result when using “With”

If you use “With” for the Item property of the gallery as below, only the data up to the Data Row Limit will be displayed regardless of delegation.

Note that even if you do not use the value you assigned to a local variable in With, it will be affected by the Data Row Limit.

Extras 2：Combination with non-delegable functions like “AddColumns”

As I mentioned in the past article, “AddColumns” is non-delegable but no warnings are shown.

Why AddColumns breaks Gallery Paging (Infinite Scroll) without Delegation Warnings

I recently encountered a frustrating issue where a Power Apps Gallery would stop loading items at 500 (or 2000) rows, ev...

If you wrap the data with “AddColumns”, “sorting by the added columns” is NOT possible (delegation-wise), but you can bring “the data after sorting” up to the limit.