cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gioce
Helper II
Helper II

function countif

Hi, sorry my english ... I have a problem to solve ... I need to count the records of a table, but when I exceed 2000 records the result is wrong. I tried the following Sum function (ForAll (Filter ('[dbo]. [USR_RIG_INVENTARI]'; CODINV = Dropdown2.Selected.CODICE); 1); Value), but it gives me the value 2000, when instead the lines are 296628. I also tried with the Countif function, but it reports incorrect values. Could you help me understand?

1 ACCEPTED SOLUTION

Accepted Solutions
v-siky-msft
Community Support
Community Support

@gioceYes, Counting function is not delegable for SQL. But as an alternative workaround, I often use CountRows() function directly on the gallery from SQL, because the SQL is delegable, so the gallery can get all records. Please check this.

1. Add a gallery, set Items property: [dbo]. [USR_RIG_INVENTARI]

2. Set Text of Label:  CountRows(Gallery1.AllItems)

Note: there is no need to display the gallery, set its Visible property to false

Sik

View solution in original post

4 REPLIES 4
mdevaney
Super User
Super User

@gioce

The SUM and COUNT functions cannot be delegated.  Therefore you will only get the 1st 2,000 results when using them.

 

Link to MS Delegation Article

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

is there an alternative to count records that exceed 2000? thank you

@gioce
The answer is complicated... it depends.

There is no function in PowerApps to CountRows over 2,000. However, you could calculate that in a table in your database or keep a count of it and simply display that number. It’s too long to explain the method here but I am considering doing a blog article about it.

Another idea is simply to use the last ID number of the table assuming no records get deleted.

—-
Please Accept as Solution if this post answered your question so others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.
v-siky-msft
Community Support
Community Support

@gioceYes, Counting function is not delegable for SQL. But as an alternative workaround, I often use CountRows() function directly on the gallery from SQL, because the SQL is delegable, so the gallery can get all records. Please check this.

1. Add a gallery, set Items property: [dbo]. [USR_RIG_INVENTARI]

2. Set Text of Label:  CountRows(Gallery1.AllItems)

Note: there is no need to display the gallery, set its Visible property to false

Sik

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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.

Users online (1,742)