Power Apps delegation is the processing of a query within the data source itself rather than by Power Apps.  Delegable queries are far more efficient because the query is executed quickly on the server and the full set of results are sent to Power Apps

Delegable Queries

In the example below.  Power Apps sends a delegable query to the data source requesting all the blue records.  Because the query is delegable, the data source processes the query and returns the results that are displayed in the app

Power Apps Delegation

Non-delegable Queries

A non-delegable query cannot be processed by the data source.  The data source simply sends the first 500 queries to Power Apps.  Power Apps then executes the query against the data it receives and displays the results, which is only a subset of the requested data

The default Canvas apps delegation limit is 500 records.  This can be increased up to 2000, but non-delegable queries should be avoided for the following reasons:

  • An apps’ performance can be impacted by the delay waiting for the data to be passed over the network
  • When the data is received, executing the query will take longer on a relatively low spec user device such as a mobile phone or tablet, rather than a high-end cloud server
  • Most importantly, for non-delegable queries, Power Apps sets a limit to the number records that will be transferred from the data source. This is defined in the settings section of each Canvas App.  The setting is set to 500 by default and may be raised to a maximum of 2000.  If there are a greater number of records in the data source than the defined delegation limit, then the number of records transferred will be truncated.  Hence the data processed and the results returned may well be incomplete

When a query isn’t delegable, blue lines are sometimes displayed against the non-delegable section in the formula bar, however this isn’t always the case.  Commonly used formulas such a Collect, ClearCollect and ForAll are not delegable but beware, the blue warning lines aren’t displayed for these functions

Power Apps Delegation

An added complication is that some formulas can be partially delegable.  Structuring formulas properly is important to ensure a query returns the correct results

To look at some examples, I’ve created an app connected to a Dataverse table containing the 92 counties in Great Britain and Northern Ireland.  My aim is to display the 7 counties that start with the letter B.  For demonstration purposes, the delegation limit of the app is set to 10

 

I have conducted 5 queries and each of the results are shown below

1) Directly From the Dataverse Table

To list all the counties in Great Britain and Northern Ireland sorted alphabetically directly from the table, set the items property of the gallery to the following:

Sort(Counties,Name)

All the 92 counties are displayed and there are no delegation issues.  The query is processed in its entirety on the Dataverse server and the results returned to Power Apps

test 1

2) Directly from the Dataverse Table Via a Filter

StartsWith and Filter are fully delegable with Dataverse.  Sort may be delegable depending on its parameters, which is the case with this query

Sort(Filter(Counties,StartsWith(Name,”B”)))

This formula produces the desired results and the 7 counties starting with B are displayed

test 2

3) Add the Data to a Collection and then Display

The third test is to pre-load the county data into a collection.  This can be done with the OnSelect property of a button set to the first formula below.  Then display the results in a gallery using the second formula as the galleries Items property

ClearCollect(colCounties,Counties)

Sort(colCounties,Name)

Only 10 records are displayed!  This is because the ClearCollect (and Collect) function are not delegable so only the first 10 records out of the full set of 92 are sent to Power Apps.  The blue lines in the formula bar that warn when a formula isn’t delegable aren’t displayed, but don’t be misled, a collection created by loading a data set that is larger than the delegation limit will be incomplete

test 3

4) Add the Data Into a Collection and then filter

This is done using the same ClearCollect formula above and then the formula below is used in the Items property of a gallery to display the results

Sort(Filter(colCounties,StartWith(Name,”B”),Name)

As you’d expect, In this case only 3 records are returned.  This is because the source I am using is a collection of only 10 records, and of those records only 3 records start with B so the result is incomplete

test 4

5) Filter the Dataverse Table and Add to a Collection

The formula below is added to the OnSelect property of a button.  The results is displayed in a gallery with the Items property set to the second formula

ClearCollect(Filter(colFilteredCounties,StartWith(Name,”B”))

Sort(colFilteredCounties,Name)

This displays all the 7 counties that begin with the letter B

The reason for this is that this query is partially delegable.  Dataverse formulas are calculated from the inside out.  The Filter section is executed first and because it is delegable, all the 7 counties that start with B are returned.  Those 7 records are then added to the collection and because the record count is below the delegation limit of 10 the returned data is complete.  If the delegation limit was reduced to 5 then the data would have only 5 records and not be incomplete

test 5

Summary

Thinking through both the structure of formulas and the data on which they will be used is important

For a small set of data, such as the Counties table with 92 records, if the app delegation limit is set to 500, even a non-delegable query is not going to be a problem.  For a much larger data set, for example incidents being raised at a corporate helpdesk, a non-delegable query could well return incomplete data.  Making the query partially delegable (for example filtering for open cases being managed by a specific helpdesk advisor) reduces the risk of incomplete data

There are advantages to loading data into collections and working on it locally rather than continually interrogating and updating the data source direct.  However, care does need to be taken with this approach.  The above examples demonstrate how this may create problems with delegation unless formulas are structured correctly

Whilst I have used Dataverse in my examples, the same principles apply to other data sources such as SharePoint.  The Power Apps functions that are delegable can vary by data source.  Full details can be found here

Related Posts

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top