Showing results for 
Search instead for 
Did you mean: 
Advocate III
Advocate III

CountIf is not delegable function, any alternative? Custom API?

Hello, I am building an app and it is imprescindible for me to have the CountIf for count rows if they satisfy a condition, for example, I have a list in a gallery that has 2 columns: name of product and quantity. I want to know how many products I have by name, for instance:


Computers - 2

Laptops - 5

Tablets - 10


This is clearly the next SQL Sentence:

SELECT Name, COUNT(Name) FROM Products GROUP BY Name


In my application I get all names and then use the next CountIf:

CountIf(Products; Name = ThisItem.Name)


Of course this doesn't work if I have more than 500 rows in my table Products that is located in SQL Server because it is just looking for the first 500 rows. My solution for try to do this was to replace the CountIf for a Filter that is delegable:


CountRows(Filter(Products; Name = ThisItem.Name))


The problem of this is that in my gallery each row do that sentence, this provokes that the application works really slow for a simple action that is counting..


Do you know a better way to do this? I was thinking as well to use as connection a custom api and pay azure subscription but I am not sure if that would fix my problem, I think with a custom API I can get all the information, but how do I send that information for the custom API to the Application in powerapps?

Community Support
Community Support

Hi Brank,


From this documentation, we know that CountRows doesn’t support delegation:


I am not sure if Custom API can get all the information, you could have a try with it, please refer to this documentation about how to register Custom APIs in PowerApps:


Best regards,
Mabel Mao

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

hi @Brank

you can use some caching in the process to improve performance

We still in the same way, because I don't know how many records I have in my data source, it could be 500 or 1000 or 4000, I understand that you want me to get some cache as creating Collections and store the information in groups of 500 rows.


I have to say that I created a web service and I am getting the data using a custom API, I have to say that I can get all the rows I am expecting, this means that it doesn't have the 500 limit. I also can store that data in a collection for use it, this means that I can store 1000 rows in a collection if I get the data from the custom API.


With this method all the functions are delegable because I am getting the data with the custom API. (CountRows, CountIf, Collection, etc).


The only thing is that I am working right now with Azure App Service and I would like to know if I can start a web server using a physical server such as my local machine.



Community Champion
Community Champion

Hi @Brank

Using a web service is a much better solution, but I have not had time to try it myself.  I will come back to you for guidance when I will try.  But hopefully Sum, CountRows etc. will be delegated before I need to.  Alternatively if we were able to see views and not just tables, we could create summary views.

Anyway, thanks for letting me know.

Helpful resources

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (4,099)