Power Pivot Principles: Resource Management with Calculated Columns

Welcome back to our Power Pivot blog.  Today, we discuss factors to consider when creating and using Calculated Columns.

If you do are not sure what a calculated column is, we have discussed calculated columns before here.

Calculated columns perform a calculation for every individual row in a given table, whereas a measure is only calculated for the filtered, aggregated cells that are used in a PivotTable or a PivotChart; because of this, the formula in a calculated column can be more resource intensive than a formula used in a measure (you can read more about measures here).

For instance, a calculated column in a table with a million rows will always have to calculate 1 million results.  A PivotTable will generally have filters and slicers culling the reporting table to much less than 1 million rows, furthermore any measure is only calculated for the subset of data in each cell in the PivotTable.

Also, note that if a formula in a calculated column has dependencies on object references, such as other columns and other expressions, the calculated column at the end of the dependency cannot be evaluated until the all of the other columns have been evaluated.  Updating data will cause the entire dependency chain to refresh.  This may slow down the responsiveness of the model if there are too many dependencies built into the model. 

Keeping these points in mind, unless it is absolutely necessary we should stick to creating measures rather than calculated columns when we can.

If multiple calculated columns are needed, we recommend the following:

• step out any formula that contain multiple dependencies, with results saved to columns so that we are able to validate the results and evaluate any impacts on performance
• a little more controversially, if updating data with numerous calculated columns with interdependencies, you might wish to consider setting the (re)calculation mode temporarily to manual.  Remember to switch the mode back to automatic after updating though. 

That’s it for this week!

Stay tuned for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.