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 (this blog)
- Using the function wizard
- Making choices (the IF and the SWITCH functions)
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.
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.
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:
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 =:
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.
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:
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:
|*||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.