This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Calculated columns (this blog) Using the function wizard 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.

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.

Calculated columns (this blog)
Using the function wizard
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

Calculated columns

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:

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.

Parts of this blog
Calculated columns (this blog) Using the function wizard Making choices (the IF and the SWITCH functions)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC