Power BI Intermediate Level: 46 - Calculated Columns in DAX - Basics

Table of Contents?|?Power BI Report File?| Sample Input

Short summary: You already know how to create calculated columns in Power Query. In this article, I will show you how to create Calculated Columns in DAX. Most use cases for calculated columns involve leveraging relationships or the evaluation of measures in columns. These are the cases which are difficult or impossible to achieve in Power Query.

So far, we have created all of our calculated columns in Power Query. There is actually a second way to create calculated columns, and that is in the data model using DAX syntax. As a reminder, DAX stands for Data Analysis Expressions, which is the language used for calculating the data points in your visuals.

Calculated Columns: Power Query vs DAX

Before I show you how to create calculated columns in DAX, let's address the elephant in the room here: In general, when should you create calculated columns in Power Query and when in DAX?

It is somewhat up to preference but here is my clear recommendation based on several years of Power BI experience:

• Create as many of your calculated columns as you can in Power Query. This way you have your calculated columns together with your other preprocessing steps.
• Only use DAX calculated columns if you want to leverage the relationships between the tables or use DAX measures or anything else that would be difficult or impossible to achieve in Power Query.

Our Sample Data Model

Let us now revisit the data model from the article on data modelling and table relationships. If you haven't read the article yet, please do so before continuing. This is our data model, with transactions in the FACT table as well as two dimension tables for stores and products. The tables relate via Store ID and Menu Item ID.

In the model view in Power BI, let's create the table relationships via the matching ID columns.

Creating Basic Calculated Columns in DAX

When creating calculated DAX columns, you can do arithmetic and logical operations between columns as well as use DAX functions or your own custom measures.

In our 46_FACT_Transactions table, let us create a Profit column, which is sales amount minus cost. Before we can do that, we also need a column with the cost from the related 46_DIM_Menu table. We can do this using the RELATED function, which looks up a column of a related dimension table. That table has to be on the 1-side of the relationship. In the Data view of Power BI, select the table FACT_Transactions and under the Table tools ribbon, click New Column.

Write the column name Menu Item Cost, then the equal sign, and then start typing RELATED. You see that an IntelliSense window opens that helps you to write your code. Note that you only get to see columns which are related to this table. You can navigate the selection with the up and down arrows on the keyboard to choose the Menu Item Cost column and then autocomplete by hitting the Tab key. The formula is:

Menu Item Cost = RELATED('46_DIM_Menu'[Menu Item Cost])        

You can change the formatting of the column to a Euro currency under the Column tools context ribbon.

The formula looks similar to how we define a measure. However, the difference is that for calculated columns, we have a row context, so we can use data from the same row or a row in a related table for the calculation. As noted in the paragraph Power Query vs DAX, this is a case we we leverage the table relationships in the data model. If you wanted to achieve the same result in Power Query, you would have to merge the two tables.

Now, let's add the profit column, which is sales amount minus menu item cost. When you want to use a column from the same table in the calculation, wrap the column name in square brackets. As usual, you can use autocomplete for quicker and easier writing of formulas. The formula for the calculated column is:

Profit = [Sales Amount Before Discount] - [Menu Item Cost]        

Note that in the data view on the bottom right, our two calculated columns have a special symbol. This is only for DAX calculated columns, whereas calculated columns created in Power Query have no special symbol.

Because we created the table relationships, we can create a table visual in the report view using columns from all three tables.

Conclusion

You now know that you can create calculated columns in either Power Query or in DAX. I recommend to use Power Query in most cases, however the choice is up to you. I recommend using calculated columns in DAX only if you want to leverage table relationships use DAX functions or custom measures.

In the next article, I will show you how to use custom measures in calculated columns for summarizing data of a related table.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article: CALCULATE Mastery - Using Measures in Calculated Columns