WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 479 reviews for our classroom and online training
If you found this blog useful and you’d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

Learn how to create calculated columns in a tabular model using DAX
Part three of a three-part series of blogs

Creating calculated columns in tabular models is deceptively similar to creating formulae in Excel. Deceptively, because the underlying language (DAX) is completely different, and you can make use of functions like SWITCH which have no Excel equivalent. This blog shows what calculated columns are, and how to create them.

1. Calculated columns
2. Using the function wizard
3. Making choices (the IF and the SWITCH functions) (this blog)

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 05 January 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Making choices (the IF and the SWITCH functions)

Suppose that you want to categorise purchases into the following 3 bands:

Band What it contains
Cheap Items costing less than 10 pounds
Middling Items costing more than 5 pounds but less than 10
Expensive Items costing 10 pounds or more

(For overseas readers, a pound is just like a dollar, but better).

There are several ways to create these categories.  This blog shows two: using an IF function, and using SWITCH.

You can also use the EARLIER function to achieve the same result, as shown in a later blog.

## Before we begin - doing the categorisation in SQL

If you know SQL and have the right to create views in your database, the easiest way to solve the above problem is to use a CASE ... WHEN statement:

USE MAM

GO

CREATE VIEW vwTransactions

AS

-- show the transaction fields

SELECT

t.TransactionId,

t.ProductId,

t.PosId,

t.Price,

t.Quantity,

-- work out the category

CASE

WHEN t.Price < 5="">THEN 'Cheap'

WHEN t.Price < 10="">THEN 'Middling'

ELSE 'Expensive'

END AS Category

FROM

tblTransaction AS t

GO

-- test this out

SELECT * FROM vwTransactions

ORDER BY TransactionId

This would give the following results: The final column gives the price category.

You could then import the view created by this SQL into your tabular model, avoiding the need to create a calculated column.

## The IF function

This works in exactly the same way as the IF function in Excel.  The syntax is:

=IF ( Some condition, What to do if it's true, What to do if it's false )

However, this can only take us so far: Here we've catered for the situation where the price is less than 5 pounds, but we still need to distinguish between the other two cases.

The solution is to embed one IF function within another, although it doesn't look pretty! If the price is less than 5, show Cheap. Otherwise, if the price is less than 10, show Middling; otherwise, show Expensive.

Here's what this would give: The different categories are calculated correctly.

## The SWITCH function

To avoid nested IF functions, you can use the more powerful SWITCH function, which allows you to include multiple comparisons: The syntax isn't obvious from this!

The arguments are:

Number Argument What it means
1 Expression The field or formula you're testing against
2 Value1 The first thing you're testing for
3 Result1 What to return if you find it
... ... ...
Final Else If you still haven't found what you're looking for (shades of U2?), use this value.

This is much easier to understand with an example, so let's use the one above: This formula returns the same price categories as did the nested IF function above.  The arguments are explained below.

The first argument in a SWITCH formula is often TRUE(), since you're often trying to find when a particular condition first becomes true.

Here's what the above formula means:

• Firstly, see if the price for the row in question is less than 5 pounds.  If it is, we've found something which equals the start expression TRUE(), so we can stop and return Cheap.
• If the price wasn't less than 5 pounds, move on to the next value, and test whether the price is less than 10 pounds.  If it is, we've found something which equals the start expression TRUE(), and we can stop and return Middling.
• If the price wasn't less than 5 pounds and wasn't less than 10 pounds, we've run out of conditions, so the Else argument is assumed to equal Expensive, and this is returned from the formula.

One final note about the SWITCH function - internally it's converted to a nested IF function before running, so it won't actually save any processing time.

1. Calculated columns
2. Using the function wizard
3. Making choices (the IF and the SWITCH functions) (this blog)