Learn how to create calculated columns in a tabular model using DAX
Part one 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 (this blog)
  2. Using the function wizard
  3. Making choices (the IF and the SWITCH functions)

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.

Calculated columns

This blog shows the basics of creating calculated columns in SSAS Tabular:

Example calc column

Amount is a calculated column - the amount for any transaction equals price times quantity.

What are calculated columns?

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:

Excel formula

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.

Creating calculated columns using the mouse

To begin creating a calculated column, click under the Add Column heading to the right of the existing columns, and type =:

Add column

Starting to add a new calculated column.

Click anywhere at all on the first column to be referenced:

Adding first column

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:

Adding quantity

Click on the Quantity column to add it into the formula.


Press Enter to complete your formula:

Renaming a column

SSAS Tabular creates your formula - you can right click on the column to rename it from the default CalculatedColumn1, as shown here.


Creating calculated columns using the keyboard

The other way to create a calculated column is as follows.  Begin by typing an = sign as for the mouse method:

Starting a new column

We're starting to create a new calculated column.


Type in the important [ symbol to show a list of field names to choose from:

Choosing a field name

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:

Tab key inserts a field

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. 

This blog has 0 threads Add post