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.
Solved! Go to 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.
Ok, here's a temporary solution if anyone needs it. It's conditional: this only works if
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
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.
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.
Thank you for the workaround, Greg. It is taking time to rework formulas, but that is working for me so far.
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.
Ok, here's a temporary solution if anyone needs it. It's conditional: this only works if
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
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.
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?
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?
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?
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:
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/
User | Count |
---|---|
256 | |
111 | |
95 | |
48 | |
40 |