Rollback delegation

[mr-dang]

Speeding up PowerApps is important, and that's what the PA team had in mind when they built in delegation.

However, the implementation of delegating is incomplete. For instance, when you use Sum, Average, or Sort with Filter, you'll only be accessing the first 500 records of the table. An app that relies on Sum(Filter()) will be inaccurate.

I am recommending that delegation is rolled back until all functions can work with Filter. If that is not possible, I am looking for an alternative way to Sum a column of a filtered table.

[GregLi · Accepted solution]

Thanks for your feedback.   Our current situation is clearly not where we want to be and the good news is that we won't be here long.

As we approached the Preview release, we had a choice to make: rollback the delegation or push ahead and make delegation work.  This was a hottly debated topic and we ultimately decided to push ahead as a top priority.  Rapid progress has been made since then and we'll start seeing better delegation support released over the next month.

Our first priority is to unblock the apps that we generate when, for example, you create an app from a SharePoint list.  These apps use Sort and Filter so that is the priority.  Since we don't generate apps that use aggregation (Sum, Average, etc), this will take a little longer to come out, but it is on the radar.

The only workaround I can offer is to Collect the result of a Filter (that could be delegated) into a local collection and Sum or Average over that.  Depending on the table size, it could be very slow to pull all the data into the collection.

[mr-dang]

Thank you for the workaround, Greg. It is taking time to rework formulas, but that is working for me so far.

[mr-dang]

Wait a sec. I'm still at 500. 

If I set Button1.OnSelect to:

Collect(temp, Filter(datasource,expressions))

and

TextBox1 to:

Sum(temp,column)

Then CountRows(Filter(datasource,expressions)) still results in 500.

Can you clarify how I could use Filter to draw from all records? I'm using Excel.

[mr-dang · Accepted solution]

Ok, here's a temporary solution if anyone needs it. It's conditional: this only works if

• you are okay with manually moving data
• you are okay with removing and adding a datasource, then re-saving your app
• you need your app to work and will do anything to make it work
• you already use two tables that house the same data

Create two tables

Table1: this is where you will Patch or SubmitForm your live data

Table2: this is your archive of static data; it will have the same columns as Table1

Add Table1 from your online writeable datasource (Dropbox, OneDrive, GoogleDrive).

Add Table2 as static data

1. When Table1 gets to 500 rows, manually copy+paste all of its data to Table2 using Excel.
2. Remove all records from Table1.
3. Remove Table2 from PA, then re-add it.
4. Re-save the app.

This works because static data is fully loaded (500+ records). You will need to make all your formulas check both Table1 and Table2. This is not a problem if you already split your data into two tables.

[Steelman70]

Hi, I have tried delegation by simply doing a CountRows(Filter(MySource, MyColumn = FilterValue)), where FilterValue is a number or a string depending on type of MyColumn. This has not worked for Access Web App (AWA) tables or SharePoint lists or Excel tables. I only get a subset of the 500 records (or 256 in case of AWA) returned without the filter.

Am I doing something wrong?

If not, for which sources does delegation work today?

[mr-dang]

Yes, I previously thought it worked out for me, but that was because I clicked my button for Collect(temp, datasource) twice and got 1000. 

From Greg's solution, I found that it is not possible to Collect an entire datasource to a temp collection. You will only get 500.

Another problem is that even if you set Gallery.Items to Filter(datasource, column=criteria), it will not delegate as described.

Will the next update resolve the delegation issues?

As an aside, does CountRows qualify as an "aggregate" function?

[Steelman70]

Hello, I still cannot get delegation to work, even though the blog on the latest update says it is now working.

I have tried with Access Web App tables, SharePoint lists, and Excel on OneDrive.

Has delegation worked for anyone?  And if so, with what type of data source?

[mr-dang]

Echo: There is no change in the number of records I can show in version 2.0.430. It still only operates on the first 500 records for:

• Gallery.Items = datasource
• Gallery.Items = Filter(datasource)
• Button.OnSelect = ClearCollect(collection, datasource)
• Button.OnSelect = ClearCollect(collection, Filter(datasource))

Context: I ran my tests using Excel files on OneDrive and Dropbox with these results.

My understanding is:

As the user scrolls, additional data will be brought down from the data source.

I don't see additional data being brought down as I scroll though.

If complete delegation of a formula is not possible, the authoring environment will flag 
the Filter or Sort formula as a warning. 

I also do not see a warning flag for any instance of Filter or Sort, considering that things did not work out.

Requested: additional documentation on how to successfully achieve delegation. Please update the entry for Filter and LookUp:

https://powerapps.microsoft.com/en-us/tutorials/function-filter-lookup/