Calculated columns based on conditions

When you calculate columns, you might want different results of an expression depending on whether certain conditions are fulfilled or not. Conditions can be set up in expressions by using the logical functionsIf() or Case().

If()

When you use the If() function, you specify a condition that is either true or false, followed by what to return if the condition is true, and what to return if it is false.

Examples

The expression

If(([Amount]<10),"low","high")

means, that if a row value in the column 'Amount' is lower than 10, the value 'low' is returned as the result, but if the value is 10 or higher, the value 'high' is returned.

This is illustrated below:

In other words, the If() function divides the rows in two groups; one group consisting of the rows for which the specified condition is true (returning 'low' as result), and the other group consisting of the rows, for which the specified condition is false (returning 'high' as result).

Note:

If the values to be returned are aggregated values, the two groups will be handled separately. This is exemplified in the table below by the added expression

If(([Amount]<10),Avg([Amount]),Avg([Amount]))

that contains the average aggregations. In this case, the same aggregation is specified for both of the groups, but different aggregations can be used.

For the row values less than 10, that is the 'true' group, the average 6 is returned ((5+7)/2), and for the 'false' group, the average 13 ((14+10+15)/3).

The if() function can also be used in combination with and or or. To illustrate, the expression

If(([Customer age]<20) and ([Gender]="Female"),"girl","other")

results in the added calculated column below:

Case()

If you want to write an expression, whose condition results are not restricted to true and false, you can use the case() function. Case() can be used in two different forms, both described in the following using examples.

Example 1

Assume prices of books and videos below have been reduced by 10% and 50% respectively, and the price list needs an update.

Because the reduction differs depending on the type of product, a case() expression is used to calculate the new prices:

case [Type]
when "Book" then [Price]*0.9
when "Video" then [Price]*0.5
else [Price]
end

Here case() simply checks 'Type' against each of the when options in the expression.

Example 2

In the second example, case() is used to evaluate test results for a number of students. There are three evaluation levels; Failed (total less than 100), Passed, and Excellent (total of 160 or more).

Case() in this example evaluates the conditions independently for each of the when options:

case  
when ([Test A] + [Test B])<100 then "Failed" 
when ([Test A] + [Test B])>=160 then "Excellent"
else "Passed"
end

This way, more complex conditions can be implemented than in example 1.

The resulting calculated column is shown below:

Note: In both the case() forms, multiple when/then conditions can be included in the expression.

Order execution of the conditions

Execution of the different parts of the case() expressions is made in top to bottom order. See the expression below, and the resulting column:

case
when [Number]<40 then "yes"
when [Number]<70 then "no"
else "x"
end

That is, numbers below 40 are set to 'yes' and will not change. The next part in the expression, set numbers below 70 to 'no', only affects the remaining rows.