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:

Transactions by category

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:

Half an IF function

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!

The full formula

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:

Different price categories

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:

SWITCH function

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:

Price category formula

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)
This blog has 0 threads Add post