Excel Dax 简明教程

Troubleshooting DAX Formula Recalculation

当工作簿的数据模型中发生更改时,Power Pivot 会对现有数据进行分析,以确定是否需要重新计算,并以最有效的方式执行更新。

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 在重新计算 DAX 公式时处理以下内容:

Power Pivot handles the following, during recalculation of DAX formulas −

  1. Dependencies

  2. Sequence of recalculation for dependent columns

  3. Transactions

  4. 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 始终针对表执行完整重新计算,因为完整重新计算比检查更改值更高效。触发重新计算的更改可能包括删除列、更改列的数字数据类型或添加新列。这些更改被视为重大更改。但是,看似无关紧要的更改,例如更改列的名称,也可能触发重新计算。这是因为列的名称在 DAX 公式中用作标识符。

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.

在某些情况下,Power Pivot 可能确定列可以排除在重新计算之外。

In some cases, Power Pivot may determine that columns can be excluded from recalculation.

Sequence of Recalculation for Dependent Columns

在重新计算之前会计算依赖项。如果有相互依赖的多个列,Power Pivot 将遵循依赖项的顺序。这可确保列以最高速度按正确顺序处理。

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

诸如 NOW、RAND 或 TODAY 等 DAX 函数没有固定值,被称为可变函数。如果在计算列中使用此类 DAX 函数,则查询或筛选的执行通常不会导致对它们进行重新评估,以避免性能问题。

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.

只有在重新计算整个列时才重新计算这些 DAX 函数的结果。这些情况包括从外部数据源刷新或手动编辑数据,从而导致重新评估包含这些函数的 DAX 公式。

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.