DELEGATION: Workaround?

(0) Share

Report

Posted on by seadude

1,855

Anyone got a quick tip for getting around the delegation warning thrown here?

CountRows(
 Filter(
 '[dbo].[myTable]',
 createdDate <= ThisItem.date
 )
)

Thank you!

Categories:

Building Power Apps

I have the same question (0)

All responses (13)

Answers (1)

v-monli-msft on at

Like (2)

Report

Hi @seadude,

Accourding to documentation:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

Counting functions such as CountRows, CountA, and Count can't be delegated.

I have found an idea submitted asks for this feature, the status of this idea is planned. Please vote here:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/Extend-delegation-to-Sum-and-CountRows/idi-p/23193

Regards,

Mona

Was this reply helpful? Yes No
seadude 1,855 on at

Like (0)

Report

Thanks for the response @v-monli-msft, I'll vote on the idea.
What about @Drrickryp? You have suggested some great delegation work arounds in the past. Do you know a way to get a count of a filtered range without hitting the limits?

Was this reply helpful? Yes No
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like (4)

Report
Hi @seadude,

I always rise to a challenge. One way to do it is to somehow get all of the results into a collection. The problem here is that Collect and ClearCollect are also not delegated so you would have to split your Filter function so that it only collected <2k records at a time. As an example, suppose you are collecting data over several years and that each year you never collect over 2k records.

Concurrent( ClearCollect(yr2016, Filter('[dbo].[myTable]', Year(createdDate) = Year(Now()-2),
ClearCollect(yr2017, Filter( '[dbo].[myTable]',Year(createdDate) = Year(Now())-1),
ClearCollect(yr2018, Filter( '[dbo].[myTable]', Year(createdDate) = Year(Now())); Collect(allyears,yr2016);Collect(allyears,year2017);Collect(allyears,year2018)

CountRows(allyears) should give an accurate count all of your records. You could also CountRows on each subcollection ie CountRows(yr2017) and be sure that none of the rows is >2000. Then sum the CountRows label values.

Was this reply helpful? Yes No
seadude 1,855 on at

Like (0)

Report

Thanks @Drrickryp. I will work at carving up the data into smaller chunks using Collections.
Take care!

Was this reply helpful? Yes No
Dhuayb 53 on at

Like (22)

Report
It is better to use a single column from that table so you will end up not counting the rows but number of items in a column. Therefore an easy fix is to use COUNT(Source.ID). Or in your case, use
Count( Filter( '[dbo].[myTable]', createdDate <= ThisItem.date ).ID )
This is delegable

Was this reply helpful? Yes No
pbil 4 on at

Like (0)

Report

This is great Idea! It worked for me.

Was this reply helpful? Yes No
Dhuayb 53 on at

Like (0)

Report

Glad it did

Was this reply helpful? Yes No
Dhuayb 53 on at

Like (0)

Report

Glad it worked

Was this reply helpful? Yes No
seadude 1,855 on at

Like (0)

Report

I think back in 2018 when I originally posted this, Date-type columns were not delegable. So any Filter query with Date logic would return Delegation error.

I do like your idea of counting a single column rather than all rows. I'll have to pop open Monitor and see if it reduces the response size (which would mean a performance improvement).

Nice call!

Was this reply helpful? Yes No
ArifNajiruddin 10 on at

Like (0)

Report

Hai @seadude

Hai Frankfei, to solve the problem you had, please follow step below.
1. Add A Gallery.
2. Put the filter as you want at the Gallery = Filter(
'[dbo].[myTable]',
createdDate <= ThisItem.date
)
3. Set the Gallery visible to false.
4. Add a label.
6. Set the label Text = CountRows(Gallery.AllItems)
7. The label will display the row that you had filter from the gallery.

Was this reply helpful? Yes No