cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
therush321
Helper II
Helper II

Nested If Formula in a Compose Data operation

Hi

I am having issues trying to create a nested if formula in a Compose Data operation.

Formula:

if(outputs('Compose_-_Divide_and_Multiply') < 56, 'Poor Service',
if(and(outputs('Compose_-_Divide_and_Multiply') >56 , outputs('Compose_-_Divide_and_Multiply') < 60)), 'Under Expectation' ,
if(and(outputs('Compose_-_Divide_and_Multiply') >59 , outputs('Compose_-_Divide_and_Multiply') < 68)), 'Acceptable Service',
if(and(outputs('Compose_-_Divide_and_Multiply') >67 , outputs('Compose_-_Divide_and_Multiply') < 71)), 'Over Expectation',
if(outputs('Compose_-_Divide_and_Multiply') >70, 'Exceptional Service', "Error")

 Image.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
annajhaveri
Super User
Super User

@therush321  below is the correct expression

 

if(less(outputs('Compose_-_Divide_and_Multiply'), 56), 'Poor Service',
if(and(greater(outputs('Compose_-_Divide_and_Multiply'), 56), less(outputs('Compose_-_Divide_and_Multiply'), 60)), 'Under Expectation' ,
if(and(greater(outputs('Compose_-_Divide_and_Multiply'), 59), less(outputs('Compose_-_Divide_and_Multiply'), 68)), 'Acceptable Service',
if(and(greater(outputs('Compose_-_Divide_and_Multiply'), 67), less(outputs('Compose_-_Divide_and_Multiply'), 71)), 'Over Expectation',
if(greater(outputs('Compose_-_Divide_and_Multiply'), 70), 'Exceptional Service', 'Error')))))

 

The issues in your expression are as follows:

1. If expression were not closed properly

2. For greater than comparison, you need to use greater function instead of operator >

 

I assume the output of 'Compose_-_Divide_and_Multiply' would an integer value and not text value for expression to work.

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

View solution in original post

4 REPLIES 4
annajhaveri
Super User
Super User

@therush321  below is the correct expression

 

if(less(outputs('Compose_-_Divide_and_Multiply'), 56), 'Poor Service',
if(and(greater(outputs('Compose_-_Divide_and_Multiply'), 56), less(outputs('Compose_-_Divide_and_Multiply'), 60)), 'Under Expectation' ,
if(and(greater(outputs('Compose_-_Divide_and_Multiply'), 59), less(outputs('Compose_-_Divide_and_Multiply'), 68)), 'Acceptable Service',
if(and(greater(outputs('Compose_-_Divide_and_Multiply'), 67), less(outputs('Compose_-_Divide_and_Multiply'), 71)), 'Over Expectation',
if(greater(outputs('Compose_-_Divide_and_Multiply'), 70), 'Exceptional Service', 'Error')))))

 

The issues in your expression are as follows:

1. If expression were not closed properly

2. For greater than comparison, you need to use greater function instead of operator >

 

I assume the output of 'Compose_-_Divide_and_Multiply' would an integer value and not text value for expression to work.

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

View solution in original post

Paulie78
Super User
Super User

Couple of problems:

  1. Your greater than and less than operators are invalid.
  2. Those are not nested if expressions.

Because each of your statements should only evaluate to true once. I would take a slightly different approach and do something like this:

concat(
if(less(outputs('Compose_-_Divide_and_Multiply'),56), 'Poor Service', ''),
If(and(greaterOrEquals(outputs('Compose_-_Divide_and_Multiply'), 56), less(outputs('Compose_-_Divide_and_Multiply'), 60)), 'Under Expectation', ''),
If(and(greater(outputs('Compose_-_Divide_and_Multiply'), 59), less(outputs('Compose_-_Divide_and_Multiply'), 68)), 'Acceptable Service', ''),
If(and(greater(outputs('Compose_-_Divide_and_Multiply'), 67), less(outputs('Compose_-_Divide_and_Multiply'), 71)), 'Over Expectation', ''),
If(greater(outputs('Compose_-_Divide_and_Multiply'), 70), 'Exceptional Service', '')
)

That should give you what you want. Personally I would put the thresholds into a compose and use that as the basis for my comparison, but the expression above should work.

 

Note how I am using less, greaterOrEquals and greater for the comparisons.

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

@annajhaveri It is a floating number and works well.

Thank you very much for the help. Much appreciated. 👍

@Paulie78 thanks for the assistance. I have tried @annajhaveri  code and it does the job I need. Thank you nonetheless. 

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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.

Users online (2,225)