Delegation errors that were not there before if any calculation is in the filter

[HansHeintz]

Hi,

This

    ,ClearCollect
            (
             CachedLastDierExtra
             ,Filter(
                     DierExtra
                     ,DocaNummer >= MaxDierID-1999  
                    )
                )

(dataverse table, whole number field)

did not result in delegation errors before (on the >= line) but does now.

If I remove -1999 the delegation error goes away, so it's an unnecessary error, PA could interpret the calculation first end the filter after.I could store MaxDierID-1999 into a variable first of course but it seems akward.

What is the most subtle way to deal with this? Or is it a temporary bug that will go away?

[Clue]

Hello! Based on my analysis, the issue you're facing is likely related to the collection rather than Dataverse. When you use Collect() to gather items, you're essentially instructing the system to store the data locally, which in turn disables delegation. As you may know, delegation involves offloading the task of sorting items to Microsoft's servers.

To address this, I've designed a simple test, as shown in the image below. I've calculated the size of the items, ensuring they are larger than 5. For one box (Direct), I've employed the CountRows() function over the Filter() function. In contrast, for the other box, I've used CountRows() around the collection of items (colItems), which I obtain by pressing a button. In the settings, I've configured the "Data row limit" to 1, which limits the retrieval of rows to only one for all non-delegable functions. Since Collect() and ClearCollect() fetch items locally in the browser, they conform to this limit and consequently collect just a single item.

In summary, to avoid delegation issues, try to minimize the use of Collect() and ClearCollect(). Another useful tip is to tinker with the "Data row limit" in the settings, as PowerApps might not always accurately indicate when a function is non-delegable. Testing your app with this setting set to 1 can help you improve the performance of your app! Keep in mind that if PowerApps states a function is non-delegable, it's accurate; however, if it claims a function is delegable, it might not be entirely reliable.

[HansHeintz]

Hi,

Thanks for your reaction!

Since my app is riddled with collections avoideing them is no option alas.

ClearCollect
            (
             CachedLastDierExtra
             ,With({lMax: MaxDierID-1999}
              ,Filter(
                     DierExtra
                     ,DocaNummer >= lMax  
                    )
                 )   
                )

Does solve the problem, no warning and functions as should be.

I do find that since last week my app has many delegation errors that were not there before. Not only concerning collections. Also I I do a CountIf or Filter on a table with three conditions I get a warning

So 

condition1 And condition2 And condition3

And then if condition3 is something like

planstatus=-1

I *do* get the warning

but if condition3 is just

BoolFieldValue

I don't

Something definetely changed.

[Clue]

Hi @HansHeintz, no problem.

Sadly the issue isn't that delegation warnings are starting to "appear" more that Microsoft is actually starting to show them correctly. You probably had them before they just weren't popping up correctly. The delegation issue is due to the collections sadly. Attempt to the best of your abilities to not use collections but if it is a must you can always increase the "Data row limit" to 2000 (this is the max). However, you might start running into performance issues because of this. Though as long as your data source isn't more than 2000 rows (and will never be) you won't run into the problem where you're actually missing data from the app.

I'd be happy to help if you need to convert some of the collections and ways of getting around using them!

All the best!

//Clue

[HansHeintz]

My limit is already set to 2000

I have to make reports based on combining data from several quite large SQL tables (tens of thousands records).

Since many date based filters in SQL are still not delegable in Powerapps after all these years I have to swallow them whole and do the filtering atwerwards otherwise users cannot even see what shelter animals are in their kennel / present in the shelter at the moment. This leads to large collections also with app crashes on phones due to memory overload sometimes alas.

This could all be avoided if I could use 

;ClearCollect(cAnimalsPresent,Filter(asiel_verblijf,IsBlank(date_of_departue)))

But sadly this is not to be because blank (date) fields are not delegable (with SQl)

Sometimes it seems that a field value being blank is not taking seriously / to consider, while it is essential information.

Thanks again.

[Clue]

I see, I'm assuming the database contains all animals present and departed? Therefore you have the big backlog of data. What you could do is trigger a Power Automate Flow in the OnStart of your app. This Flow could then filter through the data, which wouldn't use the browser resources. It would then send you a collection of all the animals which have no "date_of_departure".

This would be my recommendation based on your case, but I might be understanding something incorrectly. 

Hope this helps! 

[Clue]

I see, I'm assuming the database contains all animals present and departed? Therefore you have the big backlog of data. What you could do is trigger a Power Automate Flow in the OnStart of your app. This Flow could then filter through the data, which wouldn't use the browser resources. It would then send you a collection of all the animals which have no "date_of_departure".

This would be my recommendation based on your case, but I might be understanding something incorrectly. 

Hope this helps!