Answered

Delegation warning on Month and Year Formula in gallery label control

(0) Share

Report

Posted on by blessu

Let us consider one scenario: I have two data verse tables where first table is Company with columns: CompanyName of type autonumber and CompanyDate column of type Date. Another Table Employees with columns: EmployeeName of type Autonumber, EmployeeCompany of type Text but have values matching CompanyName from Company table. Another column EmployeeDate of type Date. Another column is AmountPaid of type number. Now I have one screen where I added gallery with datasource as Company. Also in same gallery I added one Label control where I want to show the sum of AmountPaid on the basis of matching CompanyName and Month & Year from EmployeeDate matching Month & Year from CompanyDate. There should not be any delegation issue while doing this.

I used below formula:

Sum(
 Filter(
 Employees, // Name of the Employees table
 EmployeeCompany = ThisItem.CompanyName &&
 Year(EmployeeDate) = Year(ThisItem.CompanyDate) &&
 Month(EmployeeDate) = Month(ThisItem.CompanyDate)
 ),
 AmountPaid
)

I don't want to create another column in Employee table with Month-year field rather use present columns to work with.

Any help will be highly appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

SpongYe 5,909 Super User 2026 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @blessu

You would want to do this in two steps.
For your Items Property of your Gallery:
ClearCollect( colAmountPaid, Filter( Employees, // Name of the Employees table EmployeeCompany = ThisItem.CompanyName && Year(EmployeeDate) = Year(ThisItem.CompanyDate) && Month(EmployeeDate) = Month(ThisItem.CompanyDate) ) )

Then on the label.Text:
Sum( colAmountPaid, AmountPaid )

As you can see in the list below the function Sum is not delegable. That's why we need to split your. formula.

More information can found on https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/connections/connection-common-data-service#power-apps-delegable-functions-and-operations-for-dataverse

------------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Was this reply helpful? Yes No
WarrenBelz 155,779 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @blessu ,

This may not be a total solution - you have three non-Delegable elements in your Filter - Sum(), Year() and Month(). If the number of employees in your list in the company chosen are less than your Delegation limit, then this should work

With( { _Company: Filter( Employees, EmployeeCompany = ThisItem.CompanyName ) }, Sum( Filter( _Company, Year(EmployeeDate) = Year(ThisItem.CompanyDate) && Month(EmployeeDate) = Month(ThisItem.CompanyDate) ), AmountPaid ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Verified answer

blessu 72 on at

Like
a
(0)

Report
Copy link

Link copied!

Unfortunately none of the answer works.
@SpongYe Your suggestion will not work because you are using same datasource in both items and control which is in same gallery.
@WarrenBelz Once delegation limits cross then i will not get right answers.
I am surprised how come @Microsoft not doing anything for these delegations issues. Its major roadblock in developing advanced applications for consumers.
Only solution i can think of now is to create another column in employee table with Month-Year taking values from Date column.

Was this reply helpful? Yes No
WarrenBelz 155,779 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@blessu ,

Yes, this and data denormalization are sometimes required to manage Delegation

Was this reply helpful? Yes No