cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aro1
Frequent Visitor

Add columns delegation issue

There is no delegation warning but below function (inside Gallery) is not calculating average on any item above 500... Any idea what is wrong or how this could be overcome?

 

Filter(AddColumns(TaskNames,"helperTaskAverage",Sum(Filter(collectionAssignedCompleted,Task=TaskName),TimeSpentOnTask)/Count(Filter(collectionAssignedCompleted,Task=TaskName).ID)),helperTaskAverage>0)

 

Thank you very much in advance.

2 REPLIES 2
Drrickryp
Super User
Super User

@aro1 

I believe that the problem is occuring at the level where your collection is being created.   Collect() and ClearCollect() are not delegatable functions so they are limited to a maximum of 2000 items.   You need to go to File>Settings>Advanced Settings>Data row limit and increase it from 500 to 2000.  The reason you are not seeing any warnings is that when you use a collection as the datasource, there are no delegation issues.

v-bofeng-msft
Community Support
Community Support

Hi @aro1 :

Firstly,could you tell me what your data source is?

Generally, the data sources that can be delegated include: SharePoint\CDS\SQl Sever.

Secondly,The output of AddColumns(……) is subject to the non-delegation record limit. In this example, only 500 records are returned even if the TaskNames data source has 501 or more records.

I think this link will help you a lot:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping#descri...

The only way I can think of is to adjust the value of Data row limit for non-delegable queries to 2000.(As @Drrickryp  said)

file-Settings-Advanced settings

1.JPG

Finally,If the number of records is greater than 2000.The data source can be segmented and saved in batches to a collection.(The data source must be delegateable)

I assume TaskNames is a SharePoint list and "Kind" is one of the text type fields

For example:

1\Set the app's OnStart proeprty to:

 

Clear(MyTaskNames)
Collect(MyTaskNames,Filter(TaskNames,Kind="Kind1"));
Collect(MyTaskNames,Filter(TaskNames,Kind="Kind2"));
……

 

2\Try this code

 

Filter(AddColumns(MyTaskNames,"helperTaskAverage",Sum(Filter(collectionAssignedCompleted,Task=TaskName),TimeSpentOnTask)/Count(Filter(collectionAssignedCompleted,Task=TaskName).ID)),helperTaskAverage>0)

 

Best Regards,

Bof

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,617)