BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Calculated columns
- Using the function wizard
- 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:
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.
- Calculated columns
- Using the function wizard
- Making choices (the IF and the SWITCH functions) (this blog)