cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
misc
Resolver II
Resolver II

Calculate if a number falls within a range, and conditional format based on that calculation

This is probably very simple but I've not been able to work it out and the closes forum thread I could find doesn't fit either.

 

My app calculates how many days a ticket has been open, and returns that number to the reader. I want this to highlight red/amber/green depending on what the number is eg. 0-7 days = green, 8-14 = amber, 15+=red. 

 

As you can see, at the moment, my formula only works it out on specific numbers, and my attempt to use < just broke it. I assume this is because it's reading it as Text, but when I tried to change the DaysOldValue.Text to .Value it didn't work (as you can see I have used Value in the 2nd & 3rd screenshot below). The Error this gives is 'Name isn't valid'.

 

disabledfill.png

 

defaultdaysold.png

defaultdaysold value.png

 

Thank you in advance

1 ACCEPTED SOLUTION

Accepted Solutions
v-albai-msft
Community Support
Community Support

Hi @misc ,

@CNT is correct, Value(DaysOldValue.Text) should work.

------------------------------------------------------------------------------------------

Besides, I see some issues from your formula, we should not use formula like "Value(DaysOldValue.Text) < 8 >14" to judge the number, instead, we should use "Value(DaysOldValue.Text)> 8 && Value(DaysOldValue.Text)< 14".

So based on your requirement, the complete formula should be:

If(
Value(DaysOldValue.Text)> 0 && Value(DaysOldValue.Text) < 7,RGBA(196, 237, 165, 1),
Value(DaysOldValue.Text) > 8 && Value(DaysOldValue.Text) < 14,RGBA(249, 202, 128, 1),
Value(DaysOldValue.Text) >= 15,RGBA(249, 142, 128, 1))

v-albai-msft_0-1621996641024.png

Best regards,

Allen

View solution in original post

3 REPLIES 3
CNT
Community Champion
Community Champion

@misc try replacing DaysOldValue.Value to Value(DaysOldValue.Text) everywhere in your formula.

v-albai-msft
Community Support
Community Support

Hi @misc ,

@CNT is correct, Value(DaysOldValue.Text) should work.

------------------------------------------------------------------------------------------

Besides, I see some issues from your formula, we should not use formula like "Value(DaysOldValue.Text) < 8 >14" to judge the number, instead, we should use "Value(DaysOldValue.Text)> 8 && Value(DaysOldValue.Text)< 14".

So based on your requirement, the complete formula should be:

If(
Value(DaysOldValue.Text)> 0 && Value(DaysOldValue.Text) < 7,RGBA(196, 237, 165, 1),
Value(DaysOldValue.Text) > 8 && Value(DaysOldValue.Text) < 14,RGBA(249, 202, 128, 1),
Value(DaysOldValue.Text) >= 15,RGBA(249, 142, 128, 1))

v-albai-msft_0-1621996641024.png

Best regards,

Allen

View solution in original post

Thank you! I had to make a tiny change of = alongside the > and < in your solution above as otherwise it wasn't counting 0 or 8 for example but otherwise that was a perfect solution. Thank you both!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,450)