Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
575 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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 ...
Written by Andy Brown
In this tutorial
This blog shows the basics of creating calculated columns in SSAS Tabular:
Amount is a calculated column - the amount for any transaction equals price times quantity.
A calculated column is a formula written in DAX (standing for Data Analysis eXpressions) which returns a value for every single row of a table,.
A calculated column is similar in this respect to a formula in Excel:
This Excel formula works out the amount for each row by multiplying the price for each transaction by the quantity of items bought.
The difference is that in Excel a formula can apply to certain rows only, whereas in SSAS Tabular a calculated column is always calculated for every single row of a table,.
There are two ways to create a calculated column: using the mouse or using the keyboard. If you're going to be progressing with SSAS Tabular, it's worth understanding both carefully.
To begin creating a calculated column, click under the Add Column heading to the right of the existing columns, and type =:
Starting to add a new calculated column.
Click anywhere at all on the first column to be referenced:
The column name is inserted into the formula, in square brackets.
Type in some operator (here we're multiplying):
Type in * to multiply this column's value for each row.
Click on the next column to include:
Click on the Quantity column to add it into the formula.
Press Enter to complete your formula:
SSAS Tabular creates your formula - you can right click on the column to rename it from the default CalculatedColumn1, as shown here.
The other way to create a calculated column is as follows. Begin by typing an = sign as for the mouse method:
We're starting to create a new calculated column.
Type in the important [ symbol to show a list of field names to choose from:
Here I've used the down arrow to choose the field I want to include.
When you've chosen the field you want to include (another good way to do this is to type in its first 2 or 3 letters), press the Tab key to select it:
SSAS Tabular fills in the name of the chosen field.
You can now proceed to type in the following characters to complete the formula:
Character | Why |
---|---|
* | Multiply by something. |
[ | Bring up a list of fields. |
Q | Choose the field beginning with this letter (Quantity). |
Tab | Fill in the chosen field name in the formula. |
Enter | Create the formula you've built up. |
You'll then have created the same calculated column as we did earlier using the mouse!
For the second part of this blog, I'll show how to use the function wizard to create more complicated formulae in SSAS Tabular.
Suppose that you want to be able to aggregate transaction volumes by the year in which they occur:
We'll create a column to show the month corresponding to any particular date.
Note that this is actually a bad idea, as it would be much better to do this using a calendar, but a) we haven't covered them in this blog yet and b) this would ruin a nice simple example!
To start with, click on the function wizard tool:
Click on the tool shown here.
To help you choose the right function, choose a category (it's usually pretty obvious what this should be):
Choose this category to find a function giving the year for any given date.
Find the function you want, and double-click on it:
Often it isn't as obvious as this!
SSAS Tabular puts the function name in your formula:
You can now complete the formula by choosing the PosDate field:
Here's the final formula you should get:
I've renamed the column too.
For the final part of this blog, I'll show how you can make choices using the IF or 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.
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.
You can learn more about this topic on the following Wise Owl courses:
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 2024. All Rights Reserved.