Microsoft SQL Server 2012 Analysis Services: DAX Basics

  • 7/15/2012
Contents

Calculated Columns

If you want to create a calculated column, you can move to the last column of the table, which is named Add Column, and start writing the formula. The DAX expression must be inserted into the formula bar, and Microsoft IntelliSense helps you during the writing of the expression.

A calculated column is just like any other column in a Tabular table and can be used in rows, columns, filters, or values of a Microsoft PivotTable. The DAX expression defined for a calculated column operates in the context of the current row of the table to which it belongs. Any reference to a column returns the value of that column for the row it is in. You cannot access the values of other rows directly.

NOTE

As you see later, there are DAX functions that aggregate the value of a column for the whole table. The only way to get the value of a subset of rows is to use DAX functions that return a table and then operate on it. In this way, you aggregate column values for a range of rows and possibly operating on a different row by filtering a table made of only one row. More on this topic is in Chapter 5, “Understanding Evaluation Context.”

One important concept that must be well understood about calculated columns is that they are computed during the Tabular database processing and then stored in the database, just as any other column. This might seem strange if you are accustomed to SQL-computed columns, which are computed at query time and do not waste space. In Tabular, however, all calculated columns occupy space in memory and are computed once during table processing.

This behavior is handy whenever you create very complex calculated columns. The time required to compute them is always process time and not query time, resulting in a better user experience. Nevertheless, you must always remember that a calculated column uses precious RAM. If, for example, you have a complex formula for a calculated column, you might be tempted to separate the steps of computation into different intermediate columns. Although this technique is useful during project development, it is a bad habit in production because each intermediate calculation is stored in RAM and wastes space.

Save to your account

This chapter is from the book