I have a SharePoint list with three columns
Column 1 = Fruit (Single line text)
Column 2 = Ripe (Single line text)
Column 3 = Destroyed (Date)
I am looking to display the count of how many rows meet a set of criteria within column 1 and 2 and where column 3 is blank using a label on my Power App screen.
Example of desired outcome:
I am looking for the row count with Fruit=Apple, Ripe=Yes, Destroyed=BLANK
This result would display 2 from the list sample below.
Fruit Ripe Destroyed
Apple Yes 7/5/20
Orange Yes 6/5/20
Pear Yes 5/2/20
Apple No 4/6/21
Apple Yes
Apple Yes
I can get one criteria to work with this formula CountRows(Filter('DATA',Fruit="Apple")) but when I try adding more criteria into the formula it fails CountRows(Filter('DATA',Fruit="Apple" && Ripe="Yes" && Destroyed=""))
I would appreciate the help. Thanks
Thank you ... that appears to calculate and return the correct number.
Yes - the issue was what I suspected (Delegation - you might have a read of this blog of mine) - a Date was not what I was expecting from you post - try this
With(
{
wData:
Filter(
'DATA',
Fruit="Apple" && Ripe = "Yes"
)
},
CountRows(
Filter(
wData,
Value(Destroyed) = 0
)
)
)
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
The SharePoint list has 4126 rows/records in total. When I manually sort the list with the criteria, I come up with 334 records. The data in the list was imported from an Excel spreadsheet. The Fruit and Ripe column is confirmed as a single line of text. The Destroyed column is confirmed as a Date and Time column with Date only selected.
Are you saying there is 334 rows with the criteria? Please confirm SharePoint is your data source and whether Ripe is a yes/no or Text field. Also how many items are in the total list and please look at my amended post.
Thank you and I gave it a try. My list has 334 rows with this criteria and the formula is displaying 47. For some reason it is not capturing all the rows. Ideas?
Hi @Armyrunner ,
Assuming Ripe is a Text field (not yes/no), this should work
CountRows(
Filter(
'DATA',
Fruit="Apple" && Ripe="Yes" && Destroyed = Blank()
)
)
If it is a yes/no field
With(
{
wData:
Filter(
'DATA',
Fruit="Apple" && Destroyed = Blank()
)
},
CountRows(
Filter(
wData,
!Ripe
)
)
)
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.