Power Apps CountRows, CountIf, CountA, and Count vs Delegation

[@morris5984]

Got your explanation the first time. Coming from someone with a learning disability, you explained it very well (though watching your delegation video helped with providing context)! I also enjoy the shorter format. I would love to see more dataverse videos. You did a great video on relationships using dataverse tables but I would love to learn how to relate tables that are “grandchildren”. Example: if you have a product table related to a parts table and then the parts table is related to a supplier table and the supplier table is related to a supplier contact table which that table has an attribute called primary contact. So then I to filter a product and show a portal with all the suppliers for the given product with the primary contact name for that supplier.

[@ShanesCows]

Thanks for the feedback Bryan. And you video idea is noted. 😀

[@AdiCristea]

Unfortunately, Power Apps will not let you directly go above or below two layers, so you'll need to work in small increments, get the first layer data, then use that to get the next one, and so on. Table.LookupColumn1.LookupColumn2 is not permitted yet (or it wasn't couple of months ago, maybe it's been fixed, good idea, let me try it, Bryan).

[@ShanesCows]

Agreed. Last time I tried that it didn't work for me either. So I had to do exactly what Adi says.

[@solarpunk_]

Can you work directly with the data to flatten out your table a little bit with an intermedeitry table? For instance are some of thouse relationships 1:1 relations thather than 1:Many? An intermedieratry table could store some of that information in additional columns instrad of having to lookup into grandchild tables...

[@morris5984]

@@ShanesCows what kind of function would you use to grab that data? I’m thinking something like a nested lookup but have never attempted that before so not exactly sure. I currently use SharePoint lists but I really want to get more into Dataverse.

[@jiromaiya]

Essentially, we need to filter down to the set delegable limit for our apps. Thanks Shane.

[@ShanesCows]

You got it! 👍

[@kimgaskins5537]

Great video Shane; I was able to resolve my issue BUT I'm having to do a filter for everything I want to count in the gallery. Sooo, if I want to count the number of students that completed a class, and the number of students that canceled a class how can I do that based on what's already filtered in the gallery control and avoid doing a FILTER for each?

[@SaraHamiltonJD]

Great video Shane! Thank you.

[@ShanesCows]

Thank yor Sara and I appreicate you being a member. 🤩

[@notonurnelly]

Great video Shane and thanks for the heads up on the new Dataverse setting that will certainly come in handy as I'm trying to do a lot more with Dataverse.

[@MrUggeh]

I always ended up making a flow in power automate to grab large data from SQL/Sharepoint and pass to powerapps in a collection to get around delegation limits, since its not always possible to ensure that your Filter query will return less than 2000 rows, especially on large datasets.

[@marounsader318]

hi, how do you use PA to add data to collection?

[@moisestatis5418]

Great video, thanks! The bonus at the end was awesome. A simple solution in front of my nose that I didn’t see it.

[@ShanesCows]

You're welcome!

[@MrHHBlackhawk]

That was more information than any other source.Thx.!

[@ShanesCows]

Glad it was helpful!

[@TheKalipolis]

Hi Shane! I loved this video. I use countif for counting confirmed appointments by person. Your option is way better. Thanks. Could you consider to make a video on how to populate Word / Excel template without premium license?

[@MrHHBlackhawk]

Danke!

[@ShanesCows]

Thank you for your support! 😎

[@franknielsen3219]

Another great video from your studio Shane. THX

[@ShanesCows]

My pleasure!

[@fadiahdab]

Thank you Shane , amazing as always

[@ShanesCows]

Thanks 😊

[@HeyTezza]

Needed this exactly, thank you so much!

[@ShanesCows]

Glad it was helpful!

[@thomasschaub9474]

Super helpful Shane!! Thank you!

[@ShanesCows]

Happy to help!

[@richardn88]

Thanks Shane, I think I have now watched everyone of your PowerApps Videos, some more than once. They have been a great help getting started with my Apps. A couple of Ideas for new videos. 1, The New PowerApps push notification V2 (what is dynamic parameter?) can it be used to trigger an action within the app? 2, The new Map connector, is there a way of drawing on the map by circling pins and adding them into a collection? I was thinking it might be possible using maps drawing JSON outputs (Latitude and Longitudes), then collecting the pins within that area.

[@brandondent2996]

Great video! After I apply my needed filter, the row count exceeds the delegation limit of 2,000. It is around 4,500 rows. Is there a way I could get the true count (4,500) in power apps? Note: I am using SQL server as my data source.

[@ShanesCows]

No 😢 Maybe write a stored procedure or view that you can call that has your count for you?

[@Alan-wt1vo]

Can the app delegation limit be called into a variable. Perhaps to use as a check with count rows to see if the is a delegation risk on the filtered data?

[@ShanesCows]

Not that I know of.

[@Saadssk94]

Thanks buddy. your videos are funny and informational at the same time.

[@ShanesCows]

Glad you like them!

[@thtoon8234]

Hi, Wonder if you can help with a different delegation issue I am having with the Value() function as below: Sort(tableName, Value(fieldName), SortOrder.Descending) This is using a Dataverse table and an AutoNumber field name, from which the Value(fieldName) is returning a delegation warning. Is there an alternative to using Value() that is not delegable? thanks Tony PS - I incorrectly assumed the AutoNumber was a numeric data type (but is actually alphanumeric). As such I am trying to use the Value() to sort this correctly within a gallery.

[@ShanesCows]

Unfortunately there is no easy answer. 😐 What you might try, I haven't tried, is go back to your table and make a formula column in DV that does the Value(autonumbercolumn). If it lets you, can you sort on that column? I don't know the answer but that is what I would try.

[@thtoon8234]

@@ShanesCows Thanks for the quick reply Shane, I will give it a try 👍

[@thtoon8234]

@@ShanesCows Hi Shane, I tried your suggestion and it worked a treat 😀 I guess using the Value() function within a Dataverse formula is not delegable, its only delegable when its used in a Canvas App. As such its a great solution to get around the problem of AutoNumber fields. Thanks again 👍

[@Juventino222]

Hi Shane, On the beginning I would like to thank you for your last tip :) You saved my day. I'm not sure if you have seen my post on previous video. I have another problem and I believe you can help. I have created Sharepoint form in Power Apps. I have question in the form and possible answers are 1,0,N/A. Is it possible to display only "Yes", "No", "N/A" instead of 1,0,N/A. So for example when I select "Yes" and submit form I can see on sharepoint list 1. I'm trying to migrate form from infopath when it was set up somehow. Thank you. Regards, Pawel

[@NOR3MSTI]

What about countif using flow to count dropdown / or radio selections

[@ShanesCows]

Sorry, I am not sure what you mean.

[@sureshpune6076]

Hi Shane , Thanks for the video. I have 4 dropdowns with S & U as common options of all dropdowns. I need the count of Selection of S. That count, I need to place in another text input column. How can we achieve this. Any help is appreciated. Thanks in advance

[@fredbarnes196]

Gallery.AllItems represents only the first 100 records in the gallery until you start scrolling down the gallery, so counting AllItems will confuse users. Relying on there never being more than the delegable number of records in the filtered datasource seems like a bad idea. And countRows still throws delegation errors even though "Enhanced delegation" feature is checked in the settings. I am still afraid to count rows **Edit** CountRows was throwing the delegation error when I was using a view for the gallery. It seems to work on large sets if you use the whole table.

[@edersonti]

Shane, Something interesting happened. When importing a view into a collection, columns created "virtually" are not imported: Example: Table: vw_Quant_students(quant numeric); Creating the view: CREATE OR REPLACE VIEW VW_Amount_alunos AS (select 'ALU' ALU_TYPE, COUNT(*) QTDE from vw_Quant_students) The view will have 2 columns: ALU_TYPE, QTDE. When importing into a collection, the collection only shows QTDE. Is this a limitation from powerapps or me? lol

[@AdiCristea]

CountRows('PowerApps issues', 'Solved by Shane with a video of his' = true)... Not delegable, and the number is too large. Cheers for the video. PS. I'm surprised you haven't mentioned our favourite function (or one of our favourites): With. What I usually do is grab the records into a variable with Filter, thus delegable, then use CountRows on the temp variable, thus delegable, problem solved, as a Power Apps guru would say, easy peasy lemon squeezy.

[@ShanesCows]

You are awesome Adi! Thanks for keeping me on my toes. 😍

[@TyRey]

Another great video Shane.

[@ShanesCows]

Thanks ☺️

[@edersonti]

Hi Master Shane, I believe that creating views in the database to perform counting operations would be more performative. Am I right ? I created a collection and populated it's content with data from a database view. I couldn't add new columns in the same command as the collection creation. AddColumns(ClearCollect(ColCopia;'VIEW_BASE_DATA');"Type";"Y") I needed to create the first collection with the view data, and the second collection adding the fields. Did I do something wrong ? tks

[@ShanesCows]

Views would be faster. You have a typo in your code: ClearCollect(ColCopia; AddColumns('VIEW_BASE_DATA';"Type";"Y")) that should be it or close anyway.

[@edersonti]

@@ShanesCows It worked. Thanks !

[@prathameshjoshi9199]

Thank you Shane 😊

[@ShanesCows]

Happy to help. Have a great day. 🐶

[@louiscutmore]

Awesome, as always 😊

[@ShanesCows]

Thanks 🙏

[@marckmagallon6318]

hi Shane, i realy like this video, just that it does not work for mi, i put this formula: Count('vacation pool'.NAME) and it says that there's something wrong, even the name of the Colum is correct, i also add a table and put Count(NAME_Column1), and it doesn't work heather, do you know what could happened, if you help me ill follow and like, thanks for your time... ps it works with countrows

[@CarlosSolisNow]

Super Useful Shane

[@ShanesCows]

Glad you liked it

[@carlosgomezalvarez1476]

Hi Shawn, great video as always :D, can you please made a video about click counting? So that we can now how many clicks the user did in a session and how to save that in sharepoint

[@danielcecil951]

Thank you. Very helpful. I tried counting items in a gallery if date = today, but nothing was returned.I have today's date in my gallery. CountRows(Gallery6.AllItems, 'End Date and Time' = Today()). Please help. Thank you

[@ShanesCows]

Are you sure they match? Does End Date and Time have a time component? 9/23/2024 2:00 PM doesn't match Today() because it is 9/24/2024 12:00 AM

[@MichaelDeBlasis1369]

I'm using a sharepoint list that has about 10 rows of data and when I use the count function to display a dashboard of how many records there are it returns 0. :(

[@lwhite3225]

Hi Shane! Your timing is impeccable. I just hit the delegation wall of 2000 records using CountRows and CountIf in a PowerApp. I'm trying to use the Count/Filter process, but I'm having trouble. The PowerApp code I'm using is "CountRows(Filter(PARS, Status = "New")). PARS is the SP list name, Status is the column name, and New is the value I need to count. PowerApps doesn't like it. Can this be made to work with text fields? BTW, Status is a choice field in the SP list. Thanks! Lisa White

[@Power-Platform]

Hey I tested your scenario, it seems you need to put "Status.Value" in your formula. Try the code below and you will get the delegation warning but the filter portion will be delegable as Shane mentioned in the vid. "CountRows(Filter(PARS, Status.Value = "New"))"

[@lwhite3225]

@@Power-Platform Thanks, Barry, that worked!

[@jasonmelchert155]

@@lwhite3225 it was helpful that you mentioned it was a choice column. That’s why you need the .value.

[@iyengar007]

Awesome tip. Will buy you coffee when you are in India/Bangalore. I'm also hoping to steal apps you may have developed for learning purpose.

[@davidakridge2831]

But the gallery only shows the first 100 records unless you scroll. It is amazing to me that I can write and app that links to a SharePoint list of 2000+ records and nowhere in the app can I get an effective count of the number of records to display. Such a simple request that is way too complicated.

[@ShanesCows]

Countrows not delegable is annoying

[@MrDionmar]

Excelent! tks very much!

[@ShanesCows]

You are welcome 🤗

[@gnanavelv3715]

how to use a COUNTIF for checking Choice column Text...

[@ShanesCows]

Not sure. Haven’t tried

[@omiboy4u]

I'm so tired of searching, Sort CountRows in the Gallery, please please please if you are reading and you know how to do it please reply,.

[@ShanesCows]

What is the formula you have tried? I don’t follow what you want to do. CountRows produces a number, not a table, so nothing to display in a galley or sort.

[@omiboy4u]

@@ShanesCows I've posted I comm and Dm you sir 👍

[@casadelujocali]

gRACIAS

[@ShanesCows]

Happy to help. Have a great day. 🐶

[@augustofilho4220]

...... Muito bom.

[@MichaelDeBlasis1369]

No Countif or Count functions make Homer something something.... ;)