Loading the player...
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.