Excel Dax 简明教程

Excel DAX - Evaluation Context

在 DAX 中，上下文是书写 DAX 公式时应注意的重要术语。DAX 上下文也称为 evaluation context ，用于确定 DAX 公式的计算和相应的结果。这意味着 DAX 公式结果可能根据上下文而有所不同。你应该清楚地了解如何使用某个特定 DAX 上下文，以及结果有何不同。

In DAX, context is an important term that you should be aware of, while writing DAX formulas. Also referred to as evaluation context, DAX context is used to determine the evaluation of a DAX formula and the corresponding result. This means, the results of a DAX formula can vary according to the context. You should clearly understand how a specific DAX context is used and how the results can be different.

计算上下文让你可以执行动态分析，其中 DAX 公式结果可以更改以反映当前行或单元格选择，以及任何相关数据。理解上下文并有效使用上下文对于构建强大的 DAX 公式、执行动态数据分析和排除 DAX 公式中的问题非常重要。计算上下文是 DAX 所有高级功能的基础，你需要掌握这些功能来创建复杂数据分析报表。

Evaluation context enables you to perform dynamic analysis, in which the results of a DAX formula can change to reflect the current row or a cell selection and also any related data. Understanding context and using context effectively are very important to build powerful DAX formulas, perform dynamic data analysis, and troubleshoot problems in DAX formulas. Evaluation contexts are the basis of all of the advanced features of DAX that you need to master to create complex data analysis reports.

当你在 DAX 公式中参考 DAX 函数以进行相关使用时，你需要参考本 DAX 上下文章节，以便清晰理解结果。

As you keep referencing to DAX functions for relevant usage in DAX formulas, you need to refer to this chapter on DAX context to obtain clarity on the results.

Types of Context in DAX

DAX 支持以下计算上下文 −

DAX supports the following evaluation contexts −

  1. Row Context

  2. Filter Context

当计算 DAX 公式时，将考虑所有上下文并按相关性应用。上下文共同存在，并且公式结果将根据计算值时使用的上下文而有所不同。例如，在数据透视表中选择行、列和筛选器的字段时，子合计将根据子合计/合计所关联的行和列进行动态计算，并且行和列中的值将由所使用的筛选器决定。

When a DAX formula is evaluated, all the contexts will be taken into account and are applied as relevant. The contexts exist together and the result of the formula will be different based on the context that is used while calculating a value. For example, when you select fields for rows, columns, and filters in a PivotTable, the subtotals are dynamically calculated based on which row and which column the subtotal/total is associated with and the values in the rows and columns are determined by the filters used.

Row Context

行上下文指 DAX 公式或 DAX 函数知道它在任何时间点引用的表中的哪行。可以将行上下文视为当前行。公式将针对行上下文逐行计算。

Row context means that the DAX formula or the DAX function knows which row of the table it is referencing at any point in time. You can consider row context as the current row. The formula will get calculated row-by-row with the row context.

某些 DAX 函数（例如 X 函数、FILTER()）和所有计算列均具有行上下文。例如，如果使用 DAX 公式 = YEAR ([Date]) 创建计算列 Year，则将逐行对表中的给定列应用给定的 DAX 公式，从而获取计算列的值。

Some DAX functions (e.g., the X-functions, FILTER ()) and all calculated columns have a row context. For example, if you create a calculated column Year with the DAX formula = YEAR ([Date]), the values of the calculated column are obtained by applying the given DAX formula on the given column in the table, row by row.

这意味着，如果已创建计算列，则行上下文将包含每个单独行中的值以及与当前行相关的列中的值，具体取决于所用的 DAX 公式。尽管 DAX 公式不包含对行的引用，但 DAX 在计算值时会隐式了解行上下文。

This means that if you have created a calculated column, the row context consists of the values in each individual row and the values in the columns that are related to the current row, as determined by the DAX formula used. Though the DAX formula does not contain the reference to a row, DAX implicitly understands the row context while calculating values.

当定义一个计算列并且所有使用 DAX 公式的计算值将出现在计算列中时，DAX 会自动创建一个行上下文。

DAX creates a row context automatically when you define a calculated column and all the calculated values with the DAX formula used will appear in the calculated column.

相反，当有 SUMX 等 DAX 函数时，逐行计算的值将被求和，并且仅显示最终结果。也就是说，中间值将被丢弃。

In contrast, when you have a DAX function such as SUMX, the values calculated row by row get summed up and only the final result will be displayed. That is, the intermediate values are discarded.

当有相关表时，行上下文确定相关表中的哪些行与当前行相关联。但是，行上下文不会自动通过关系传播。为此，必须使用 DAX 函数 - RELATED 和 RELATEDTABLE。

When you have related tables, the row context determines which rows in the related table are associated with the current row. However, the row context does not propagate through relationships automatically. You have to use the DAX functions - RELATED and RELATEDTABLE for this.

Multiple Row Context

DAX 具有迭代器函数，如 SUMX。可以使用这些函数嵌套行上下文。通过此操作，可以在程序中对内部循环和外部循环进行递归，其中可以有多个当前行和当前行上下文。

DAX has iterator functions like SUMX. You can use these functions to nest row contexts. With this, programmatically you can have a recursion over an inner loop and an outer loop, where you can have multiple current rows and current row contexts.

例如，可以使用 DAX 函数 Earlier()，该函数存储来自前一操作的行上下文。此函数在内存中存储两组上下文 - 一组代表公式内部循环的当前行，另一组代表公式外部循环的当前行。DAX 会自动在两个循环之间提供值，从而可以创建复杂的聚合。

For example, you can use the DAX function Earlier () that stores the row context from the operation that preceded the current operation. This function stores two sets of context in memory - one set of context represents the current row for the inner loop of the formula, and another set of context represents the current row for the outer loop of the formula. DAX automatically feeds the values between the two loops so that you can create complex aggregates.

有关示例，请参阅场景 - 创建动态对值进行排名的 DAX 公式一章中的场景 - 排名和比较值。

For an example, refer to the scenario - Creating a DAX Formula that Dynamically Ranks Values in the chapter Scenarios - Ranking and Comparing Values.

Filter Context

筛选上下文是指应用于 DAX 中的数据模型的任何筛选。筛选上下文由数据透视表以及 DAX 函数创建。

Filter context refers to any filtering that is applied to the Data Model in DAX. Filter context is created by a PivotTable and also by the DAX functions.

Filter Context Created by a PivotTable

由数据透视表创建的筛选上下文是数据透视表字段通过以下方式进行选择后应用的自然筛选：

Filter Context created by a PivotTable is the natural filtering that is applied by the selections made on the PivotTable fields from the following −

  1. Rows

  2. Columns

  3. Filters

  4. Slicers

由数据透视表创建的筛选上下文筛选数据模型中的基础表。如果表相互关联，则筛选器将从查找表向下流向数据表。这意味着，可以根据查找表中的结果筛选数据表。筛选器传播不会以相反的方式传递。但是，可以使用 DAX 公式根据数据表中的结果筛选查找表。

The filter context created by a PivotTable, filters the underlying tables in the Data Model. If the tables are related, then the filters flow down from the lookup tables to data tables. That means, you can filter the data tables based on the results from the lookup tables. The filter propagation does not happen the other way round. However, you can use DAX formulas to filter the lookup tables based on the results from the data tables.

Filter Context Created by DAX Functions

可以使用 DAX 筛选器函数定义计算字段和计算列，其中含有控制 DAX 公式所用值的筛选器表达式。然后，这些计算字段和计算列将成为数据透视表字段列表的一部分，并且可以将它们添加到数据透视表中。还可以使用这些 DAX 筛选器函数有目的地清除特定列的筛选器。创建筛选上下文的强大 DAX 筛选器函数的示例是 CALCULATE()。有关示例，请参阅场景 - 执行复杂计算一章。

You can use DAX Filter functions to define calculated fields and calculated columns, containing filter expressions that control the values used by the DAX formula. These calculated fields and calculated columns then become part of the PivotTable fields list and you can add them to the PivotTable. You can also selectively clear the filters on particular columns with these DAX Filter functions. An example of a powerful DAX Filter function to create Filter Context is CALCULATE (). For an example, refer to the chapter Scenarios - Performing Complex Calculations.

Filter Context as an Addition to Row Context

行上下文不会自动创建筛选上下文。可以使用包含 DAX 筛选器函数的 DAX 公式来实现相同的结果。

Row context does not automatically create a filter context. You can achieve the same with the DAX formulas containing DAX Filter functions.