DELEGATION: Workaround?

Like (0) Share

Report

Posted on 4 Dec 2018 03:27:26 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:

General Questions

I have the same question (0)

Summarized by AI

All responses (13)

Answers (1)

v-monli-msft on 04 Dec 2018 at 08:30:57

Like (2)

Report

Re: DELEGATION: Workaround?

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 04 Dec 2018 at 15:54:12

Like (0)

Report

Re: DELEGATION: Workaround?

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 04 Dec 2018 at 16:26:19

Like (4)

Report
Re: DELEGATION: Workaround?

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 05 Dec 2018 at 05:20:03

Like (0)

Report

Re: DELEGATION: Workaround?

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 05 Feb 2021 at 12:45:34

Like (22)

Report
Re: DELEGATION: Workaround?

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 05 Feb 2021 at 13:14:44

Like (0)

Report

Re: DELEGATION: Workaround?

This is great Idea! It worked for me.

Was this reply helpful? Yes No
Dhuayb 53 on 05 Feb 2021 at 13:17:10

Like (0)

Report

Re: DELEGATION: Workaround?

Glad it did

Was this reply helpful? Yes No
Dhuayb 53 on 05 Feb 2021 at 13:17:44

Like (0)

Report

Re: DELEGATION: Workaround?

Glad it worked

Was this reply helpful? Yes No
seadude 1,855 on 05 Feb 2021 at 14:21:15

Like (0)

Report

Re: DELEGATION: Workaround?

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 10 Jun 2022 at 00:59:31

Like (0)

Report

Re: DELEGATION: Workaround?

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