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 II
Super User II

@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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (44,299)