cancel
Showing results for
Did you mean:
Frequent Visitor

## Problem creating a button that changes color based on two filters and one sum

Hi, I have a SP list named "Training".  This list allows employees to enter professional training taken during the year to meet the annual requirement of 20 hours.  I have a Canvas app using a gallery that allows the currently logged on employee to see the training they have taken.   I also have a button (separate from the gallery).  I would like this button to change to Green when the employee reaches 20 hours of training and remain Red if they have less than 20 hours of training.  I do not know the formula to (1) filter the training by employee (2) filter the training by year, and (3) sum the total hours by year.

List Name:  "Training".  List items:  "Email" (pulled from person/group).   "Completed" (date of training completion),  "Hours" (number entered by employee).

I've tried the following on "Fill" for the button without success:
If(Sum(Filter('Training', 'Email'.Email=User().Email, 'Completed' >= Date(2021,01,01), 'Completed' <=Date(2021,12,31), 'Hours'>= 20, Green, Red))

Thanks in advance for any help!

1 ACCEPTED SOLUTION

Accepted Solutions
Most Valuable Professional

Try some extra brackets

``````If(
Sum(
Filter(
'Training',
'Email'.Email = User().Email,
'Completed' >= Date(2021,01,01),
'Completed' <=Date(2021,12,31)
),
'Hours'
)>= 20,
Green,
Red
)
``````

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.

Visit my blog Practical Power Apps

2 REPLIES 2
Most Valuable Professional

Try some extra brackets

``````If(
Sum(
Filter(
'Training',
'Email'.Email = User().Email,
'Completed' >= Date(2021,01,01),
'Completed' <=Date(2021,12,31)
),
'Hours'
)>= 20,
Green,
Red
)
``````

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.

Visit my blog Practical Power Apps

Frequent Visitor

Thank you!  That is exactly what I needed and saved me LOTS of frustration.   Have a great day!

Announcements

#### April 4th Copilot Studio Coffee Chat | Recording Now Available

Did you miss the Copilot Studio Coffee Chat on April 4th? This exciting and informative session with Dewain Robinson and Gary Pretty is now available to watch in our Community Galleries!   This AMA discussed how Copilot Studio is using the conversational AI-powered technology to aid and assist in the building of chatbots. Dewain is a Principal Program Manager with Copilot Studio. Gary is a Principal Program Manager with Copilot Studio and Conversational AI. Both of them had great insights to share with the community and answered some very interesting questions!     As part of our ongoing Coffee Chat AMA series, this engaging session gives the Community the unique opportunity to learn more about the latest Power Platform Copilot plans, where we’ll focus, and gain insight into upcoming features. We’re looking forward to hearing from the community at the next AMA, so hang on to your questions!   Watch the recording in the Gallery today: April 4th Copilot Studio Coffee Chat AMA