cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nduati
Frequent Visitor

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

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?Count.PNG

Count1.PNG

8 REPLIES 8
timl
Super User
Super User

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/

 

RandyHayes
Super User
Super User

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi @RandyHayes , 

 

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

RandyHayes
Super User
Super User

@nduati 

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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. 

RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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

RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,820)