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

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (76,977)