Unanswered

CountRows with Filter Working for some records and not working for others

(0) Share

Report

Posted on by nduati

Hi,

I am facing a very strange problem with the below CountRows with filter function. It works very well on some records, but returns 0 for records despite them having the data that i am querying for.

CountRows(
 Filter(
 AllStudentsDailyData,
 IDRef = Dropdown1_1.Selected.ID,
 DateValue(Text('Date Captured')) >= DateValue(Text(DP_Start_7.SelectedDate)) && DateValue(Text('Date Captured')) <= DateValue(Text(DP_End_7.SelectedDate)),
 Attended.Value = "No"
 )
)

Just to give you an example of what i mean, here is a screenshot of what i am counting for the different parameters(Attended, Completed Goals ....). Both of these selected students have details and shouldn't return 0. Unfortunately one returns the correct value, the other doesn't return any value. Kindly advice on what the issue could be. I am picking my records from sharepoint list. I have slightly over 4000 records. Could the number of records also be an issue?

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (0)

timl 36,415 Super User 2025 Season 2 on at

Like (0)

Report

Hi @nduati

CountRows is not delegable function. This is the reason why your formula doesn't return all the records that you expect.

@WarrenBelz has a blog article that provides more details on this.

https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@nduati

You are converting a Date column to text and then back to date. This is not delegable.

Please consider changing your Formula to the following:

With({_preFilter: Filter( AllStudentsDailyData, IDRef = Dropdown1_1.Selected.ID && Attended.Value = "No" ) }, CountRows(_preFilter, 'Date Captured' >= DP_Start_7.SelectedDate && 'Date Captured' <= DP_End_7.SelectedDate ) )

Technically this is all delegable if combined, but CountRows will trigger a warning otherwise. So in this case, the With scoped variable will contain the results of the pre filter and then CountRows will act on that.

Technically because all of the Criteria in your filter in this formula is now delegable, you could also use this:

With({_preFilter: Filter( AllStudentsDailyData, IDRef = Dropdown1_1.Selected.ID && Attended.Value = "No" && 'Date Captured' >= DP_Start_7.SelectedDate && 'Date Captured' <= DP_End_7.SelectedDate ) }, CountRows(_preFilter) )

I hope this is helpful for you.

Was this reply helpful? Yes No
nduati 33 on at

Like (0)

Report

Hi @RandyHayes ,

I have tried both your solutions but they don't seem to work 😟

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@nduati

Can you expand on that please? What didn't work - no results, errors, something else?

Was this reply helpful? Yes No
nduati 33 on at

Like (0)

Report
On the first one, i get an error "Invalid number of arguments: received 2, expected 1" on the CountRows function

CountRows(_preFilter, 'Date Captured' >= DP_Start_7.SelectedDate && 'Date Captured' <= DP_End_7.SelectedDate)
So i even went ahead and removed the date filters just to see if i will get any results by using only the other filters as follows, but i still didn't get any results. Still getting 0 for some and values for other.
With( { _preFilter: Filter( AllStudentsDailyData, IDRef = Dropdown1_1.Selected.ID && Attended.Value = "Yes" ) }, CountRows(_preFilter) )
The second one didn't have any errors but it also returned no values.

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@nduati

Sorry, I forgot kind of an important function in the first formula.

Should be:

With({_preFilter: Filter( AllStudentsDailyData, IDRef = Dropdown1_1.Selected.ID && Attended.Value = "No" ) }, CountRows( Filter( _preFilter, 'Date Captured' >= DP_Start_7.SelectedDate && 'Date Captured' <= DP_End_7.SelectedDate ) ) )

See what you get for that.

Was this reply helpful? Yes No
nduati 33 on at

Like (0)

Report

@RandyHayes unfortunately still returning no values. I feel completely lost at this point.

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@nduati

Can you verify that there should actually be a value where you expect one?

Your formula is all delegable at this point, so even though you are over the record limit on the app, you should be getting results that you expect.

So first, where you are getting zero, find at least 1 record that you KNOW should be in that result set. Then try to work back from that.

Throw a Gallery on a separate screen and set the Items property to delegable Filter that you KNOW should return that one record. Then try to track back why it would not come back in your other formula.

If you can find one record and have trouble tracking back, just shoot your results and scenario here and we can trudge forward.

Was this reply helpful? Yes No