cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Super User III
Super User III

Re: Add columns delegation issue

@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.

Highlighted
Community Support
Community Support

Re: Add columns delegation issue

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (6,415)