Loading the player...

If this message stays on, check that you have the latest Flash Player installed (http://get.adobe.com/flashplayer) and that your browser supports JavaScript.

Learn the diﬀerence 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.

DAX is the language used to deﬁne calculation expressions in *PowerPivot* and *Microsoft SQL Server Analysis Services* (00:23). The same DAX syntax can be used to deﬁne two diﬀerent 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 (01:02).

In the ﬁrst demo shown in this video, we create a simple calculated column (2:07), 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 deﬁne a *measure* (04:09). We’ll do that in two ways: ﬁrst, by summing the previously deﬁned *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 diﬀerence between calculated columns and measures is that you can use the result of a calculated column in a *slicer.* We create a calculated column (07:07) 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 ﬁlter criteria in the data model. However, we should always prefer *measures* over calculated columns (09:46), 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 ﬁrst step in using DAX is learning the diﬀerence 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.

- Introduction (00:08)
- What is DAX? (00:23)
- Calculated Columns and Measures (01:02)
- Demo: Creating a Simple Calculated Column (02:07)
- Demo: Creating Simple Measures (04:09)
- Demo: Creating a Conditional Calculated Column for Filtering Data (07:07)
- Characteristics of Calculated Columns (09:46)
- Summary (10:51)

## Comments

njaisson · 25 October 2012

How can I get the reference Excel document used in this video??

Marco Russo · 30 October 2012

The workbook is ﬁnally available here:

http://projectbotticelli.com/knowledge/dax-aw-sample-data-set

Marco Russo · 27 October 2012

Thanks for the comment!

We are working to provide the downloadable ﬁle very soon.

Martin_Ar · 24 October 2013

Nice video with good explanations. Thanks!

dostmuhammad · 14 October 2014

your formula for medium is wrong … the one in which you specify less than 50

Marco Russo · 15 October 2014

Sorry, what is the issue in the formula?