Linkedin Facebook Instagram

Understand delegation in Power Apps

Picture of Daniel Cieckiewicz

Daniel Cieckiewicz

Delegation, delegation, delegation.. The heck is it? Why should you care? And the most important question – how to work with Power Apps delegation?

All these questions will be answered in todays article about Power Apps Delegation! I will walk you through what is delegation, how it works and how to deal with delegation problems and challenges.

Take a break, grab some coffee and let’s start 🙂

Table of Contents

What is Power Apps delegation?

As always, how we do it? Before I will explain it my way, let’s hear what Microsoft says about delegation in Power Apps.

Delegation is where the expressiveness of Power Apps formulas meets the need to minimize data moving over the network. In short, Power Apps will delegate the processing of data to the data source, rather than moving the data to the app for processing locally.

Microsoft

Now, I think you should have basic understanding what delegation really is – but I will try to explain it my way, anyway. 

We are delegating execution of filtering and searching for records to the datasource. So, it means that this whole query will not burden Power Apps itself and will be executed on the side of a datasource. In other words the data we are looking for will be already filtered on the side of a datasource and the result will be transferred to Power Apps. So, we get everything done and Power Apps side just receives the data we want.

What is worth adding here, for better understanding is that Microsoft Power Fx language is very powerful tool and not everything that can be expressed in a Power Apps formula can be delegated to every data source. So, it means that we need delegation to work with datasources, because without delegation our queries would stop working correctly (if we would try to execute them on the side of a datasource). Datasources wouldn’t know what functions we use, BECAUSE many of these function are just not there! This also includes SQL Server.

Power Apps Canvas Apps have implemented many, many functions and they are not present in different datasources, so we only use common and standard functions in delegation for our query to execute on the side of a datasource. If we would use custom Microsoft”s Power Fx functions that only Power Apps has implemented, query could not be executed on the side of a datasource and would be fully executed on the side of Power Apps Canvas App instead.

I hope this explanation will be enough for this topic 🙂

If you want to read more, please visit Delegation in Power Apps.

How to know what is delegable?

There is no need to repeat the words of Microsoft in this case so let’s just check what they said:

To make it easier to know what is and isn't being delegated, Power Apps provides warning (yellow triangle) when you create a formula that contains something that can't be delegated. Delegation warnings appear only on formulas that operate on delegable data sources. If you don't see a warning and you believe your formula isn't being properly delegated, check the type of data source against the list of delegable data sources earlier in this topic.

Microsoft

Power Apps delegable datasources, functions and limits

To fully understand how to work with delegation you must know what functions and datasources you can use.

Remember:

Only certain functions in Power Apps Canvas Apps are delegable. If you will use just one not delegable function in your query, then whole query won't be delegable. So, it is very important that you compose your query by carefully picking delegable functions.

Power Universe

Delegable datasources

Delegable datasources are:

  • Microsoft Dataverse
  • SharePoint
  • SQL Server
  • Saleforce

As Microsoft also says:

Imported Excel workbooks (using the Add static data to your app data source), collections, and tables stored in context variables don't require delegation. All of this data is already in memory, and the full Power Apps language can be applied.

Microsoft

So, these datasources are delegable. If you will work with them in the future, you know that you can play with delegation in these cases. As Microsoft said, Excel workbooks, collections and tables stored within then application does not require delegation, because they are already stored in Power Apps Canvas App, so for filtering you can use whatever function you like.

Delegable functions

It gets more and more interesting 🙂 Delegable functions. What are they?

In short, delegable functions are:

  • And (including &&), Or (including ||), Not (including !)

  • In

  • =, <>, >=, <=, >, <

  • +, –

  • TrimEnds

  • IsBlank

  • StartsWith, EndsWith (these are very important functions, I use them very often)

  • Constant values that are the same across all records, such as control properties and global and context variables.

  • Sort and SortByColumns

  • Sum, Average, Min, and Max (but only a limited number of data sources support this delegation at this time – you must check the documentation about these datasources: Delegable datasources)

Delegable functions - explanation

Delegable functions are these functions that you can freely use across the query. They won’t cause any problems if you use them accordingly to documentation. Because, look – you want to use, StartsWith function and you want to filter person or group field – will it work for such a field? The answer is Yes 🙂 But, for example, for choice field this functions is not delegable. Check pictures below:

This is StartsWith function used with person field:

And this is StartsWith function used with choice field:

StartsWith function also works with single line of text or number fields, so you must learn when these functions can be used. So if you want to filter choice field it is very good to create for example single line of text field next to the choice field and update it like you would update a choice field. This field would be called [TECHNICAL] field and would be used only for filtering.

In summary delegable functions are these which can be used for delegation. If you are looking for wider explanation about functions please visit Delegable functions.

Delegation - our project we will be working with

Before we start figuring out how to use delegable functions and what tips do I have for you, you must know on what application we will be working.

To make it easier for you I used Power Apps template prepared by Microsoft – Leave Requests app. I modified the application a little bit and I added more filters and obviously a datasource (because default app just works with collections). In my case it is SharePoint, because many applications are developed using this datasource.

Below, please check a screenshot of the main screen we will be working with:

The application is not pretty, not pretty at all, but it must be functional at first 🙂 We focus on delegation in this case so let’s do that.

We have a gallery where all leaves are listed. Then, as you can see we have four filters. At the top we have Status filter and on the right we have three more filters: text box and two date boxes.

As I said we have 4 filters (fields in app) but we actually filter using 7 different fields or variables:

  • isApprover variable ( _managerView variable in a code)
  • Status
  • Title of a ticket
  • Requestor
  • Approver
  • Date From (leave start date)
  • Date To (leave end date)
 
So, we can really tell that this query is quite complex and we will make it delegable. As you can see there are no yellow triangles of d*ath, brrrr..

Lest see our datasource as well:

We have:

  • Title
  • Requester
  • Status
  • From
  • To
  • Submit To
  • Type of Leave (which is skipped in this query)
  • Details
There are 100 records in this list.

Delegable and non-delegable queries

For this article I prepared three different queries. One of them is standard one – not delegable and two more are delegable just to show you the ideas of delegable queries. 

All of them are doing the same job – they are filtering the same columns and they are using the same filter columns.

Check these queries below:

Non-delegable query using If and Filter functions

				
					Sort(
    If(
        _requestTypeFilter = "All",
        Filter(
            Leaves,
            If(
                _managerView,
                SubmitTo.Email = _myProfile.Mail,
                Requester.Email = _myProfile.Mail
            ),
            From >= FilterFrom.SelectedDate,
            To <= FilterTo.SelectedDate,
            StartsWith(
                Title,
                SearchBox.Text
            ) || StartsWith(
                Requester.DisplayName,
                SearchBox.Text
            ) || StartsWith(
                SubmitTo.DisplayName,
                SearchBox.Text
            )
        ),
        Filter(
            Leaves,
            Status.Value = _requestTypeFilter && If(
                _managerView,
                SubmitTo.Email = _myProfile.Mail,
                Requester.Email = _myProfile.Mail
            ),
            From >= FilterFrom.SelectedDate,
            To <= FilterTo.SelectedDate,
            StartsWith(
                Title,
                SearchBox.Text
            ) || StartsWith(
                Requester.DisplayName,
                SearchBox.Text
            ) || StartsWith(
                SubmitTo.DisplayName,
                SearchBox.Text
            )
        )
    ),
    ID,
    Ascending
)

This query is non-delegable, because it uses If() statement inside of a Filter query function. The rest of the query is delegable but this one function makes whole query non-delegable.

Delegable query using If and Filter functions

				
					If(
    _managerView,
    If(
        _requestTypeFilter = "All",
        Sort(
            Filter(
                Leaves,
                SubmitTo.Email = _myProfile.Mail,
                From >= FilterFrom.SelectedDate,
                To <= FilterTo.SelectedDate,
                StartsWith(
                    Title,
                    SearchBox.Text
                ) || StartsWith(
                    Requester.DisplayName,
                    SearchBox.Text
                ) || StartsWith(
                    SubmitTo.DisplayName,
                    SearchBox.Text
                )
            ),
            ID,
            Ascending
        ),
        Sort(
            Filter(
                Leaves,
                SubmitTo.Email = _myProfile.Mail,
                Status.Value = _requestTypeFilter,
                From >= FilterFrom.SelectedDate,
                To <= FilterTo.SelectedDate,
                StartsWith(
                    Title,
                    SearchBox.Text
                ) || StartsWith(
                    Requester.DisplayName,
                    SearchBox.Text
                ) || StartsWith(
                    SubmitTo.DisplayName,
                    SearchBox.Text
                )
            ),
            ID,
            Ascending
        )
    ),
    If(
        _requestTypeFilter = "All",
        Sort(
            Filter(
                Leaves,
                Requester.Email = _myProfile.Mail,
                From >= FilterFrom.SelectedDate,
                To <= FilterTo.SelectedDate,
                StartsWith(
                    Title,
                    SearchBox.Text
                ) || StartsWith(
                    Requester.DisplayName,
                    SearchBox.Text
                ) || StartsWith(
                    SubmitTo.DisplayName,
                    SearchBox.Text
                )
            ),
            ID,
            Ascending
        ),
        Sort(
            Filter(
                Leaves,
                Requester.Email = _myProfile.Mail,
                Status.Value = _requestTypeFilter,
                From >= FilterFrom.SelectedDate,
                To <= FilterTo.SelectedDate,
                Status.Value = _requestTypeFilter,
                StartsWith(
                    Title,
                    SearchBox.Text
                ) || StartsWith(
                    Requester.DisplayName,
                    SearchBox.Text
                ) || StartsWith(
                    SubmitTo.DisplayName,
                    SearchBox.Text
                )
            ),
            ID,
            Ascending
        )
    )
)

This query is delegable. Filter query uses only functions that are delegable for SharePoint datasource. All filters that use choice or must be filtered outside of a Filter function are not present in Filter itself. They are kicked away from the Filter function and they are placed at the top of the query. 

_managerView variable could be implemented in the query as well as other fields that use StartsWith function but this approach I see very often in applications so I left it here as it was implemented by Microsoft.

Look how I put Sort function in this query – Sort function used at the top of the whole query ruins delegation in Power Apps!

Delegable query using If, Switch and Filter functions

				
					
    Switch(
        _managerView,
        true,
        If(
            _requestTypeFilter = "All",
            Sort(Filter(
                Leaves,
                SubmitTo.Email = _myProfile.Mail,
                From >= FilterFrom.SelectedDate,
                To <= FilterTo.SelectedDate,
                StartsWith(
                    SubmitTo.Email,
                    SearchBox.Text
                ),
                StartsWith(
                    Title,
                    SearchBox.Text
                ) || StartsWith(
                    Requester.DisplayName,
                    SearchBox.Text
                ) || StartsWith(
                    SubmitTo.DisplayName,
                    SearchBox.Text
                )
            ),
            ID,
            Ascending),
            Sort(Filter(
                Leaves,
                SubmitTo.Email = _myProfile.Mail,
                Status.Value = _requestTypeFilter,
                From >= FilterFrom.SelectedDate,
                To <= FilterTo.SelectedDate,
                StartsWith(
                    Title,
                    SearchBox.Text
                ) || StartsWith(
                    Requester.DisplayName,
                    SearchBox.Text
                ) || StartsWith(
                    SubmitTo.DisplayName,
                    SearchBox.Text
                )
            ),
            ID,
            Ascending)
        ),
        false,
        If(
            _requestTypeFilter = "All",
            Sort(Filter(
                Leaves,
                Requester.Email = _myProfile.Mail,
                From >= FilterFrom.SelectedDate,
                To <= FilterTo.SelectedDate,
                StartsWith(
                    Title,
                    SearchBox.Text
                ) || StartsWith(
                    Requester.DisplayName,
                    SearchBox.Text
                ) || StartsWith(
                    SubmitTo.DisplayName,
                    SearchBox.Text
                )
            ),
            ID,
            Ascending),
            Sort(Filter(
                Leaves,
                Requester.Email = _myProfile.Mail,
                Status.Value = _requestTypeFilter,
                From >= FilterFrom.SelectedDate,
                To <= FilterTo.SelectedDate,
                StartsWith(
                    Title,
                    SearchBox.Text
                ) || StartsWith(
                    Requester.DisplayName,
                    SearchBox.Text
                ) || StartsWith(
                    SubmitTo.DisplayName,
                    SearchBox.Text
                )
            ),
            ID,
            Ascending)
        ),
        If(
            _requestTypeFilter = "All",
            Sort(Filter(
                Leaves,
                Requester.Email = _myProfile.Mail,
                From >= FilterFrom.SelectedDate,
                To <= FilterTo.SelectedDate,
                StartsWith(
                    Title,
                    SearchBox.Text
                ) || StartsWith(
                    Requester.DisplayName,
                    SearchBox.Text
                ) || StartsWith(
                    SubmitTo.DisplayName,
                    SearchBox.Text
                )
            ),
            ID,
            Ascending),
            Sort(Filter(
                Leaves,
                Requester.Email = _myProfile.Mail,
                Status.Value = _requestTypeFilter,
                From >= FilterFrom.SelectedDate,
                To <= FilterTo.SelectedDate,
                StartsWith(
                    Title,
                    SearchBox.Text
                ) || StartsWith(
                    Requester.DisplayName,
                    SearchBox.Text
                ) || StartsWith(
                    SubmitTo.DisplayName,
                    SearchBox.Text
                )
            ),
            ID,
            Ascending)
        )
    )

This query is delegable. Filter query uses only functions that are delegable for SharePoint datasource. As in previous example. The only difference is that we use switch function for filtering _managerView variable instead of if().

As for sorting it is the same story.

General advices for delegation

Think - don't always do what Clients want

Remember to validate requirements, before you agree on them and start implementing the app. Clients very often want many features they will not even use.

Instead of that, please take a moment and meet with the Client and try to understand their real needs and what filtering (in this case) they will really need. I am not saying that you should not do what Client wants you to do, but there is a difference between taking requirements and blindly implement everything there is and checking and understanding what you will be doing. Instead of that I would rather spend an hour to confirm what is really needed. Especially in the case when you were not involved in the process of analysis.

This approach is very good for both sides. For you, and for the Client. It is good for you, because you now know what you must do and what must be delivered – what is really needed. And it is also good for the Client because they feel understood and they know that the product will be developed the right way.

I am real believer of that approach. I always try to understand what must be delivered, why it must be delivered and what is the purpose of the whole project.

Use StartsWith function for text, number and person fields

One text box filter is enough to cover all these needs. StartsWith function can be used like I showed you in the code previously. Single line of text, numbers and person fields – these can be handled within one filter box. 

I have a homework for you. Go and check if Currency field type is delegable with StartsWith function 🙂

In the application I can play with this filter and it works perfectly!

Here we filter a gallery and we look for leaves submitted to Lee Gu:

And here we filter a gallery for a leave titled “Summer vacations”

As you can see it works really great!

Use If and Switch functions at the top of the whole query (if you really need them)

If and switch are non-delegable functions, you can’t use them inside of a Filter query. If you must check something (like choice value of a field) do this outside of a Filter query, but don’t act tough – don’t implement 5 ifs at the top of the query because you will quickly get lost. As I said previously – for choices you can always implement [TECHNICAL] column which will hold actual choice value only for filtering. Then you can filter this column in your text box filter. There is no need of implementing ifs and ifs and even more ifs 🙂 

Think twice before you start implementing your datasource – what you will need and what is the purpose!

Try not to use many calculated columns

Calculated columns are really usefull in Sharepoint or Dataverse but these will always be non-delegable. Remember about that when you create data model for your application.

Don't change the limits if you can

Default limit for delegation in Power Apps is 500. 100 records is the number that you will get when you will create delegable query. If you will need more data, don’t worry, it will be transferred into Power Apps from a datasource when you need them. After initial load you will get your first 100 and if you will scroll down you will get another 100 and so on.

What is more important, if you don’t use delegable functions within your query, 500 is maximum number of records that you will get in the app. Even if datasource holds 10000 records, you will get only first 500. 

Remember, even if you change the limit to 2000 (which is maximum) and datasource exceeds this number, you will never get more records than that. So, when you will work with apps that have more that 2000 records delegation is a MUST!

Please read what Microsoft says about delegation limits in their article, it is very interesting.

500 is the default number of records, but you can change this number for an entire app. In some cases, you'll know that 2,000 (or 1,000 or 1,500) will satisfy the needs of your scenario. With care, you can increase this number to fit your scenario. As you increase this number, your app's performance may degrade, especially for wide tables with lots of columns. Still, the best answer is to delegate as much as you can. To ensure that your app can scale to large data sets, reduce this setting down to 1. Anything that can't be delegated returns a single record, which should be easy to detect when testing your app. This can help avoid surprises when trying to take a proof-of-concept app to production.

Microsoft

There will be situations when you will not need to implement delegable query in your app. For example lookup lists for other fields or really small lists/views that hold only couple of records. But I personally think that you should try and work with delegation as many times as possible. Practice will make you better developer. When you will be avoiding working with delegation, you will never be able to fully understand how to deal with it and at the worse scenario you will probably not even try – even if there is a MUST for it. Be a professional and if there is a need for delegable query – just implement it!

Use sorting close with Filter function

Don’t use Sorting at the top of the query! If you didn’t know that sorting at the top of ifs or switches ruins whole query. It must be used within the query, close to the Filter function! It is very, very important.

Test your changes

Always test your changes. It is very easy to produce bugs in the code, especially when the app is huge. Testing is very important here. It is also very good practice to work with queries in Power Apps with limit for delegation set to 1 – it is very hard to notice delegation problems when you work on 3 test records and the limit is 500. The problem with Sorting is not displayed on the screen! You will not see any popup or underline. The best way to check if your query is delegable is setting delegation limit to 1 and create more than 1 record. If all records are retrieved, then your query is delegable 🙂

Delegation performance (efficiency)

I searched for this topic across the Internet and I didn’t find any information about performance of delegable queries.

Did you think if delegable query is more efficient than non delegable one?

Let’s test it out!

In this place I will call Query 1, 2 and 3 – as I presented them a little bit higher. Query 1 is non-delegable. Query 2 is this one with ifs only and finally Query 3 is this one with switch and ifs.

Performance of the 1 Query (non-delegable).

Loading (entering the screen for the first time) - performance

The performance of the query is very good (just for 100 records). Small list gives possibilities to really quickly filter datasource. For this particular example this query is the fastest from delegable queries. And I am really surprised. But the funny thing is presented in the next paragraph.

Now I present the numbers on how long this Query 1 was loading to present values:

Here you can see that non-delegable Filter query loaded in 228ms.

Playing with filters - performance

I couldn’t believe the results but let’s see them:

Playing with filters – which means changing dates, using text box and status filters – takes only 1ms for Power Apps to retrieve values. That’s crazy! I will leave that here because you may think – non-delegable query are GOAT 😀 No, no, no.. 

Performance of the 2 Query (delegable).

Loading (entering the screen for the first time) - performance

The performance of the query is also very good. The numbers are almost the same. So, just check screen below.

Here you can see that delegable Filter Query 2 loaded in 204ms.

Playing with filters - performance

Playing with filters is much faster than first load. The average speed is around 130ms. I think no comment is required.

Performance of the 3 Query (delegable).

Loading (entering the screen for the first time) - performance

The performance of the third query is very good as well. The numbers are almost the same. So, just check screen below:

Here you can see that delegable Filter Query 2 loaded in 255ms.

Playing with filters - performance

Playing with filters is as well much faster than first load. The average speed is around 120ms. But sometimes it needs up to 200ms (in this particular example).

I also checked the performance for 400 records and the results were almost the same – all queries executed in around 330ms. 

In summary, performance of delegable queries for 100 records for SharePoint are almost the same. Concededly, non-delegable query was much quicker when playing with filters but this difference for User is not really measurable.

Summary

Whoooh. It is by far my longest article I ever wrote (I was writing articles before Power Universe as well).

All you need to know is – work with delegation as much as possible, at every scenario. Secondly, don’t be afraid and play with different scenarios and queries and choose whatever works best for you. And thirdly, check performance of your queries using “Monitor” feature. It is very important.

And finally, do the homework! 🙂

Thank you for your time, and for reading this article. Feel free to rate this article down here and comment if you liked it. If you have any questions feel free to contact me (via contact@poweruniverse.org), but first, you may be interested in joining a Newsletter? Hmm? (Sign up here) If you already did, woow, thanks, thanks a lot 🙂 

Via Newsletter I am sharing with you insights of my work, plans for upcoming weeks and knowledge about Power Platform Universe and IT world 🙂 If you are interested feel free to join! To every person who joins I am going to send latest Newsletter as well!

See ya!

About the author

Daniel Ciećkiewicz

FOUNDER

I am a Senior Power Platform Consultant focused on Dataverse, Power Apps, and Power Automate. I was also a Team Leader responsible for the Power Platform Team and their development paths. 

In my private life, I like video games, sports, learning & gaining knowledge, and a taste of good Scotch Whisky! 

Ooo, I almost forgot, I love our Polish Tatra Mountains!

Categories
Top 3 articles
Newest articles
These May also interest you:
Tagged
5 2 votes
Article Rating
Subscribe
Notify of
guest

4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Vorg
Vorg
1 year ago

Great article! Thx-

3
Reply
Daniel Cieckiewicz
Author
Daniel Cieckiewicz
1 year ago
Reply to  Vorg

Thanks! 🙂

0
Reply
Tom
Tom
1 year ago

Finally! Now I understand the magic of delegation 🙂 Thanks!

2
Reply
Daniel Cieckiewicz
Author
Daniel Cieckiewicz
1 year ago
Reply to  Tom

Thank you for you comment! I hope from now on you will create only delegated queries 🙂

0
Reply
wpdiscuz   wpDiscuz

Power Platform Universe

Home  |  All Articles

Privacy Policy  |  Cookies Policy

Facebook-f Linkedin Instagram
4
0
Would love your thoughts, please comment.x
()
x