560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources. |
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.
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.
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.
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:
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.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.