Calculated Columns vs Measures in DAX

Level: Beginners
Original: Feb 2016

It has been many years since Matt wrote his first take on Calculated Columns vs Measures, back in 2014 when he wrote the blog post 5 common mistakes of self taught DAX students at PowerPivotPro.com.  Since then Matt expanded his ideas with the original release of this article back in Feb 2016, yet this topic continues to be relevant today.  Here, at Excelerator BI, we train a lot of people in Power BI and Fabric at our training courses, including Matt’s Supercharge Power BI online training course, and also help many more understand DAX and Power BI on various forums.  The number 1 mistake we see self-taught DAX students with an Excel background make is the inappropriate use and over use of Calculated Columns when they should be using Measures!  That is why I am updating this article for the current audience.

Moths to a Flame

Through no real fault of their own, Power BI developers coming from an Excel background are typically attracted to Calculated Columns like “moths to a flame” with sometimes equally dangerous side effects.

This is likely because Excel users feel very comfortable working in the table structure that exists in an Excel Spreadsheet.  In addition, self-taught DAX authors normally don’t know any better.

Take a look at the Table View window below (Adventure Works Sales Table).  When an untrained DAX user who is used to working in Excel looks at this table (which looks a lot like an Excel spreadsheet), the “natural thing to do” is to think “I’ve got Sales (1 below), I’ve got Cost (2), but I need Margin $ (Sales minus Cost)”.  So then they jump in and write a simple Calculated Column for Margin (3 below).

But this is 100% the wrong thing to do.  Please, please don’t do this!  Use Measures instead.

Calculated Columns are Evil

Matt was very critical of Calculated Columns in the past, to the extent that he suggested they are evil (as implied in the graphic below), but since then his (and my) opinion has mellowed.  Calculated Columns are not evil, in fact there are modeling challenges that are best solved using Calculated Columns (more on that later), but they certainly can be bad in the hands of the uninitiated, which is why we warn new DAX authors to steer clear of them unless they are absolutely certain it is the right approach.

Here is a list of reasons that Calculated Columns can be bad.

1. The results of your formula are calculated and stored in the table for every row in the table.  This takes up space on disk when you save your report and also uses space in memory when your report is open, and this will make your report less performant.  If your table is 100 rows then it probably doesn’t matter.  If your table is 100 million rows, then it can be a big problem!
2. The compression of Calculated Columns may not be as good as compression of imported columns.  This is because Calculated Columns are not able to be calculated until the imported columns’ data is available to use, as a result they are the last columns to be stored, and thus the last to compress.  The VertiPaq engine (Power BI’s column store database) therefore doesn’t give Calculated Columns the same prioritisation consideration it gives imported columns.
3. Calculated Columns are recalculated every time the workbook is refreshed against the data source.  This will slow down the refresh process (increasingly so for every additional Calculated Column).
4. You can unwittingly create circular reference conflicts in your tables (particularly data tables) for reasons that are very difficult for learners to understand (and beyond the scope of this article).

Write Measures Instead

The most practical reason you shouldn’t write Calculated Columns is that you simply don’t need to.  Power BI was built for Measures.  If Calculated Columns are Evil, then Measures are Angelic.  Here are the benefits of using Measures instead of Calculated Columns.

1. Your workbooks will be smaller and faster.  You are not “materialising” the results of every possible calculation in your table and storing it in memory and on disk (like with a Calculated Column).  Instead Measures are calculated on demand, and Measure calculations are normally lightening fast.  The only results that are materialised are the results that need to be displayed inside the report visual.
2. Power BI visuals naturally filter your data (semantic model) before calculations are completed.  This means that Measures only need to be calculated over the filtered subset of data, further reducing the number of calculation iterations required.

Take the Calculated Column mentioned earlier (Total Margin).  You can get the exact same outcome much more efficiently by writing the following measures.  Note the calculations below  are Measures, not Calculated Columns.

Total Sales = SUM(Sales[SalesAmount])

Total Cost = SUM(Sales[TotalProductCost])

Total Margin = [Total Sales] - [Total Cost]

When Calculated Columns are OK

I still believe Calculated Columns are extremely dangerous in the wrong hands, however as I have built depth of knowledge I have learnt more about the specific conditions when it is okay to use Calculated Columns.  Here are the general rules for when it is okay to use Calculated Columns.  For Excel users that are still learning; if you don’t know which to use, then it is recommend that you assume you should use a Measure at least until you are sure in your mind why you must use a Calculated Column.  With that in mind, it is generally OK to use Calculated Columns in the following circumstances.

1. When you need to use the results of your formula to filter your data model.  Measures can only ever be placed on a Values field well.  You can’t add a Measure to a Categorical field well (i.e. Rows and Columns of a Matrix, Slicers or the Filters Pane, or X-Axis of a Column Chart).  If you want or need to do this, then you need a Column and not a Measure.  Most of the time, it is preferable to get the column added in your data source rather than use a Calculated Column.  The benefits of loading it from the source include:
   • that you can re-use the source column in other workbooks without re-writing the calculated column
   • there is the potential for improved compression on import
   • the refresh of your workbook will be faster.

   But for times when that is not possible to import from the source, and it doesn’t make sense to build it using Power Query, then a Calculated Column is your friend.

2. When it doesn’t make sense to build it using Power Query. The most common reason you should choose to add a Calculated Column (using DAX) is if you need to leverage existing parts of the model to create the new data.  Again an Adventure Works example here should help.  Imagine you want to classify your customers in bands, of high sales, medium sales, or low sales.  You need a new column in your Customer table (lookup table) so you can use that column on a slicer in one of your reports.  If you tried to do this task inside Power Query, it would be quite some additional work.  You would have to work out the total sales for each customer in Power Query, and that would require you to
   1. Create a join between the Customer table and the Sales table
   2. Pre-aggregate the sales data for each customer
   3. Group the customers into the size bands using the business logic you need.
   4. Add the column (high, medium, low).

   The important point here is that items 1 and 2 will most likely already exist in the data model itself.  In the case of Adventure Works, the model already has a Customer table, a relationship to the Sales table, and a measure that aggregates the sales.  These features of the model can be used to easily add the new Calculated Column using DAX.  So in short, you should prefer a Calculated Column when it leverages the logic of your model (measures and relationships) so that you don’t have to repeat this logic inside Power Query.

3. When you are enhancing a Lookup (Dimension) table.  Lookup tables are normally smaller (less rows) and hence the negative impacts associated with Calculated Columns are not as significant.
4. When the results of the Calculated Column have a low cardinality.  Uniqueness of values in a column is the enemy of compression in Power BI.  So if your Calculated Column returns 2 possible unique values (eg Yes or No) and this column helps you with writing a complex Measure, then there are no problems with the Calculated Column.  Calculated Columns with a low cardinality compress really well.
5. When you have a very complicated formula that is very taxing on your report to the point where run time performance of a Measure calculation is so bad that it is preferable to “pre-calculate” and store the result in a column.  It is unlikely that a self taught Excel user will be writing such a complex formula in the early days of learning DAX, but it is worth noting that this is an acceptable and valid use case.

Where to learn more

1. Subscribe to Blogs and read all you can.
2. Read a book.  Matt’s book Supercharge Power BI covers the right way to use Calculated Columns as well as all the other topics that you need to learn to be good at Power BI, and any other tool that utilises the VertiPaq engine, such as Power Pivot for Excel.  Matt’s book focuses on giving you the theory as well as hands on practice.  We also keep a curated list of other great Power BI and Power Query books in our Knowledge Base.
3. Do some live training.  Students from our live training courses in Australia walk away with depth of knowledge that is difficult to replicate in other learning environments.  If you really want to gain deep understanding, then consider attending one of our live training courses.
4. If you prefer self-paced online training, you can enroll in Matt’s Supercharge Power BI Online Training for guided learning.