Excel Dax 简明教程

Excel DAX - Recalculating DAX Formulas

Recalculation of a DAX formula 要求反映公式本身内数据的更改和更改。但是，重新计算 DAX 公式会产生性能成本。

Recalculation of a DAX formula is required to reflect changes in the data and changes in the formula itself. However, recalculating a DAX formula involves performance cost.

即便如此，为了获得准确的结果，重新计算也是必不可少的。在重新计算期间，会检查列依赖关系，如果列发生了更改、数据无效或以前可用的 DAX 公式中出现错误，您会收到通知。

Even then, to obtain accurate results, recalculation is essential. During recalculation, column dependencies are checked and you will be notified if a column has changed, if the data is invalid or if an error has appeared in a DAX formula that used to work.

Types of Recalculation

你有两种重新计算 DAX 公式的选项 −

You have two options for recalculating DAX formulas −

  1. Automatic Recalculation Mode (default)

  2. Manual Recalculation Mode

默认情况下，Power Pivot 会根据需要自动重新计算，同时优化处理所需的时间。但是，如果你使用的是复杂公式或非常大的数据集，并且想要控制更新时间，则可以选择手动更新计算。

By default, Power Pivot automatically recalculates as required while optimizing the time required for processing. However, you can choose to update calculations manually, if you are working with complex formulas or very large data sets and want to control the timing of updates.

DAX 公式的自动和手动重新计算模式都具有优势。但是，推荐的方式是采用自动重新计算模式。通过这种方式，你可以让 Power Pivot 数据保持同步，并防止因删除数据、名称或数据类型发生更改或缺少依赖关系而造成的问题。

Both automatic and manual modes of recalculating DAX formulas have advantages. However, the recommended way is to use automatic recalculation mode. This way you can keep the Power Pivot data in sync and prevent problems caused by deletion of data, changes in names or data types or missing dependencies.

Recalculating DAX Formulas Automatically

如果你选择 DAX 公式的默认重新计算模式，即自动重新计算，任何会导致任何 DAX 公式的结果发生更改的数据更改都会触发包含该 DAX 公式的整个列的重新计算。

If you choose the default mode of recalculating DAX formulas, i.e. recalculating automatically, any changes to data that would cause the result of any DAX formula to change will trigger recalculation of the entire column that contains the DAX formula.

如下更改始终需要重新计算 DAX 公式 −

The following changes always require recalculation of DAX formulas −

  1. Values from an external data source have been refreshed.

  2. The DAX formula itself is changed.

  3. Names of tables or columns that are referenced in the DAX formula have been changed.

  4. Relationships between tables have been added, modified or deleted.

  5. New calculated fields or calculated columns have been added.

  6. Changes have been made to other DAX formulas within the workbook, so columns or calculations that depend on those DAX formulas need to be recalculated.

  7. Rows have been inserted or deleted in the table.

  8. You applied a filter that requires execution of a query to update the data set. The filter could have been applied either in a DAX formula or as part of a PivotTable or PivotChart.

When to Use Manual Recalculation Mode?

在工作簿中完成所有必需的 DAX 公式之前，你可以使用手动重新计算模式。通过这种方式，你可以避免在仍然处于草稿状态的工作簿上计算公式结果的成本。

You can use manual recalculation mode until you are ready with all your required DAX formulas in your workbook. This way, you can avoid incurring the cost of computing formula results on the workbook that is still in the draft state.

你可以在下列情况下使用 DAX 公式的手动重新计算 −

You can use manual recalculation of DAX formulas in the following conditions −

  1. You are designing a DAX formula by using a template and want to change the names of the columns and tables used in the DAX formula before you validate it.

  2. You know that some data in the workbook has changed but you are working with a different column that has not changed so you want to postpone a recalculation.

  3. You are working in a workbook that has many dependencies and want to defer recalculation till you are sure that all the necessary changes have been made.

但是，您应该知道，只要工作簿配置为手动重新计算模式，就不会执行任何公式的验证或检查。这将导致以下结果：

But, you should be aware that as long as the workbook is configured to manual recalculation mode, any validation or checking of formulas is not performed. This will result in the following −

  1. Any new formulas that you add to the workbook will be flagged as containing an error.

  2. No results will appear in the new calculated columns.

Configuring the Workbook for Manual Recalculation

正如您所了解的，自动重新计算是任何工作簿的数据模型中的默认模式。要为手动重新计算配置工作簿，请执行以下操作：

As you have learnt, automatic recalculation is the default mode in the Data Model of any workbook. To configure a workbook for manual recalculation, do the following −

  1. Click the Design tab on the Ribbon in the Power Pivot window.

  2. Click the Calculation Options in the Calculations group.

  3. Click the Manual Calculation Mode in the dropdown list.

workbook for manual recalculation

Recalculating DAX Formulas Manually

要手动重新计算 DAX 公式，请执行以下操作：

To recalculate the DAX formulas manually, do the following −

  1. Click the Design tab on the Ribbon in the Power Pivot window.

  2. Click the Calculation Options field in the Calculations group.

  3. Click the Calculate Now field in the dropdown list.

recalculating dax formulas manually