Guide: Creating IF, Nested IFs, and CASE Expressions with Examples

Published:

This article will guide you through creating IF, Nested IFs, CASE expression using a simple example. These type of expressions can be used to conditionally calculate values based on certain criteria.

Example Scenario

Consider the above table, which contains the field OrderQty. In the table, if you want to have an expression to return OrderQty as “high” when OrderQty is greater than 15.

Using IF and THEN expression:

Syntax:

if <condition> then <true action=""> else <false action="">

Example:

IF([OrderQty] > 15, 'High', 'Low')

This expression checks if OrderQty is greater than 15. If the expression result is true, then it returns the value high; if not, it returns low.

Using CASE:

You can use CASE and WHEN too to achieve this.

CASE  
  WHEN ([OrderQty] > 15) 
  THEN 'High'
  ELSE 'Low'
END

Nested IF expressions:

You can use any expression as any part of an IF and THEN statement. You can also “nested” If and Then expressions, allowing you to evaluate a series of dependent expressions.

Example:

IF([OrderQty] > 15, 'High', IF([OrderQty] < 8,'Low', 'Medium'))

The text “Medium” is the false part argument of the innermost IF and Then function. Since each nested IF and Then function is the false part argument of the IF and THEN function that contain it, the text “Medium” is only returned if all the expression arguments of all the IF and THEN functions evaluate to False which means the OrderQty is between 8 to 15.