Excel Dax 简明教程
Excel DAX - Performing Complex Calculations
DAX 公式可以执行涉及自定义聚合、筛选以及使用条件值在内的复杂计算。您可以使用 DAX 执行以下操作：
DAX formulas can perform complex calculations that involve custom aggregations, filtering, and the use of conditional values. You can do the following with DAX −
-
Create custom calculations for a PivotTable.
-
Apply a filter to a DAX formula.
-
Remove filters selectively to create a dynamic ratio.
-
Use a value from an outer loop.
Creating Custom Calculations for a PivotTable
DAX 函数 CALCULATE 和 CALCULATETABLE 功能强大，且灵活。它们对于定义计算域非常有用。这些 DAX 函数让您能够更改执行计算时的上下文。您还可以自定义要执行的聚合或数学运算的类型。
DAX functions CALCULATE and CALCULATETABLE are powerful and flexible. They are useful for defining calculated fields. These DAX functions enable you to change the context in which the calculation will be performed. You can also customize the type of aggregation or mathematical operation to perform.
CALCULATE Function
CALCULATE(<expression>, [<filter1>], [<filter2>]…)
CALCULATE (<expression>, [<filter1>], [<filter2>]…)
CALCULATE 函数在一个由零个或更多指定筛选器修改的上下文中计算给定的表达式。
CALCULATE function evaluates the given expression in a context that is modified by zero or more specified filters.
如果您的数据已过筛选，CALCULATE 函数将更改筛选数据的上下文，并在您通过筛选器指定的新的上下文中计算表达式。这意味着，将删除已指定列上的任何现有筛选器，而改为应用在筛选器自变量中使用的筛选器。
If your data has been filtered, the CALCULATE function changes the context in which the data is filtered and evaluates the expression in the new context that you specify by the filters. That means, any existing filters on the specified column are removed and the filter used in the filter argument is applied instead.
Example
假设您要按国家/地区名称筛选按运动分类的奖牌百分比。您的计算应该获取已覆盖您在数据透视表中对国家/地区应用的筛选器的百分比值。
Suppose you want to display the percentage of medals sport-wise filtered by Country names. Your calculation should get the percentage value overriding the filter that you apply on Country in the PivotTable.
-
Define a calculated field – Percentage of Medal Count as shown in the following screenshot.
通过这个 DAX 公式，结果表中的所有行都会在 CALCULATE 函数中考虑，其中筛选器包含 ALL 函数。通过这种方式，您在分母中有总数。
With this DAX formula, all the rows in the Results table are taken into account in the CALCULATE function with the filter containing the ALL function. This way, you have the total count in the denominator.
您的数据透视表将如下图所示。
Your PivotTable will be as shown in the following screenshot.
在上面的截图中，将国家/地区筛选为美国，并且在数据透视表中显示了前 18 个值。接下来，您可以在数据透视表中动态筛选值。但是，计算将通过您使用的自定义 DAX 公式来进行更正。
In the above screenshot, Country is filtered to USA and Top 18 values are displayed in the PivotTable. Next, you can dynamically filter values in the PivotTable. However, the calculations will be correct by the custom DAX formula that you used.
CALCULATETABLE 函数采用一个值表并执行与 CALCULATE 函数相同的操作。
The CALCULATETABLE function takes a table of values and performs the same action as that of CALCULATE function.
Filtering Data in Formulas
可以在 DAX 公式中创建筛选器，以从源数据中选择用作计算的值。为此，可以通过定义筛选器表达式以及使用作为 DAX 公式输入的表来实现。
You can create filters within DAX formulas, to select the values from the source data for use in calculations. You can do this by defining a filter expression and using it along with the table that is an input to the DAX formula.
筛选器表达式让你可以获取源数据的一个子集。筛选器在每次更新 DAX 公式的结果时会根据数据的当前上下文动态应用，你可以确信获得准确的预期结果。
The filter expression enables you to obtain a subset of the source data. The filter is applied dynamically each time that you update the results of the DAX formula, depending on the current context of your data and you can be assured of accurate and expected results.
筛选器表达式通常包含一个返回表中仅选择行的 DAX 筛选器函数，然后此函数可用作正在用于数据聚合的另一个 DAX 函数的参数。
The filter expression typically contains a DAX filter function that returns only selected rows of the table, which then can be used as an argument for another DAX function that you are using for data aggregation.
Example
以下屏幕截图显示了仅提供夏季运动奖牌数的计算字段的定义。
The following screenshot shows the definition of a calculated field that gives medal count only for summer sports.
使用此计算字段后，透视表会显示在下个屏幕截图中。
With this calculated field, the PivotTable looks as shown in the following screenshot.
正如你观察到的那样，带新计算字段的右侧透视表中的值与左侧透视表中的值匹配，左侧透视表明确地将筛选器应用到了赛季字段中。
As you can observe, the values in the PivotTable on the right side with the new calculated field match with those that are in the PivotTable on the left side with the filter on Season field applied explicitly.
Note − DAX 筛选器和值函数返回一个表，但从不会将表或行直接返回到数据模型中，因此始终嵌入在另一个 DAX 函数中。
Note − DAX filter and value functions return a table, but never return the table or rows directly to the Data Model and hence are always embedded in another DAX function.
有关这些 DAX 函数的详细信息，请参阅章节 - DAX 筛选器函数。
For details on these DAX functions, refer to the chapter – DAX Filter Functions.
Adding and Removing Filters Dynamically
在透视表中使用的 DAX 公式可能会受到透视表上下文的影响。然而，可以通过添加或移除筛选器，有选择地更改上下文。你可以使用 DAX 函数 ALL 和 ALLEXCEPT 来动态地选择行，而不管透视表上下文如何。
DAX Formulas that you use in a PivotTable can be affected by the PivotTable context. However, you can selectively change the context by adding or removing filters. You can use the DAX functions ALL and ALLEXCEPT to dynamically select the rows irrespective of the PivotTable context.
此外，你可以使用 DAX 函数 DISTINCT 和 VALUES 来返回不同的值。
Additionally, you can use the DAX functions DISTINCT and VALUES for returning distinct values.