Troubleshooting DAX Formula Recalculation
Whenever changes occur in the Data Model of your workbook, Power Pivot performs an analysis of the existing data to determine whether recalculation is required and performs the update in the most efficient way possible.
Power Pivot handles the following, during recalculation of DAX formulas −
- Dependencies
- Sequence of recalculation for dependent columns
- Transactions
- Recalculation of volatile functions
Dependencies
When a column depends on another column, and the contents of that other column change in any way, all related columns might need to be recalculated.
Power Pivot always performs a complete recalculation for a table, because a complete recalculation is more efficient than checking for changed values. The changes that trigger recalculation might include deleting a column, changing the numeric data type of a column or adding a new column. These changes are considered as major changes. However, seemingly trivial changes, such as changing the name of a column might also trigger recalculation. This is because the names of the columns are used as identifiers in the DAX formulas.
In some cases, Power Pivot may determine that columns can be excluded from recalculation.
Sequence of Recalculation for Dependent Columns
Dependencies are calculated prior to any recalculation. If there are multiple columns that depend on each other, Power Pivot follows the sequence of dependencies. This ensures that the columns are processed in the right order at the maximum speed.
Transactions
Operations that recalculate or refresh data take place as a transaction. This means that if any part of the refresh operation fails, the remaining operations are rolled back. This is to ensure that data is not left in a partially processed state. However, you cannot manage the transactions as you do in a relational database or create checkpoints.
Recalculation of Volatile Functions
DAX functions such as NOW, RAND, or TODAY do not have fixed values and are referred to as volatile functions. If such DAX functions are used in a calculated column, the execution of a query or filtering will usually not cause them to be re-evaluated to avoid performance problems.
The results for these DAX functions are only recalculated when the entire column is recalculated. These situations include refresh from an external data source or manual editing of data that causes re-evaluation of DAX formulas that contain these functions.
However, such functions will always be recalculated if the functions are used in the definition of a Calculated Field.
