How to solve 20 nested IF ELSE in microsoft power automate?

[010920]

How to solve 20 nested IF ELSEs in microsoft power automate. Because creating an IF ELSE condition in Power Automate is limited to only 8 conditions.

[Chriddle]

Use an expression (or rethink the approach 😉)

[Tjan]

Hi @010920 , from my experience most often nested-ifs can be optimised by the 'Switch' function. That might also be a good option for you. I think switches allow 30 different cases (if I am not mistaken)

[010920]

Hi @Tjan, Thank you for your response and suggestions. Can the Switch Control function use IF AND for its condition?

Because I want to create a Priority matrix based on Urgency and Impact.

For example, if Urgency = Critical and Impact = Extensive / Widespread then Priority = Critical.
Next, 20 nesting conditions will be created.


saya buat formula di microsoft excel seperti ini:
IF(AND([@Urgency]="Critical", [@Impact]="Extensive / Widespread"), "Critical", IF(AND([@Urgency]="Critical", [@Impact]="Significant / Large"), "Critical", IF(AND([@Urgency]="Critical", [@Impact]="Moderate / Limited"), "High", IF(AND([@Urgency]="Critical", [@Impact]="Minor / Localized"), "Medium", IF(AND([@Urgency]="Critical", [@Impact]="Not Meaningful"), "Low", IF(AND([@Urgency]="High", [@Impact]="Extensive / Widespread"), "Critical", IF(AND([@Urgency]="High", [@Impact]="Significant / Large"), "High", IF(AND([@Urgency]="High", [@Impact]="Moderate / Limited"), "Medium", IF(AND([@Urgency]="High", [@Impact]="Minor / Localized"), "Low", IF(AND([@Urgency]="High", [@Impact]="Not Meaningful"), "Low", IF(AND([@Urgency]="Medium", [@Impact]="Extensive / Widespread"), "High", IF(AND([@Urgency]="Medium", [@Impact]="Significant / Large"), "Medium", IF(AND([@Urgency]="Medium", [@Impact]="Moderate / Limited"), "Low", IF(AND([@Urgency]="Medium", [@Impact]="Minor / Localized"), "Low", IF(AND([@Urgency]="Medium", [@Impact]="Not Meaningful"), "Very Low", IF(AND([@Urgency]="Low", [@Impact]="Extensive / Widespread"), "High", IF(AND([@Urgency]="Low", [@Impact]="Significant / Large"), "Medium", IF(AND([@Urgency]="Low", [@Impact]="Moderate / Limited"), "Low", IF(AND([@Urgency]="Low", [@Impact]="Minor / Localized"), "Very Low", IF(AND([@Urgency]="Low", [@Impact]="Not Meaningful"), "Very Low", ""))))))))))))))))))))

[Chriddle]

You can literally do the same thing with Power Automate's if() and and() expressions.

[Tjan]

Hi @010920 ! Well explicitly you can't use IF AND, but you're free to add conditions IN the switch elements themselves. I think the Switch is just a better starting point to keep things a bit more organised. Having a lot of nested-ifs can be really chaotic!

[010920]

Hi @Chriddle, thank you for your response and suggestions. I will try it.

[Chriddle]

Another approach:

In your expression you describe a matrix as follows:

Why don't you treat it as a matrix?

RiscCalc (Compose)

{
	"Urgency":{
		"Critical": 0,
		"High": 1,
		"Medium": 2,
		"Low": 3
	},
	"Impact":{
		"Extensive / Widespread": 0,
		"Significant / Large": 1,
		"Moderate / Limited": 2,
		"Minor / Localized": 3,
		"Not Meaningful": 4
	},
	"Matrix":[
		["Critical",	"Critical",		"High",		"High"		],
		["Critical",	"High",			"Medium",	"Medium"	],
		["High",		"Medium",		"Low",		"Low"		],
		["Medium",		"Low",			"Low",		"Very Low"	],
		["Low",			"Low",			"Very Low",	"Very Low"	]
	]

}

Compose

Gets the matrix value for Urgency: Medium and Impact: Significant / Large

outputs('RiscCalc')['Matrix']
	[outputs('RiscCalc')['Impact']['Significant / Large']
][outputs('RiscCalc')['Urgency']['Medium']]

[010920]

Thank you very much @Chriddle , the method you provided will really help me, but I don't know how to connect to the Sharepoint list that I have. 

[Chriddle]

Just show your list and describe your needs.
Maybe I can help😉

[010920]

Mr. @Chriddle ,
So here it is, I have created an automate to connect Microsoft Forms to the SharePoint List database.
This Microsoft form will later be used by employees to submit service requests / incident reports to our division.
In the Microsoft form there is a column, namely urgency, we get information about the urgency value from employees. Meanwhile, one of the other columns in the share point list are the Impact and Priority of our division which determines it.
I want to determine the value of the Priority column using a Priority matrix based on the Urgency and Impact values.

[Chriddle]

This will calculate and insert the value when a Sharepoint item is created in the list:

RiscCalc

Same as above

Compose

outputs('RiscCalc')['Matrix']
	[outputs('RiscCalc')['Impact'][triggerBody()['Impact']]
][outputs('RiscCalc')['Urgency'][triggerBody()['Urgency']]]

Update item

Id

triggerOutputs()?['body/ID']

Priority

outputs('Compose')

[010920]

Thank you so much Mr. @Chriddle , I will practice it now. But where can I find RiscCalc data operation?

Can I use "Compose" as the data operation tool, then change the name?

If that's the way it works, I've tried it. However, when I save the flow, a warning appears as below.

[Chriddle]

Yes, "RiscCalc" is a Compose (as shown above).

The circular loop warning is caused by the fact that you used the trigger "When an item is created or modified".

Since this flow modifies the item, this might trigger the flow again (and again)

Do you change the list manually after creation and want the calcualtion to happen again?

If so, you have to add a trigger condition that prevents circular loops:

Then the calculation and value adding only happens if this field is empty.

This means, if you change Urgency or Impact manually, remove the Impact value otherwise no new calculation will happen.

If not, just change the trigger to "When an item is created" (the one I used in my proposal).