I have the following formula in my PowerApp:
Sum( Filter( '[dbo].[tmtrak_dev_records]', submittedBy = userEmail, activityDate = Date(2021,3,1) ), activityHours )
Where "dbo.tmtrak_dev_records" is a SQL data source and "activityHours" has data type NUMERIC(4,2).
The formula above doesn't display any errors, but returns a blank value. When I wrap "activityHours" in a VALUE() function it correctly displays the result.
Since VALUE() isn't a delegated function however this isn't a workaround solution for me. Any thoughts?
The one odd thing I found is that in the Filter table result, "activityDate" has a value of "2021-03-01, 12:00:00 a.m." even though it has data type DATE in SQL (i.e. "2021-03-01"). When "activityDate" is left out from the formula, the SUM is correctly calculated.
For me I would use the Power of SQL and get everything present and correct at source and then make use of in your Power App. So what I like to do where possible is for example create a view in SQL based off of the Table, with desired logic and then make use of said view within the Power App.
This sounds like a huge limitation of PowerApps though. Essentially, comparing against a Date in SQL while preserving the original data types isnt possible?
I have tried changing my SQL datatypes to DATETIME, DATETIME2 and to various numeric ones for "activityHours" but it always requires wrapping it in a VALUE-function to get it to calculate correctly.
Instead of Value() you can multiply activityhours by 1. This is a short cut in PowerApps that I use all the time to make my formulas more readable.
The following formula still throws a delegation error though:
Sum( Filter( '[dbo].[tmtrak_dev_records]', submittedBy = userEmail, activityDate = mondayDate ), activityHours * 1 )
Am I missing something here, or is this merely an alternative to Value() without specifically addressing the delegation warning?
A delegation warning is only a warning not an error. If you are summing more than 2000 rows, it can be a problem but if you are not dealing with such large numbers, you can ignore it.
So my entire dataset ("dbo.tmtrak_dev_records") will have many thousands of records, but the table result of the Filter function ("submittedBy = userEmail, activityDate = mondayDate") will have at most a couple of records.
Since the Sum function is only applied to these few records, does it mean I can ignore the delegation warning?
Check out the News & Announcements to learn more.
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.