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
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.
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:
|*||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.
|Parts of this blog|
25 Aytoun Street