Learn the difference between calculated columns and measures in Data Analysis Expression (DAX) in this free, 10-minute video by Marco Russo, a well-known book author on the subject, consultant at sqlbi.com, and a recognized speaker at international conferences like TechEd, PASS Summit and SQLBits. Log-in or get a free account to watch it!

DAX is the language used to define calculation expressions in PowerPivot and Microsoft SQL Server Analysis Services. The same DAX syntax can be used to define two different types of formulas. Calculated columns are calculated row-by-row when the content of a table is refreshed, whereas measures are computed at query time, by aggregating rows.

In the first demo shown in this video, we create a simple calculated column, based on the Sales table of the AdventureWorks DW sample database (download here). We calculate the product of quantity by price, obtaining the LineTotal column. In order to display an aggregation of LineTotal in a PivotTable, we need to define a measure. We’ll do that in two ways: first, by summing the previously defined LineTotal column, and later by using a SUMX function, which performs a row-by-row multiplication at query time, without relying on a calculated column. Knowing both techniques is important: even if a pure measure is usually better, aggregating a calculated column might be preferable whenever the row-by-row calculation is particularly complex.

An important difference between calculated columns and measures is that you can use the result of a calculated column in a slicer. We create a calculated column that classify unit price of products in three categories, implementing all the logic in a DAX formula, whose result is persisted row-by-row, every time the table is refreshed. We should always use calculated columns when they might be used as a filter criteria in the data model. However, we should always prefer measures over calculated columns, whenever possible, because of the lower consumption of RAM, thanks to the query-time evaluation of the DAX formula, whose results are not persisted.

As you now understand, the first step in using DAX is learning the difference between calculated columns and measures, in order to make the right choice at design time. Proceed now to learn the fundamentals of this language from the other videos in our series on DAX.

We would also like to recommend Alberto’s and Marco’s book, co-authored with Chris Webb: Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model.

Log in or register for free to access this content.