CountIf no longer counting all my record entries

[KimberlyM]

Up until recently I was able to keep count of all my record status settings. Now I notice none of my numbers are changing so I did an export of all my records and sorted by my 2020 records so so the excel screenshot is showing my actual record values where my powerapps is "maxed" out at 1624. Can someone suggest to me why this is happening?

My formula is:

CountIf(Lead_Data, CreatedYear = ReportYear_1.SelectedText.Value)

I have no delegation warnings so I am unsure why it would not be able to count the records.

See attached screenshot to see the formula, powerapp screen and excel document side-by-side.

[Drrickryp]

Hi @KimberlyM 

What do you get if you do

CountRows(Filter(Lead_Data, CreatedYear = ReportYear_1.SelectedText.Value))

[KimberlyM]

@Drrickryp Adding the filter to it creates a delegation error and no number change.

[Drrickryp]

@KimberlyM 

Most likely you are dealing with a delegation issue. CountIf and  CountRows is not delegatable but Filter is so if the filter results in less than 2000 items CountRows should give a correct result regardless, provided that File>Settings>Advanced Settings> data row limit is 2000. 
Just for a test, use Import from Excel as static data to bring in your table and try our formulae against that datasource.  Static Excel files are not subject to delegation and CountRows and CountIf should give you accurate results.  if you are getting the correct count using the static source then that would prove that the problem is delegation related. Otherwise there must be an error somewhere in your calculations. 

[v-yutliu-msft]

Hi @KimberlyM ,

Could you tell me

1)what is your data source?

2)how many records in your data source?

Not having delegation warning does not mean that you do not have delegation limit.

Delegation warning will show when you use delegate data sources and no-delegate functions.

Delegate data sources are: cds, sql and sharepoint.

If you use no-delegate data source, delegation warning will not show but you still could at most deal with 2000 records.

(If you have changed delegation limit to 2000.)

To deal this problem, you need to :

1)change to use delegate function

2)save data to collection (countif is not delegate functions.)

3)count the collection 

Best regards,

[KimberlyM]

Hello @v-yutliu-msft! Thank you for your response.

1)what is your data source?
My data source is SharePoint.

2)how many records in your data source?
I have 2 SharePoint lists I am pulling. 1- Lead_Data is now 2394 records. 2- Lead_History is now 5083 records. 

Lead_Data is the one I am counting as Lead_History is all the comments that is assigned to each particular lead record.

What do you mean by 1)change to use delegate function?

Thanks again.

[v-yutliu-msft]

Hi @KimberlyM ,

If your data source is larger than delegation limit number(2000) and you use no-delegate functions, your app will not perform good, just like the issue that you described.

The solution is to only use delegate functions.

The delegate functions for sharepoint list:

Item	Number	Text	Boolean	DateTime	Complex [1]
Filter	Yes	Yes	Yes	No [4]	Yes
Sort	Yes	Yes	Yes	Yes	No
SortByColumns	Yes	Yes	Yes	Yes	No
Lookup	Yes	Yes	Yes	No	Yes
=	Yes	Yes	Yes	No [4]	Yes
<, <=,<>, >, >=	Yes [2]	No	No	No	Yes
StartsWith	-	Yes	-	-	Yes
IsBlank	-	No [3]	-	-	No

CoutIf or CountRows are both not delegate.

If you insist in using this function, there's another solution: use collection.

In collection, there's no delegate limit. So you could use CountIf function for collection.

However, to save data to collection, you also need to pay attention to delegate functions.

One key point: ID is not delegate for <,>, other number field is delegate for <,>,Filter.

So you need to :
insert another number field to save data to collections.

For example:

ClearCollect(collection1,
Filter(listname,numberfield<=2000)
);
//make sure the filtered records number is less than 2000 records
ClearCollect(collection2,
Filter(listname,numberfield>2000)
)

 Then count then one by one.

 Here's a doc about delegate functions for sharepoint in details for your reference:

https://docs.microsoft.com/en-us/connectors/sharepointonline/

Best regards,

[KimberlyM]

@v-yutliu-msft thank you for your response! I have taken some time to work this out and further understand it. 

I have it calculating now but I am not understanding the number it is giving me. Here is what I have done:

• OnVisible of my screen I have set:
  ClearCollect(RecordsToCount, Lead_Data);
  Collect(RecordsToCount, Lead_Data);
  
• On my field I want to calculate the Total Amount of Leads for 2020 I have set (see screen capture attachment):
  CountIf(RecordsToCount, CreatedYear = ReportYear_1.SelectedText.Value)
  
  
• When I download and manually calculate how many records it should count I get 2105 records/rows in excel. It is counting 3248. Are you able to suggest why this is happening/how I can fix this/what may be happening? The number doesn't make sense to me as my total number of leads for 2019 and 2020 (all records) is 2481 based on the data at this time.

I hope I am being clear and providing enough information. Thank you again for helping me with this! FYI I tried the filter option but I don't know that I need it to calculate all records on this first cell. I tried it and just kept getting errors.

[v-yutliu-msft · Accepted solution]

Hi @KimberlyM ,

ClearCollect and Collect function is also not delegate.

So if you use these two functions :ClearCollect(RecordsToCount, Lead_Data);Collect(RecordsToCount, Lead_Data);,

you will save the first 2K records of Lead_Data to your collection for two times.

Then actually, your collection have 4K records and half of them is repeated.

The reason why you get 3248 result is that the first 2K records in Lead_Data have 1624 records that meet this rule:CreatedYear = ReportYear_1.SelectedText.Value.

Then the result is 1624*2=3248

What I suggest you is: 

save your data to multiple collections per 2K records by filtering your original table(use delegate functions).

If you insist in saving data to one collections, then please save at most 2K records every time by filtering table.

For example:

ClearCollect(collection1,
Filter(listname,numberfield<=2000)
);
Collect(collection1,
Filter(listname,numberfield>2000)
)

By using formula like this "Filter(listname,numberfield<=2000)" to make sure that you save at most 2K records one time in a collection. You need to replace the filter rule based on your situation.

Notice that: please use delegate data source like sharepoint list. Excel is not delegate.

Best regards,

[KimberlyM]

Thank you @v-yutliu-msft for your reply. I think I understand what you are saying about breaking the collection into two groups.

My next question comes in with the "numberfield". The only sequential number field I have is the ID field but using this setup I am still capping out at 1624 total records (it should be calculating at least 2140 as this is what I reported at the end of Friday with manual calculations using Excel). I have attached a screenshot again as well as an image into this area. My data is in a SharePoint list. I also tried to separate it into two differently name collections which did not render a different result.

If I need to have another numerical column in my SharePoint list, other than ID do you know how I could apply this to all my records without having to do it one-by-one?

Thank you again for your time in helping me understand this.