Using ShowColumns has fixed the delegation error..

[johnjohn123]

I have 2 SharePoint lists:-

1) Project Master

2) Activities

The Activities list has a lookup field to the Project Master, as follow:-

now inside the Projects Gallery we want to show the:-

Total number of Project's completed Activities / Total number of Project's Activities .

in other words we need to show the percentage of the tasks completion under certain Project. to do so >> i added a label inside the Project's Gallery and i define the following formula :-

but we received a delegation warning.. so i changed the formula to use showColumn, as follow:-

and the delegation error has gone.. so i am totally confused .. on how did using Showcolumns fixed the delegation error?

[WarrenBelz]

@johnjohn123 ,

I now see @RandyHayes was responding while I was putting that code together. We do things slightly different ways, but are both giving you the same advice. I used the double With() values more for structure convenience in the end formula. I could have done it at the bottom, but that would have made that bit more complex.

To answer your question, if the first formula (the project numbers) is less than 2,000 items, then obviously the completed items are also less than this. 

You then have two sets of data that are processed "locally" and therefore not subject to Delegation and you will get the result you are after.

A With() statement will only collect the first (up to) 2,000 matching records in the filter - so in plain English, if you have less than 2,000 items in the project, this will work.

BTW @RandyHayes has retired for the night due to his time zone. 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[johnjohn123]

@WarrenBelzso your formula will get the first 2000 Activities that are linked to the Project is this correct? so let same i have 10,000 items inside the Activities list >> then you formula will still be able to get up to 2000 activities link to the Project.. so in other words as long as i do not have any project which have more than 2000 activities your formula should work fine under all the cases? so even if i have 10,000+ items inside the Activities list, i will be fine as long as i do not have any Project which has more than 2000 activities? are my statements valid?

[WarrenBelz · Accepted solution]

@johnjohn123 ,

That is what I said in my last post - I have a blog explaining this if it helps. It should will also run a bit quicker doing it this way

With(
   {
      wList:
      Filter(
         Activities,
         ProjectTitle.Value=varProjectdetails.Title
      )
   },	  
   Text(
      Roundup(
         CountRows(
            Filter(
               wList,
               Status.Value="Completed”
            )
         )/CountRows(wList)*100,
         2
      ),
      "0%"
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[johnjohn123]

@WarrenBelz  So as long as i do not have any project which have more than 2000 activities your formula should work fine under all the cases and will give 100% accurate result.is this correct?

[RandyHayes · Accepted solution]

@johnjohn123 

That is correct.  That is the same thing I mentioned prior - Everything in PowerApps is limited to your record limit (except, in a way, galleries, but they page...that is quite different).  ShowColumns can only return 2000 max records. Filter can only return 2000 max records, etc.

You cannot delegate anything to ShowColumns because it has no criteria and you will only ever get the FIRST max records.  

Filter allows delegable criteria, so it can delegate beyond the 2000 because you are delegating - your criteria is handed to the datasource to filter.  

NOW...if your delegable filter criteria results in less than 2000 records, then you will get them all regardless of the size of the datasource.  If it is delegable and it will result in MORE than 2000 results, then you will only get the first 2000 results.

It is common to misunderstand that record limit and delegation are two different things...but they are.

And to be clear on your last question.  YES, if you have less than 2000 activities for a project, your prefilter will get them all and you will have them in the With scoped variable where you can further narrow as much as you want and delegation no longer plays any role.  

If your project has MORE than 2000 activities, you will only get the first 2000.

I hope that is clear and helpful for you.

[johnjohn123]

@RandyHayes  yes thing are 100% clear to me.. thanks a lot

[WarrenBelz]

@johnjohn123 ,

So was my answer not correct?

[johnjohn123]

@WarrenBelz  yes it is correct.. thanks for your help

[johnjohn123]

@WarrenBelz  your formula is raising these errors:-

[WarrenBelz]

@johnjohn123 ,

I thought you would spot the typo there - now fixed.