DAX HANDBOOK
2.2 Calculated Columns

If you wish to follow along, you can find PBIX/Excel files at the bottom of the article. 

Authors

Krešimir Ledinski

Krešimir Ledinski

Microsoft certified expert in the field of Business Intelligence. His biggest passions are DAX, M, and data modeling.

Kristian Radoš

Kristian Radoš

Experienced data analyst. Advanced in SQL, PowerApps and M language.

Columns Overview

In general, the data model consists of Tables. Tables consist of Columns. Columns are used to form Filter context by plotting them on the canvas in filter fields. They are also used as building blocks of DAX Measures. We can say that columns are the most used objects in data modeling. In import mode, there can only be 2 types of columns:

  • Imported column – columns coming from the data source
  • Calculated columns – additional columns added from within the data model and created using DAX

Calculated columns are used to enrich your data model with additional, physically stored columns. A calculated column is always a part of a table and static in its nature.

In our demo model, we have a single table called Sales. In the Power BI table view (blue square in the picture above) we can see the structure of the selected table. Sales[SalesAmount] is a calculated column with the calculation SalesAmount = Sales[Price]*Sales[QTY]. If we observe values in the selected column, they change for each row of the table.
Someone coming from an Excel environment who is used to references finds this operation hard to grasp. How can the same formula produce different results for different rows of the table?

Row context concept

It does so because of a row context concept. The row context concept means that each row calculates the same formula, but since for each row there are different figures going in the calculation, so does the result differ.


When DAX operates in a row context, the following rules apply.

  1. The same formula applies to each row of the table

The value of each row in a calculated column is evaluated independently and in parallel.

  1. Each row can only see values from other columns of the same row (row record)

If we focus on the highlighted row, the same formula now operates on values of adjacent columns of the same row. When in row context, we can access any other column from the same row, but we cannot access values from other rows.

  1. There is no direct way of accessing other rows in a table

When working in a row context, the current row can only see values of the same row. For the highlighted row, the formula Sales[Price]*Sales[QTY] can only multiply the price of 2443.35 and the quantity of 28. There is no direct way we can tell the calculation to multiply the price by the value of the row 8 “cells” below the current one. If we observe the Power BI table view, we can see that rows aren’t indexed. There is no row 5, 10, 50, etc. Each row is a row for itself. A row cannot be accessed with a row reference (like in Excel where we could use references to access different rows of the same column (e.g. D10 * D50), but instead we need to use other techniques involving filtering to acquire similar results. Those techniques will be explained in later chapters.

Row context is one of the most important concepts in DAX. It is always present in Calculated Columns. We can also invoke virtual row context in a measure using iterative functions, which will be explained in the next chapter.

Calculated columns usage

Calculated columns can be used to return values or text. They should be used with care, especially in big models, because they consume the RAM memory of the model. And the more memory they consume, the bigger the model, the slower overall model performance.
Calculated columns are mostly used for the following purposes:

  • Adding additional Value columns (e.g. SalesAmount column in our previous example).
  • Adding additional Filter columns based on the conditions included in a formula.

E.g. Retail vs Wholesale is a calculated column in which we define “Retail” sales as sales with more than 10 QTY sold. We can now use that column as a filtering column in a visual.

Evaluation order for the picture above:

  1. We created a calculated column in a table Sales called [Retail vs Wholesales].
    • The calculated column is created in a row context of the Sales table.
  2. We can now plot that column on the visual and use its distinct values to form the original filter context.
  3. When we drag Column into a Values field of the visual, the model automatically creates an implicit measure and aggregates the data. In the case of a numeric column, the default aggregation is SUM. In the case of a Text column, default aggregation is First.

Materials

We wish to create the best possible content!

If you are a novice looking for a better explanation of any element of the topic, feel free to comment on the part you didn't quite understand!

If you are an expert in DAX and believe certain topic lacks important internals, your comments are more than welcomed!

COMMENTS

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments

OUR SERVICES

Prefer live training or consultations?

Table of Content

Table of Content

GET LATEST BI NEWS TO YOUR INBOX

Receive the latest updates on all business analyst news across all platforms.

By subscribing you are agreeing to our Privacy Policy.

Related blog posts

DAX HANDBOOK
7. VARIABLES

Variables are used in almost every measure you will create. The reason we introduce them last is that they use all other parts of DAX code to produce faster, more powerful and maintainable code. Variables are like containers of a part of the DAX code which can be used throughout...

Read more

DAX HANDBOOK
6.8 ALLSELECTED

Explanation ALLSELECTED is one of the most complex functions in DAX. When used improperly it can lead to unexpected results. As a rule of thumb, you should not use it in iterative functions. It is preferable to use it only as a CALCULATE filter remover, not as a table function....

Read more

DAX HANDBOOK
6.7 Lineage

What is Lineage? Lineage is a part of DAX mechanics that enables us to use tables as filter arguments for the CALCULATE function. It’s also used in row2filter context transition and other aspects of the data model that involve filter propagation throughout the model. We can state that lineage is...

Read more

DAX HANDBOOK
6.6 Crossfilter

Crossfilter is a feature of DAX when it filters the underlying dataset even though there aren’t any visual filters present. Introduction In this example, we will explain a very important feature of CALCULATE filter arguments. We will also explain why you should always prefer the combination of ALL/REMOVEFILTER + VALUES...

Read more
0
Would love your thoughts, please comment.x
()
x
Scroll to Top