Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Return row count that meet multiple criteria

(0) ShareShare
ReportReport
Posted on by 28

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

  • Armyrunner Profile Picture
    Armyrunner 28 on at
    Re: Return row count that meet multiple criteria

    Thank you ... that appears to calculate and return the correct number. 

  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 145,422 on at
    Re: Return row count that meet multiple criteria

    @Armyrunner ,

    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.

     

  • Armyrunner Profile Picture
    Armyrunner 28 on at
    Re: Return row count that meet multiple criteria

    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.

  • WarrenBelz Profile Picture
    WarrenBelz 145,422 on at
    Re: Return row count that meet multiple criteria

    @Armyrunner ,

    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.

  • Armyrunner Profile Picture
    Armyrunner 28 on at
    Re: Return row count that meet multiple criteria

    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?

  • WarrenBelz Profile Picture
    WarrenBelz 145,422 on at
    Re: Return row count that meet multiple criteria

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,422

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,711

Leaderboard