Answered

CountIf and Help avoiding delegation flag

(0) Share

Report

Posted on by radiosonicfan

Hello,

I am getting a delegation flag with formula below. Trying to count row data for current year. Simplified the formula. I have more selected FieldName = " ", but no need to show long formula in this forum. The formula yields right count. Just try to build app without delegation flags.

CountIf(Gateways, Region = "North America" && Year('Dates') = Year(Today()) && Reference_Only.Value = "No" && (FieldName.Value = "ABC" || FieldName.Value = "DEF"))

Thanks for the assistance.
Kevin

Categories:

General Questions

All responses (4)

Answers (1)

radiosonicfan 72 on at

Like (0)

Report

Re: CountIf and Help avoiding delegation flag

@v-bofeng-msft Thanks for the feedback. I am going to avoid my I want to do and rely on a Qlik Dashboard for some light metrics reporting. Not going to hold my breathe for Microsoft to implement delegation for CountRows. Planned and 4 years has ticked by....
Verified answer

v-bofeng-msft on at

Like (2)

Report

Re: CountIf and Help avoiding delegation flag

Hi @radiosonicfan :

I am afraid it is impossible to solve the delegation problem in your formula.

The point is that "CountRows","Year" are not a delegable functions.Although the delegation warning can be eliminated by using the With function, the limitation still exists.For example:

I have a list named "BofNewTest" which has 9391 records

But I can still only get 500 by this formula although there is no delegation warning.

In addition,using Flow can increase the limit of 2k to 5K

Best Regards,

Bof
radiosonicfan 72 on at

Like (0)

Report

Re: CountIf and Help avoiding delegation flag

Thanks. Think I can ignore. Have under a 1k records and generate about 400-600 records a year. Assuming if we generated 1k/records/year. It would take a while to hit 50K delegation limit.

"There is a 50K delegation limit if you use CountRows and CountIf functions with filters. There is no hard limit on the CountRows function when extracted directly from the data source because of the cached count that Dataverse keeps"
lcimon 89 on at

Like (2)

Report
Re: CountIf and Help avoiding delegation flag

Hello,

You get away from this delegation issue by:
Adding a new column to you datasource to store the Year only. This will help to remove the part Year('Dates') of your formula.
Spliting your formula in 2 steps (the CountIf function isn't delegable) as following:
With( {pRecords: Filter(Gateways, Region = "North America" && YearOnly = Year(Today()) && Reference_Only.Value = "No" && (FieldName.Value = "ABC" || FieldName.Value = "DEF"))}, CountRows(pRecords) )
Where YearOnly is your new column.