SharePoint row totals not accurate in PowerApp app

[FranciscoSaba]

Hello PowerApps community, 

I'm in need of help, I have this new home dashboard I'm working on and I'm running into a SharePoint List count and countrow discrepancy. 
For the first discrepancies - under the "Welcome!" text, I'm trying to retrieve the total of room checks entered on current day by the technician. The formula I'm using for the total count is the following: 

"Daily Room Checks " & 
    CountRows(
        Filter(
            'VNOC Room Checks',
            'AV Technician'= User().FullName &&
            Created = Today()
        )
    )

I'm getting 0 as my total when I have a total of 1. The SharePoint column types are 'AV Technician' - Single Line Text and Created is Date. 

For the second discrepancy, under the Reports button - The total of YTD Pass and Fail count is inaccurate - this is the formula being used in the Text Label datacard:

"YTD Fail: " & 
    CountIf(
        'VNOC Room Checks', 
        Status.Value = "Fail" &&
        Year(Year) = Year(Today())
    ) &
"                                                                  
    
"&
    "YTD Pass: "&
    CountIf(
        'VNOC Room Checks', 
        Status.Value = "Pass" && 
        Year(Year) = Year(Today())
    )

the real total should be closer to 1500 for Passes and 218 for Fails. The Status column in the SharePoint List is a Choice column.

Any help with how to fix this and getting a better accurate count?

[Amik]

@FranciscoSaba - not the answer you will want to hear, but you will have noticed that the CountRows function is not delegable (indicated by the blue underline). There is nothing to "fix" here as this is the intended design.

Even if you're using a Filter function (which is a delegable function), the non-delegable Count Rows function will only run on the first 2000 rows returned on that filtered subset. One solution is to apply an additional filter condition, and if you can guarantee the result of that filter will return less than 2000 rows, you will recieve an accurate result.

Regarding the second problem. The CountIf function is also not delegable. However, you could mitigate this somewhat by using the same pattern you used for the first problem. You can run a delegable Filter function, and then use the non-delegable CountRows function on that filtered subset:

With(
    {
        _prefiltered_data: Filter(
            'VNOC Room Checks',
            'Status.Value = "Fail",
            Year(Year) = Year(Today())
        )
    },
    CountRows(_prefiltered_data)
)

Note however the same reasoning applies, the CountRows function will only run on the first 2000 rows returned from the Filter.

An alternate option is to use Dataverse if your organisation premium licences as it supports better delegation than SharePoint.

Another option is to move the count operation to Power Automate, and then return the result back to your app using the Respond to Power Apps action.

[FranciscoSaba]

Thank you for your reply @Amik - I can understand why the totals will not reflect accurately with Monthly or Yearly totals. The only question I have is, if the rows returned is limited to 200 rows, then I'm still wondering why my formula doesn't work. On any given day, the entire team can submit up to 50, give or take, but never at or over 200 rows, so it should technically work? Do you have more insight on why or is it due to not being delegable? 

Interestingly enough, the Conference Room list count I have works fine - I have over 800 rows and everything seems to be in order.

I'm trying to figure out how to create an action in Power Automate to count all rows and Respond to Power Apps. Any help with how to achieve would be highly appreciated!

[Amik]

@FranciscoSaba - some points:

• The max delegation limit is 2000, not 200.
• Delegation means that operations undertaken by your app are "delegated" back to your data source for processing. This means your data source will execute the operation and "pass" the result back to your app. However, server side operations can only be delegated for up to the first 2000 rows in your entire data source, not by the number of rows which have been added by your team on any given day.
• I have no idea what the "Conference Room list" is, but given you have said you have over 800 rows, this is well within the max delegation limit of 2000.

Regarding the Respond to Power Apps action, please see here:

https://learn.microsoft.com/en-us/business-applications-release-notes/april18/microsoft-flow/return-...

If you have specific questions on this, please raise a new post in the Power Automate General Forum:

https://powerusers.microsoft.com/t5/General-Power-Automate/bd-p/MPAForum