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

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

Making choices (the IF and 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.

Before we begin - doing the categorisation in SQL

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

CREATE VIEW vwTransactions

-- 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

-- 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.

The IF function

This works in exactly the same way as the IF function in Excel. The syntax is:

=IF ( Some condition, What to do if it's true, What to do if it's false )

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.

The SWITCH function

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:

Firstly, see if the price for the row in question is less than 5 pounds. If it is, we've found something which equals the start expression TRUE(), so we can stop and return Cheap.
If the price wasn't less than 5 pounds, move on to the next value, and test whether the price is less than 10 pounds. If it is, we've found something which equals the start expression TRUE(), and we can stop and return Middling.
If the price wasn't less than 5 pounds and wasn't less than 10 pounds, we've run out of conditions, so the Else argument is assumed to equal Expensive, and this is returned from the formula.

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.

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

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