PowerPivot calculated columns are not dynamic

A quick and dirty one – in attempting some clever dynamic security modelling in DAX I was warned about a gotcha in my design – that calculated columns were only evaluated when the model processed, so any approach based on calculated columns was doomed to failure. I didn’t quite believe it so I decided to do a road test in PowerPivot. Using a simple data set of one Dimension and one Fact, like below:

Simple Data Model
Simple Data Model

I hooked them up in PowerPivot with a relationship between “Column” on both tables. Then, using the ISFILTERED() function I created a couple of calculations. One, at Row Level, that would return a value of 1 if I filtered on the Attribute column:

=IF(ISFILTERED(Dim[Attribute]),1,0)

Which I then put a SUM on top of. I also added one at measure level, perfoming a similar function:

FilterCheckAsMeasure:=IF(ISFILTERED(Dim[Attribute]),1,0)

Then I created a Pivot Table checking the results, and got this:

Results
Results

The takeaway being that filtering on the Attribute was picked up by the table level measure, but the calculated column did not change value.

You can have a check of this yourself in my sample workbook: DAX Calculated Columns Evaluation Order.

What does the documentation say?

Well, nothing terribly clear, but in this page there is this small paragraph:

When a column contains a formula, the value is computed for each row. The results are calculated for the column as soon as you create the formula. Column values are then recalculated as necessary, such as when the underlying data is refreshed.

It doesn’t say exactly when “recalculated as necessary” is necessary, but the implication is that it’s a model level change, rather than the switching of context, or in my case the changing of the user viewing the model.

So in summary, we have to assume that our calculated column values are fixed upon initial calculation, formula changes or data load (or processing in a Tabular model) and there isn’t a way to make the value in a given cell change.

3 thoughts on “PowerPivot calculated columns are not dynamic

  1. Hi James,

    Calculated columns are always stored at processing time, measures are always evaluated at run time. So calculated columns are great for row level calculations. This means that they can be retrieved very fast at query time, but also means that functions that use the current query context like IsFiltered or AllSelected can’t really be used effectively.

    In a lot of corporate Data Warehouse scenarios these sort of things are often calculated as part of the ETL, but in a PowerPivot world having the ability to do them in the model can be quite valuable (although now with Power Query that is another place where such transformations can be applied)

    Cheers
    Darren

  2. Thanks for the clarification Darren. It gets a bit confusing because the use of some of these forumlas is not blocked in a calc column even though they will never work in that way. It would be useful to have the capability to have dynamic columns, even if it did add processing overhead.

    Cheers, James

  3. Yeah, it can be confusing. However you should be able to do pretty much most of what you would need dynamic columns for by using the iterator functions (SUMX, MINX, MAXX< COUNTX) as they let you evaluate an expression at the grain of a given table expression and aggregate it.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>