THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Avoiding calculated column in DAX

A calculated column is a DAX expression which is evaluated when the PowerPivot workbook is updated. It is very useful, but there are cases where you want to delay calculation at query time. For example:

  • You want to make part of the calculation depending on the selection in the PivotTable (i.e. selecting a scenario for a simulation)
  • You want to avoid the storage of another column in the PowerPivot workbook (it has a cost in terms of space, after all)

First of all, if you have to be worried about storage space, probably you have to look at your data source and check if the calculation can be made on source data, without requiring all the underlying columns to be imported. For example, if you have Quantity and Price and want to calculate Amount (as the product of the formers), you can make this calculation on the source data and import only Quantity and Amount (the Price can be calculated later as an average, which is what people usually want to do).

That said, the point here is that calculating the Amount is pretty simple in a calculated column.

'Orders'[Amount] = 'Orders'[Quantity] * 'Orders'[Price]

However, this formula is not working as calculated measure, because you will get this error message if you try to define a calculated measure with the expression above:

The value for column 'Quantity' in table 'Orders' cannot be determined in the current context.

To avoid this, you have to make a decision about the aggregation formula you want to use and then using the “X” version. For example, the Amount will be aggregated summing values, so it is safe writing this calculated measure:

'Orders'[Amount] = SUMX( 'Orders', 'Orders'[Quantity] * 'Orders'[Price] )

As I said at the beginning, this technique is fundamental whenever you want to delay the product operation at calculation time. In fact, you might have an expression other than a simple product, where part of the calculation depends on the current context of the calculation in the pivot table. More about this in a future post.

Published Friday, February 5, 2010 1:27 PM by Marco Russo (SQLBI)
Filed under: ,


No Comments
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement