cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KimberlyM
Helper IV
Helper IV

CountIf no longer counting all my record entries

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
Drrickryp
Super User II
Super User II

Hi @KimberlyM 

What do you get if you do

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

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

@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
Community Support
Community Support

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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.

KimberlyM_0-1598284722847.png

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.
 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza Winner Announcement

Please join us on Wednesday, July 21st at 8a PDT. We will be announcing the Winners of the Demo Extravaganza!

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,306)