cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Multiple Sum and Filter from SQL Database

Hi Guys,

 

I am struggling to show a sum of a column from my SQL Database based on specific criteria, it keeps saying a delegation warning and also not showing the correct results:

 

Sum(Filter('[dbo].[LeadWithAccountNo]',Lead_x0020_Engineer_x0020_Name = UserNamelbl.Text && Last12MonthCheck = "YES"),Points_x0020_New)

Lead Engineer Name to equal UserNamelbl (this label is just doing User().Fullname)

 

Also Last12MonthCheck to equal YES (this is from a case statement in my SQL DB which looks to see if a data is within last 12 months.

 

Weirdly a figure is actually showing in the label but I have a hunch it is only summing the column on the first occurance and not looking at all the other entries in the SQL DB.

 

Any help appreciated.

 

Andrew

1 ACCEPTED SOLUTION

Accepted Solutions
Community Champion
Community Champion

@Anonymous

The correct result is not showing probably because of delegation as @v-monli-msft mentioned, which means that the result is based only on a subset of records.

Fortunately the solution is very easy.  You create a view in SQL Database already aggregated with the crieria, so in your case you would want to SELECT Lead_Engineer_Name, Last12MonthCheck, Sum(Points_New) AS SumPointsNew and GROUP BY Lead_Engineer_Name, Last12MonthCheck

 

Then in PowerApps you connect to the view and simply:

 

LookUp(MyView, Lead_x0020_Engineer_x0020_Name = UserNamelbl.Text && Last12MonthCheck = "YES", SumPointsNew)

 

This will perform much better than any non-delegated solution that you may come up with.

 

Please let me know how you get on

View solution in original post

3 REPLIES 3
Community Support
Community Support

Hi @Anonymous,

 

If you only get the delegation warning, then it means that your formula should work, but only might won't return records that are over Data row limit for non-delegable queries

 

Notice that User().FullName returns the full name of the current PowerApps user. Please check if you have the same full name for PowerApps and Lead_x0020_Engineer_x0020_Name. Notice that this is case sensitive. You could check the user full name in PowerApps here:
Capture.PNG

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Champion
Community Champion

@Anonymous

The correct result is not showing probably because of delegation as @v-monli-msft mentioned, which means that the result is based only on a subset of records.

Fortunately the solution is very easy.  You create a view in SQL Database already aggregated with the crieria, so in your case you would want to SELECT Lead_Engineer_Name, Last12MonthCheck, Sum(Points_New) AS SumPointsNew and GROUP BY Lead_Engineer_Name, Last12MonthCheck

 

Then in PowerApps you connect to the view and simply:

 

LookUp(MyView, Lead_x0020_Engineer_x0020_Name = UserNamelbl.Text && Last12MonthCheck = "YES", SumPointsNew)

 

This will perform much better than any non-delegated solution that you may come up with.

 

Please let me know how you get on

View solution in original post

Anonymous
Not applicable

Hi, Thanks both for this, worked perfect doing it via a view, not used the Lookup command yet but this also did the job. Thanks Andrew

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (11,999)