Project Botticelli

Status message

See the new version of this article on Tecflix.com! Why? Read our announcement.

DAX: Calculated Columns vs. Measures Purchase this course

16 August 2012 · 6 comments · 46743 views

Fundamentals of Data Analysis Expressions

DAX Formula for Classifying Data Using a Calculated Column

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.

Purchase A Full Access Subscription

Subscription Best Value

$480/year

Access all content for 1 year.
  Purchase on Tecflix

  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

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 finally 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 file 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?