Excel Dax 简明教程

Excel DAX - Quick Guide

Excel DAX - Overview

DAX 代表数据分析表达式。DAX 是一种公式语言，它是由可以在公式或表达式中使用的函数、运算符和常量组成的集合，可计算并返回一个或多个值。DAX 是与 Excel Power Pivot 的数据模型关联的公式语言。

DAX stands for *D*ata *A*nalysis E*x*pressions. DAX is a formula language and is a collection of functions, operators, and constants that can be used in a formula or expression to calculate and return one or more values. DAX is the formula language associated with the Data Model of Excel Power Pivot.

它不是编程语言，而是一种公式语言，允许用户在计算列和计算字段（也称为度量）中定义自定义计算。DAX 帮助您从数据模型中已经存在的数据中创建新信息。DAX 公式使您可以执行数据建模、数据分析，并将结果用于报告和决策制定。

It is not a programming language, but is a formula language that allows the users to define custom calculations in calculated columns and calculated fields (also known as measures). DAX helps you create new information from the data that is already present in your Data Model. DAX formulas enable you to perform data modeling, data analysis, and use the results for reporting and decision making.

DAX 包含一些用于 Excel 公式的函数，但具有经过修改的功能，另外还有用于处理关系数据和执行动态聚合的其他功能。

DAX includes some of the functions that are used in Excel formulas, but with modified functionality and additional functions that are designed to work with relational data and perform dynamic aggregation.

Importance of DAX

DAX 的基础是 Excel 中的 Power Pivot 数据库的数据模型。数据模型由可以定义关系的表组成，以便合并来自不同源的数据。数据模型与数据源的连接可以根据源数据更改随时刷新。数据模型利用 Power Pivot xVelocity 内存分析引擎 (VertiPaq)，该引擎可以使数据操作尽可能快速，还可以容纳数千行数据。有关数据模型的更多信息，请参考教程 - Power Pivot。

The foundation of DAX is the Data Model that is the Power Pivot database in Excel. Data Model consists of tables between which relationships can be defined so as to combine the data from different sources. The data connections to the Data Model can be refreshed as and when the source data changes. Data Model makes the use of the Power Pivot xVelocity in-memory analytics engine (VertiPaq) that makes the data operations to be as quick as possible in addition to accommodating several thousands of rows of data. For more information on Data Model, refer to the tutorial – Power Pivot.

DAX 与数据模型结合，能实现 Excel 中几个强大特性——Power Pivot、Power 透视表、Power 透视图表和 Power View。您可以使用 DAX 解决一系列基础计算和数据分析问题。

DAX in conjunction with Data Model enables several power features in Excel – Power Pivot, Power PivotTables, Power PivotCharts and Power View. You can use DAX to solve a number of basic calculations and data analysis problems.

DAX 在 Power BI 中也非常有用，用于创建新的 Power BI Desktop 文件并导入一些数据。此外，DAX 公式还提供一些功能，例如分析产品类别的增长百分比，针对不同日期范围计算同比增长与市场趋势相比，以及其他很多功能。

DAX is also useful in Power BI to create a new Power BI Desktop file and import some data into it. Further, DAX formulas provide capabilities such as analyzing growth percentage across product categories and for different date ranges, calculating year-over-year growth compared to market trends and many others as well.

学习如何创建有效的 DAX 公式将帮助您充分利用您的数据。当您获得所需信息时，您可以开始解决影响您最终收入的实际业务问题。这是 Power BI 的强大功能，而 DAX 将帮助您实现这些功能。

Learning how to create effective DAX formulas will help you get the most out of your data. When you get the information you need, you can begin to solve real business problems that affect your bottom line. This is the power in Power BI and DAX will help you get there.

Prerequisites for this Tutorial

本教程是 Excel Power Pivot 教程的扩展，您可以在其中了解 Power Pivot 特性、数据模型、关系、Power 透视表、Power 透视图表等。进一步了解本教程将有助于您深入了解 DAX，因为本教程更侧重于 DAX 语言，其中您为数据模型的数据分析编写公式并报告这些结果。

This tutorial is an extension to Excel Power Pivot tutorial, where you have learnt about the Power Pivot feature, Data Model, Relationships, Power PivotTables, Power Pivot Charts, etc. It would be a good idea to brush up on this tutorial before you delve into DAX as this tutorial is more on the DAX language wherein you write formulas for the analysis of data in the Data Model and report those results.

本教程还介绍了 DAX 函数，这些函数类似于 Excel 函数，但有一些变化。提供了 Excel 函数和 DAX 函数的比较以帮助您区分两者。同样，对 Excel 公式和 DAX 公式进行了比较，并讨论了它们的相似性和差异性。很好地理解这些差别将帮助您有效地编写有效的 DAX 公式。

This tutorial also introduces DAX Functions that are like Excel Functions, but with some variations. A comparison of Excel Functions and DAX Functions is provided to help you distinguish both. Similarly, Excel formulas and DAX formulas are compared and the similarities and differences are discussed. A good understanding of these differences would help you in writing effective DAX formulas efficiently.

本教程不需要了解 Excel 函数和 Excel 公式，因为 DAX 完全用于 Power Pivot 窗口中的数据模型。您将只能进入 Excel 工作表以查看基于数据模型的 Power 透视表、Power 透视图表和 Power View 可视化。但是，如果您是一位 Excel 专业人士，并且对 Excel 函数和公式有相当丰富的了解，那么最好记下上一节中提到的内容和本教程中给出的详细信息。

Knowledge of Excel Functions and Excel Formulas is not necessary for this tutorial, as DAX is entirely for the Data Model in the Power Pivot window. You will get into an Excel worksheet only to view the Power PivotTables, Power Pivot Charts and Power View visualizations that are based on Data Model. However, if you are an Excel professional with good amount of knowledge in Excel Functions and Formulas, better make a note of what is mentioned in the previous section and the details given in the course of this tutorial.

Calculated Columns

计算列是您可以通过 DAX 公式添加到数据模型中的列。您已经在 Excel Power Pivot教程中了解了它们，但您将在章节——计算列中详细了解，因为 DAX 完全是关于计算列、计算域和 DAX 函数的。

Calculated columns are the columns that you can add to a table in the Data Model, by means of a DAX formula. You have already learnt about them in Excel Power Pivot tutorial, but you will learn in detail in the chapter – Calculated Columns as DAX is all about calculated columns, calculated fields, and DAX functions.

Calculated Fields / Measures

您不能通过编辑更改数据模型中的值。但是，您可以向表中添加可用于 Power 透视表中的计算域。计算域通过赋予名称和定义 DAX 公式来定义。有关更多详细信息，请参阅第二章—— Calculated Fields 。

You cannot change the values in the tables in the Data Model by editing. However, you can add calculated fields to a table that can be used in the Power PivotTables. The calculated fields are defined by giving a name and by defining a DAX formula. For details, refer to the chapter – Calculated Fields.

在 Excel 2013 之前的 Excel 版本中，计算域被命名为度量。在 Excel 2016 中，它们被重新命名为度量。在本教程中，我们将它们称为计算域。但是，请注意，术语“计算域”和“度量”是同义词，在所有方面都指代同一事物。

The calculated fields were named as measures in the Excel versions prior to Excel 2013. They are renamed back to measures in Excel 2016. In this tutorial, we will refer them as calculated fields. But, note that the terms - calculated fields and measures - are synonymous and refer to the same in all aspects.

在定义并存储计算域之后，您可以对其进行编辑。您可以更改定义中使用的 DAX 公式，也可以重命名计算域。您将从中了解更多信息章节—— Editing a Calculated Field 。您可以删除计算域。请参阅章节—— Deleting a Calculated Field 。

You can edit a calculated field after it is defined and stored. You can change the DAX formula used in the definition or you can rename the calculated field. You will learn about this in the chapter – Editing a Calculated Field. You can delete a calculated field. Refer to the chapter – Deleting a Calculated Field.

DAX Formulas

DAX 公式是 DAX 语言的核心。您可以通过使用 DAX 公式定义计算域和计算列来创建它们。您可以编写 DAX 公式进行数据分析操作。DAX 公式不引用表中的单个单元格或单元格范围，而是引用数据模型中的表格和列。数据模型中表中的列必须包含相同的数据类型。

DAX formulas form the heart of the DAX language. You can create calculated fields and calculated columns by defining them with DAX formulas. You can write DAX formulas for the data analysis operations. DAX formulas do not refer to the individual cells or range of cells in the table, but refer to the tables and columns in the Data Model. A column in a table in the Data Model must contain the same data type.

DAX 公式包含表、列、计算列、计算域、DAX 运算符和 DAX 函数。有关更多详细信息，请参阅章节—— DAX Formulas 。

DAX formulas contain the tables, columns, calculated columns, calculated fields, DAX operators, and DAX functions. Refer to the chapter – DAX Formulas to learn in detail.

DAX Syntax

正如任何语言一样，公式语言 DAX 也有其语法。您的 DAX 公式应遵循 DAX 语法，否则，您将在设计时或运行时收到错误，或者将收到不正确的结果。

As is the case with any language, DAX, the formula language also has a syntax. Your DAX formulas should follow DAX syntax, or else, you will either get errors at design time or at run time or you will receive incorrect results.

您将在章节—— DAX Syntax 中了解以下内容：

You will learn the following in the chapter – DAX Syntax −

  1. DAX naming requirements for Tables, Columns

  2. DAX operators

  3. DAX special values

  4. DAX data types

  5. DAX implicit data type conversions

DAX Operators

DAX 是一种公式语言，因此在定义公式时使用了运算符。DAX 具有以下类型的运算符——

DAX is a formula language and hence makes the use of the operators in defining the formulas. DAX has the following types of operators −

  1. DAX Arithmetic Operators

  2. DAX Comparison Operators

  3. DAX Text Concatenation Operator

  4. DAX Logical Operators

还定义了 DAX 运算符优先级顺序，并且不同于 Excel 运算符优先级顺序。有关更多详细信息，请参阅章节—— DAX Operators 。

DAX operator precedence order is also defined and varies from Excel operator precedence order. Refer to the chapter – DAX Operators.

DAX Standard Parameters

DAX 函数语法对参数有特定的要求。这是因为 DAX 函数参数可以是表格、列或计算域或其他 DAX 函数。请参阅第二章 - DAX Standard Parameters 。

DAX Function syntax has certain requirements on parameters. This is because the DAX function arguments can be tables or columns or calculated fields or other DAX functions. Refer to the chapter - DAX Standard Parameters.

DAX Functions

在 DAX 公式中可以使用 Excel 2013 的 246 个 DAX 函数。您将在本章节中按照类别等级了解这些函数—— DAX Functions 。然而，对于每个 DAX 函数的语法、参数、用法和返回值的详细信息，您必须参考我们的教程—— DAX Functions 。每个 DAX 函数描述中使用的部分名称在章节中给出—— Understanding DAX Functions 。

Excel 2013 has 246 DAX functions that you can use in DAX formulas. You will learn about these functions at the category level in the chapter – DAX Functions. However, for details on each DAX function syntax, parameters, usage and return values, you have to refer to our tutorial on – DAX Functions. The section names used for the description of each DAX function is given in the chapter – Understanding DAX Functions.

由于在编写 DAX 公式时需要 DAX 函数，并且所使用 DAX 函数的结果取决于它们所使用的上下文，因此您可能需要在这两个教程之间来回切换，才能掌握您将在数据建模中使用 DAX 的 DAX 和 Power BI。

As DAX functions are required in writing the DAX formulas and the results of the DAX functions used depend on the context they are used, you might have to go back and forth between these two tutorials to get a grasp on DAX that you will use in Data Modeling with DAX and Power BI.

DAX Special Functions

DAX 有一些函数使 DAX 变得强大。这些 DAX 函数属于类别——DAX 时间智能函数和 DAX 筛选器函数，并要求特别提及。您将在章节中学习 DAX 时间智能函数—— Understanding DAX Time Intelligence 。您将在章节中学习 DAX 筛选器函数的使用—— DAX Filter Functions 。

DAX has some functions that make DAX powerful. These DAX functions come under the categories – DAX time intelligence functions and DAX filter functions and require a special mention. You will learn about DAX time intelligence functions in the chapter – Understanding DAX Time Intelligence. You will learn about the usage of DAX filter functions in the chapter – DAX Filter Functions.

DAX Evaluation Context

DAX 公式的结果可能会根据用于求值的上下文而有所不同。DAX 有两种类型的求值上下文——行上下文和筛选器上下文。请参阅章节—— DAX Evaluation Context 。

The results of a DAX formula can vary based on the context that is used for evaluation. DAX has two types of evaluation context – Row Context and Filter Context. Refer to the chapter - DAX Evaluation Context.

DAX Formulas

DAX 是一种公式语言，您必须在编写 DAX 公式中充分利用它。请参阅章节——DAX 公式，以了解公式语法以及如何轻松正确地创建它们。

DAX is a formula language and you have to get the most of it in writing the DAX formulas. Refer to the chapter - DAX Formulas to learn about the formula syntax and how to create them easily and correctly.

只要刷新数据和重新计算 DAX 公式，DAX 公式的结果就会更改。您必须了解数据刷新和重新计算之间的差异。请参阅章节—— Updating the Results of DAX Formulas 。

The results of the DAX formulas change whenever the data is refreshed and whenever the DAX formulas are recalculated. You have to understand the difference between data refresh and recalculation. Refer to the chapter - Updating the Results of DAX Formulas.

预期数据模型中的数据会随时间而改变。这是因为数据用于数据分析活动，在任何时间点都需要最新数据。要了解刷新数据的不同方式，请参阅章节—— Updating Data in Data Model 。

Data in the Data Model is expected and subjected to change from time to time. This is because the data is used for data analysis activities that require up-to-date data at any point of time. To understand the different ways of refreshing data, refer to the chapter - Updating Data in Data Model.

您将在章节中了解不同类型的 DAX 公式重新计算—— Recalculating DAX Formulas 。

You will understand the different types of DAX formula recalculation in the chapter - Recalculating DAX Formulas.

DAX 公式重新计算必须考虑数据依赖关系并遵循特定顺序。否则，您可能会遇到错误或错误的结果。请参阅章节—— Troubleshooting DAX Formula Recalculation 了解更多详情。

DAX formula recalculations have to consider data dependencies and follow a specific order. Otherwise, you might get errors or erroneous results. Refer to the chapter - Troubleshooting DAX Formula Recalculation for details.

您将深入了解一些常见的 DAX 公式错误，您将在章节中学习如何修复这些错误—— DAX Formula Errors 。

You will get an insight into some of the common DAX formula errors and you will learn how to fix those errors, in the chapter - DAX Formula Errors.

DAX Scenarios

如果您开始学习一门新语言，那么熟悉这门语言的最佳方法是了解在何处使用什么。同样，DAX 作为一种用于数据分析的公式语言，您需要了解可以在其中使用它的各种场景。

If you start learning a new language, the best way of getting acquainted to the language is by understanding where to use what. Similarly, DAX being a formula language meant for data analysis, you need to understand the various scenarios where it can be used.

请参阅以下章节以获取详细信息。

Refer to the following chapters to get details on this.

  1. DAX Scenarios

  2. Scenarios - Performing Complex Calculations

  3. Scenarios - Working with Text and Dates

  4. Scenarios - Conditional Values and Testing for Errors

  5. Scenarios - Using Time Intelligence

  6. Scenarios - Ranking and Comparing Values

Excel DAX - Calculated Columns

@{s1} 是通过定义列值的 DAX 公式将您添加到工作簿数据模型中现有表中的列。您将创建计算列，而不是导入列中的值。

A calculated column is a column that you add to an existing table in the Data Model of your workbook by means of a DAX formula that defines the column values. Instead of importing the values in the column, you create the calculated column.

您可以在数据透视表、数据透视图、Power 透视表、Power 透视图表或 Power View 报表中使用计算列，就像使用任何其他表列一样。

You can use the calculated column in a PivotTable, PivotChart, Power PivotTable, Power PivotChart or Power View report just like any other table column.

Understanding Calculated Columns

用于创建计算列的 DAX 公式类似于 Excel 公式。但是，在 DAX 公式中，您不能为表中的不同行创建不同的公式。DAX 公式自动应用于整个列。

The DAX formula used to create a calculated column is like an Excel formula. However, in DAX formula, you cannot create different formulas for different rows in a table. The DAX formula is automatically applied to the entire column.

例如，您可以创建一个计算列，从现有列 - “日期”中提取年份，使用 DAX 公式 −

For example, you can create one calculated column to extract Year from the existing column – Date, with the DAX formula −

= YEAR ([Date])

YEAR 是一个 DAX 函数，Date 是表中的现有列。如您所见，表名括在括号中。您将在章节 DAX Syntax 中了解更多相关信息。

YEAR is a DAX function and Date is an existing column in the table. As seen, the table name is enclosed in brackets. You will learn more about this in the chapter – DAX Syntax.

当您使用此 DAX 公式将列添加到表中时，在创建公式后便会计算列值。将创建一列标题为 CalculatedColumn1 且填入年份值的新列。

When you add a column to a table with this DAX formula, the column values are computed as soon as you create the formula. A new column with the header CalculatedColumn1 filled with Year values will get created.

当有必要时，例如在刷新底层数据时，列值会被重新计算。您可以根据现有列、计算字段（度量）和其他计算列创建计算列。

Column values are recalculated as necessary, such as when the underlying data is refreshed. You can create calculated columns based on existing columns, calculated fields (measures), and other calculated columns.

Creating a Calculated Column

考虑一下数据模型和奥运会结果，如下面的截图所示。

Consider the Data Model with the Olympics Results as shown in the following screenshot.

creating calculated column

  1. Click the Data View.

  2. Click the Results tab.

您将查看结果表。

You will be viewing the Results table.

view result table

如上图所示，最右侧列的标题是 – 添加列。

As seen in the above screenshot, the rightmost column has the header – Add Column.

  1. Click the Design tab on the Ribbon.

  2. Click Add in the Columns group.

add column

指针将出现在公式栏中。这意味着您正在添加带有 DAX 公式的列。

The pointer will appear in the formula bar. That means you are adding a column with a DAX formula.

  1. Type =YEAR ([Date]) in the formula bar.

pointer in formula bar

正如在上述截图中看到的，带标题 – 添加列的最右侧列被高亮显示。

As can be seen in the above screenshot, the rightmost column with the header – Add Column is highlighted.

  1. Press Enter.

完成计算需要一段时间 (几秒钟)。请稍候。

It will take a while (few seconds) for the calculations to be done. Please wait.

新的计算列将插入到最右侧的添加列的左侧。

The new calculated column will get inserted to the left of the rightmost Add Column.

inserted calculated column

如上图所示，新插入的计算列被高亮显示。整个列中的值根据使用的 DAX 公式出现。列标题是 CalculatedColumn1。

As shown in the above screenshot, the newly inserted calculated column is highlighted. Values in the entire column appear as per the DAX formula used. The column header is CalculatedColumn1.

Renaming the Calculated Column

若要将计算列重命名为有意义的名称，请执行以下操作 −

To rename the calculated column to a meaningful name, do the following −

  1. Double-click on the column header. The column name will be highlighted.

renaming calculated column

  1. Select the column name.

  2. Type Year (the new name).

select column name and type year

如上图所示，计算列的名称已更改。

As seen in the above screenshot, the name of the calculated column got changed.

您还可以通过右键单击该列，然后单击下拉列表中的重命名来重命名计算列。

You can also rename a calculated column by right-clicking on the column and then clicking on Rename in the dropdown list.

只需确保新名称与表中的现有名称不冲突。

Just make sure that the new name does not conflict with an existing name in the table.

Checking the Data Type of the Calculated Column

您可以按如下方式检查计算列的数据类型 −

You can check the data type of the calculated column as follows −

  1. Click the Home tab on the Ribbon.

  2. Click the Data Type.

checking data type

正如您在以上截图中看到的，下拉列表具有列可能的数 据类型。在此示例中，选定的是默认（自动）数据类型，即整数。

As you can see in the above screenshot, the dropdown list has the possible data types for the columns. In this example, the default (Auto) data type, i.e. the Whole Number is selected.

Errors in Calculated Columns

计算列中可能会出现错误，原因如下 −

Errors can occur in the calculated columns for the following reasons −

  1. Changing or deleting relationships between the tables. This is because the formulas that use columns in those tables will become invalid.

  2. The formula contains a circular or self-referencing dependency.

Performance Issues

正如前面在奥运会结果示例中所见，Results 表有大约 35,000 行数据。因此，当您使用 DAX 公式创建列时，已立即在该列中计算了全部 35,000 多个值，这需要一些时间。数据模型和表旨在处理数百万行数据。因此，当 DAX 公式有太多引用时，它可能会影响性能。您可以通过以下操作避免性能问题：

As seen earlier in the example of Olympics results, the Results table has about 35000 rows of data. Hence, when you created a column with a DAX formula, it had calculated all the 35000+ values in the column at once, for which it took a little while. The Data Model and the tables are meant to handle millions of rows of data. Hence, it can affect the performance when the DAX formula has too many references. You can avoid the performance issues doing the following −

  1. If your DAX formula contains many complex dependencies, then create it in steps saving the results in new calculated columns, instead of creating a single big formula at once. This enables you to validate the results and assess the performance.

  2. Calculated columns need to be recalculated when data modifications occur. You can set the recalculation mode to manual, thus saving frequent recalculations. However, if any values in the calculated column are incorrect, the column will be grayed out, until you refresh and recalculate the data.

Excel DAX - Calculated Fields / Measures

数据模型中的表中的 calculated field 是通过 DAX 公式获得的字段。在早期版本的 Power Pivot 中，计算字段被称为度量。在 Excel 2013 中，它被重新命名为计算字段。但是，在 Excel 2016 中，它又被重新命名为度量。如果您参考任何文档，您会观察到这两个术语混淆在一起。请注意，计算字段和度量这两个术语是同义的。在本教程中，我们使用术语计算字段。

A calculated field in a table in a Data Model is the field obtained by a DAX formula. In earlier versions of Power Pivot, the calculated field was termed as a measure. In Excel 2013, it was renamed as a calculated field. However, it is renamed back to measure in Excel 2016. If you refer to any documentation, you can observe a mix up of these two terms. Note that the terms calculated field and measure are synonymous. In this tutorial, we use the term calculated field.

Understanding Calculated Fields

计算字段是专门为在数据透视表（或数据透视图）中使用而创建的公式。

A calculated field is a formula that is created specifically for use in a PivotTable (or PivotChart).

您可以基于标准聚合函数（如 COUNT 或 SUM）或通过定义自己的 DAX 公式来创建计算字段。

You can create a calculated field based on standard aggregation functions, such as COUNT or SUM, or by defining your own DAX formula.

以下是计算字段和计算列之间的区别 −

Following is the difference between the calculated field and the calculated column −

  1. A calculated field can be used only in the VALUES area of a PivotTable.

  2. A calculated column with the calculated results can be used in ROWS, COLUMNS and FILTERS areas also.

Saving Calculated Field

计算字段将与它的源表一起保存在数据模型中。它作为表中的一个字段显示在 Power PivotTable 或 Power PivotChart 字段列表中。

The calculated field will be saved with its source table in the Data Model. It appears in the Power PivotTable or Power PivotChart Fields list as a field in the table.

Using Calculated Field

要使用计算字段，您必须从 Power PivotTable 字段列表中选择它。计算字段将添加到 VALUES 区域，并且将评估用于计算字段的公式。为行列字段的每个组合创建一个结果。

To use a calculated field, you have to select it from the Power PivotTable Fields list. The calculated field will get added to the VALUES area and the formula used for the calculated field will be evaluated. A result is created for each combination of row and column fields.

Calculated Field – An Example

考虑以下奥运会数据的数据模型 −

Consider the following Data Model for Olympics data −

calculated field

如以上屏幕截图所示，Results 表有一个 Medal 字段，其中包含值 – Gold、Silver 或 Bronze，用于每个包含 Sport – Event - Country – Date 组合的行。假设您想要每个国家的奖牌数，那么您可以使用以下 DAX 公式创建一个计算字段 Medal Count −

As seen in the above screenshot, the Results table has a field Medal that contains the values – Gold, Silver, or Bronze for each of the rows containing Sport – Event - Country – Date combination. Suppose you want medal count for each country, then you can create a calculated field Medal Count with the following DAX formula −

Medal Count := COUNTA([Medal])

Creating a Calculated Field in a Table

若要在 Results 表中创建计算字段 Medal Count，请执行以下操作 −

To create the calculated field Medal Count in the Results table, do the following −

  1. Click the cell in the calculation area below the Medal column in the Results table. The cell will be highlighted.

  2. Type Medal Count:=COUNTA([Medal]) in the formula bar.

creating calculated field in table

按 Enter 键。

Press Enter.

press enter

如以上屏幕截图所示，计算字段显示在所选单元格中，显示值为 34,094。这个数字是 Results 表中的总行数。因此，乍一看并没有多大意义。正如前面所讨论的，只有将计算字段添加到 Power PivotTable 或 Power PivotChart 中才能看到它的实际用途。

As seen in the above screenshot, the calculated field appears in the selected cell, showing the value as 34,094. This number is the total number of rows in the Results table. Hence, it does not make much sense at the first look. As discussed earlier, the real use of a calculated field can be seen only by adding it to a Power PivotTable or a Power PivotChart.

Using the Calculated Field in a Power PivotTable

若要使用计算字段计算每个国家的奖牌数，请执行以下操作 −

To use the calculated field to count the number of medals for each country, do the following −

  1. Click the PivotTable on the Ribbon in the Power Pivot window.

  2. Click the PivotTable in the dropdown list.

using calculated field in power pivottable

将出现创建数据透视表对话框。

Create PivotTable dialog box appears.

  1. Click the Existing Worksheet.

  2. Select where you want to place the PivotTable.

将创建一个空数据透视表。

An empty PivotTable will get created.

  1. Click the Results table in the PivotTable Fields list.

  2. Click the fields – Country and Medal count.

empty pivottable created

如您所见，奖牌数被添加到值区域，国家/地区被添加到行区域。使用国家/地区字段值出现在行中的数据透视表进行创建。并且针对每一行，对奖牌数进行计算和显示。正是以此方式，计算出的字段评估所用的 DAX 公式并显示值。

As you can observe, Medal Count is added to VALUES area and Country is added to ROWS area. The PivotTable is created with the field Country values appearing in the rows. And for each row, the Medal Count value is calculated and displayed. That is the way, the calculated field evaluates the DAX formula used and displays the values.

  1. Add the field Sport from the Results table to ROWS area.

add field sport

正如您在上述屏幕截图中看到的那样，对每个国家/地区计算奖牌数，由运动分类，以及针对国家/地区本身计算一个子总数。

As you can see in the above screenshot, Medal Count is calculated for each Country - Sport-wise and a Subtotal for the Country itself.

这是 DAX 补充 Power 功能的方式。

This is how DAX supplements the Power features.

Types of Calculated Fields

计算字段有两种类型 - 隐式和显式。

There are two types of Calculated Fields – Implicit and Explicit.

  1. An implicit calculated field is created in the Power PivotTable Fields list pane.

  2. An explicit calculated field is created either in the table in the Power Pivot window, or from the PowerPivot Ribbon in the Excel window.

Creating an Implicit Calculated Field

隐式计算字段可以用两种方式创建，都位于 PowerPivot 数据透视表字段窗格中。

An implicit calculated field can be created in two ways, both in the Power PivotTable Fields pane.

Creating an Implicit Calculated Field in the PivotTable Fields List

您可以使用数据透视表字段列表中的奖牌字段创建奖牌数量字段，如下所示 -

You can create the Count of Medal Field from the Medal field in the PivotTable Fields list as follows −

  1. Deselect the field Medal Count.

  2. Right-click on the field Medal.

  3. Click Add to Values in the dropdown list.

creating implicit calculated field

奖牌数出现在值区域中。奖牌数列将添加到数据透视表。

Count of Medal appears in the Values area. Count of Medal column will be added to the PivotTable.

medal column count

Creating an Implicit Calculated Field in the VALUES Area

您可以在值区域中创建隐式计算字段 - 父行百分比，以表达一个国家/地区获得的每项运动的奖牌数占该国家/地区获得的奖牌总数的百分比。

You can create an implicit calculated field - % of Parent Row in the Values area to express the Medal count of each sport that a country has won as a percentage of the total number of Medals won by that Country.

  1. Click the down arrow in the Count of Medal box in VALUES area.

  2. Click the Value Field Settings in the dropdown list.

creating implicit calculated field in values area

将出现Value Field Settings对话框。

Value Field Settings dialog box appears.

  1. Type % Medals in the Custom Name box.

  2. Click the Show Values As tab.

  3. Click the box under Show values as.

  4. Click the % of Parent Row Total.

value field settings

  1. Click the Number Format button.

将出现“设置单元格格式”对话框。

Format Cells dialog box appears.

  1. Click Percentage.

  2. Type 0 in decimal places.

  3. Click OK.

  4. Click OK in the Value Field Settings dialog box.

  5. Select Do Not Show Subtotals.

format cells dialog box

你创建了另一个隐式计算域“% Medals”，正如你所观察到的，对于每个国家，每一项体育竞技所占的奖牌比例都会显示出来。

You created another implicit calculated field % Medals and as you can observe, for each Country, the percentage of Medals Sport-wise are displayed.

Drawbacks of an Implicit Calculated Field

隐式计算域容易创建。事实上，你一直在 Excel 数据透视表和透视图表中创建它们。但是，它们有以下缺点：

Implicit calculated fields are easy to create. In fact, you have been creating them even in Excel PivotTables and Pivot Charts. But, they have the following drawbacks −

  1. They are volatile. That means, if you deselect the field you used for calculated field, it will be removed. If you want to display it again, you have to once again create it.

  2. Their scope is limited to the PivotTable or PivotChart in which they are created. If you create another PivotTable in another worksheet, you have to create the calculated field again.

另一方面，显式计算域将与数据表一起保存，并且在你选择该数据表时可用。

On the other hand, explicit calculated fields will get saved with the table and will be available whenever you select that table.

Creating an Explicit Calculated Field

你可以用两种方式创建显式计算域：

You can create an explicit calculated field in two ways −

  1. In the calculation area in a Table in the Data Model. You have already learnt this in the section – Creating Calculated Field in a Table.

  2. From PowerPivot Ribbon in the Excel table. You will learn this way of creating an explicit calculated field in the next section.

Creating an Explicit Calculated Field from PowerPivot Ribbon

要从 PowerPivot 功能区创建显式计算域，请执行以下步骤：

To create an explicit calculated field from PowerPivot Ribbon, do the following −

  1. Click the POWERPIVOT tab on the Ribbon in your workbook.

  2. Click the Calculated Fields in the Calculations area.

  3. Click the New Calculated Field in the dropdown list.

creating explicit calculated

计算字段对话框出现。

Calculated Field dialog box appears.

  1. Fill in the required information as shown in the following screenshot.

calculated field dialog box

  1. Click the Check formula button.

  2. Click OK only if there are no errors in the formula.

如您所见，您可以在此对话框中定义计算字段的类别和格式。此外，您可以使用 IntelliSense 功能了解函数的使用，并使用自动完成功能轻松完成函数、表和列的名称。有关 IntelliSense 功能的详细信息，请参阅章节 – DAX Formulas 。

As you can observe, you can define the category and format of the calculated field in this dialog box. Further, you can use the IntelliSense feature to understand the usage of the functions and to use the AutoComplete feature to easily complete the names of the functions, tables, and columns. For details on IntelliSense feature, refer to the chapter – DAX Formulas.

这是创建显式计算字段的推荐方式。

This is a recommended way to create explicit calculated fields.

Excel DAX - Editing a Calculated Field

您可以编辑计算字段以修改它。但是，在编辑计算字段之前，您应该知道它存储在哪里。意思是在哪个表中存储了计算字段。隐式计算字段和显式计算字段都是如此。一个计算字段只能与数据模型中的一个表关联。

You can edit a calculated field to modify it. But, before you edit a calculated field, you should know where it is stored. That means, in which table the calculated field is stored. This holds both for implicit and explicit calculated fields. A calculated field can be associated with only one table in a Data Model.

Finding Calculated Fields

若要找到数据模型中的计算字段，请执行以下操作：

To find the calculated fields in the Data Model, do the following −

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

  2. Click the Show Implicit Calculated Fields.

finding calculated fields

  1. Click the Diagram View.

click diagram view

正如您在上述屏幕截图中所看到的，显示隐式计算字段在 Ribbon 上被高亮显示。如果其未被高亮显示，请再次单击它。

As you can see in the above screenshot, Show Implicit Calculated Fields is highlighted on the Ribbon. If it is not highlighted, click it again.

您还可以观察到，有四个复选框 - 列、计算字段、层次结构和 KPI。默认情况下，所有四个都被选中。

You can also observe that there are 4 checkboxes – Columns, Calculated Fields, Hierarchies, and KPIs. By default, all the 4 are selected.

  1. Uncheck the boxes - Columns, Hierarchies and KPIs.

这将只保留选中计算字段框。

This will leave only Calculated Fields box checked.

calculated fields box checked

如上图所示，只有结果表有显示的字段。其他两个表为空白。这表明只有结果表有计算字段。您还可以观察到，隐式计算字段显示一个图标，而显式计算字段 - 奖牌数没有该图标。

As seen in the above screenshot, only the Results table has fields displayed. The other two tables are blank. This shows that only the Results table has calculated fields. You can also observe that the implicit calculated fields have an icon displayed, whereas the explicit calculated field – Medal Count does not have that icon.

Viewing Calculated Fields in the Table

您可以按照如下步骤在表中查看计算字段：

You can view the calculated fields in the Table as follows −

  1. Click the calculated field.

  2. Right-click and select Go To in the dropdown list.

viewing calculated fields in table

该表将出现在数据视图中。

The table will appear in Data View.

data view

如上图所示，计算字段显示在表的计算区域中。

As seen in the above screenshot, the calculated fields appear in the calculation area of the table.

Changing a Calculated Field in the Table

您可以改变表中计算字段使用的公式。

You can change the formula used for a calculated field in the table.

  1. Click the calculated field in the table in data view of the Data Model.

  2. Select the formula in the formula bar – to the right side of :=.

该公式将高亮显示。

The formula will get highlighted.

formula highlighted

  1. Type the new formula.

  2. Press Enter.

您将在后续章节中了解有关 DAX 公式的更多信息。

You will learn more about the DAX formulas in the subsequent chapters.

Renaming a Calculated Field in the Data Model

您可以在数据视图或图表视图中的数据表中更改计算字段的名称。

You can change the name of a calculated field in the Data Table either in Data View or Diagram View.

Renaming a Calculated Field in the Data View

  1. Click the calculated field in the table in data view of the Data Model.

  2. Select the calculated field name in the formula bar – to the left side of :=.

计算字段名称将高亮显示。

The calculated field name will get highlighted.

field name highlighted

  1. Type the new name for the calculated field.

  2. Press Enter.

您将在后续章节中了解有关 DAX 语法的更多信息。

You will learn more about the DAX Syntax in the subsequent chapters.

Renaming a Calculated Field in the Diagram View

  1. Right-click the calculated field name in the table in the diagram view.

  2. Click Rename in the dropdown list.

renaming calculated field in diagram view

该名称将进入编辑模式。键入计算字段的新名称。

The name will get into editing mode. Type the new name for the calculated field.

Viewing Calculated Fields in the Excel Window

您可以按照以下步骤在 Excel 窗口中查看计算的字段：

You can view the calculated fields in the Excel window as follows −

  1. Click the POWERPIVOT tab on the Ribbon.

  2. Click Calculated Fields in the Calculations group.

  3. Click Manage Calculated Fields in the dropdown list.

viewing calculated fields in excel window

将显示“管理计算的字段”对话框。数据模型中的显式计算字段的名称将显示在该对话框中。

Manage Calculated Fields dialog box appears. The names of the explicit calculated fields in the Data Model appear in the dialog box.

manage calculated fields dialog box

Changing a Calculated Field in the Manage Calculated Fields

您可以在“管理计算的字段”对话框中更改计算的字段。

You can change a calculated field in the Manage Calculated Fields dialog box.

  1. Click the Medal Count.

  2. Click the Edit button.

changing calculated field in manage calculated fields

计算字段对话框出现。

Calculated Field dialog box appears.

  1. Select the formula to the right of = in the formula box.

calculated field dialog box appear

  1. Type the new Formula.

  2. Click OK.

  3. Click Close in the Manage Calculated Fields dialog box.

Renaming a Calculated Field in the Manage Calculated Fields

您可以在“管理计算字段”对话框中重命名计算字段。

You can rename a calculated field in the Manage Calculated Fields dialog box.

  1. Click the Medal Count.

  2. Click the Edit button.

计算字段对话框出现。

Calculated Field dialog box appears.

  1. Select the name in the calculated field name box.

renaming calculated field in manage calculated fields

  1. Type the new name for the calculated field.

  2. Click OK.

  3. Click Close in the Manage Calculated Fields dialog box.

Moving a Calculated Field in the Data Model

您可以在创建它的表的计算区域内移动计算字段。但是，它无法移动到其他表。

You can move a calculated field within the calculation area of the table in which it is created. But, it cannot be moved to another table.

  1. Right-click the calculated field.

  2. Click Cut.

  3. Move the pointer to a different place in the calculation area of the same table.

  4. Click Paste.

Note - 计算字段在表的计算区域中的位置并不重要，因为计算字段的 DAX 公式中的数据引用是按列名称进行的，并且是明确说明的。

Note − It does not really matter where the calculated field is within the calculation area of the table because the data references in the DAX formula of the calculated field are by the column names and are stated explicitly.

Excel DAX - Deleting a Calculated Field

您可以删除显式和隐式计算字段。有几种方法可以做到这一点，您将在本章中学习这些方法。

You can delete both explicit and implicit calculated fields. There are several ways of doing so, which you will learn in this chapter.

但是，在删除计算字段之前，您需要记住以下几点：

However, you need to remember the following points before deleting a calculated field −

  1. An explicit calculated field can be used in more than one PivotTable and/or PivotChart. Hence, you need to make sure that deleting the explicit calculated field does not affect any of the reports that you have already generated.

  2. An explicit calculated field can be used in the calculations of other explicit calculated fields. Hence, you need to make sure that the explicit calculated field is not used in any of the calculations of other explicit calculated fields.

  3. An implicit calculated field is limited to the PivotTable or PivotChart where it is used. Hence, before deleting an implicit calculated field, it is just sufficient to make sure that it can be deleted from the corresponding PivotTable or PivotChart.

  4. Creating an implicit calculated field is simpler than creating an explicit calculated field. Hence, more caution is required before deleting an explicit calculated field.

  5. You cannot create an explicit calculated field, if the name conflicts with the name of an implicit calculated field. Hence, you might have to delete that implicit calculated field before creating the explicit calculated field.

Deleting an Explicit Calculated Field in the Data Model

您可以在数据模型中的数据视图或图表视图中删除显式计算字段。

You can delete an explicit calculated field either in the data view or the diagram view in the Data Model.

Deleting an Explicit Calculated Field in the Data View

  1. Locate the calculated field in the calculations area in the Data View.

  2. Right-click the calculated field.

  3. Click Delete in the dropdown list.

deleting explicit calculated field in data view

将出现一条消息以进行删除确认。

Message appears for delete confirmation.

delete confirmation

单击从模型中删除。将删除显式计算字段。

Click Delete from Model. The explicit calculated field will get deleted.

Deleting an Explicit Calculated Field in the Diagram View

  1. Locate the calculated field in the data table in the Diagram View.

  2. Right-click the calculated field name.

  3. Click Delete in the dropdown list.

deleting explicit calculated field in diagram view

将出现一条消息以进行删除确认。

Message appears for delete confirmation.

message appears

单击从模型中删除。将删除显式计算字段，并且它将不会出现在数据表的字段列表中。

Click Delete from Model. The explicit calculated field will get deleted and it will not be seen in the fields list of the data table.

Deleting an Explicit Calculated Field in the Excel Window

您可以按照以下步骤从 Excel 窗口中删除显式计算字段：

You can delete an explicit calculated field from the Excel window as follows −

  1. Click the POWERPIVOT tab on the Ribbon.

  2. Click Calculated Field in the Calculations group.

  3. Click Manage Calculated Fields in the dropdown list.

deleting explicit calculated field in excel window

将出现“管理计算字段”对话框。

Manage Calculated Fields dialog box appears.

  1. Click the explicit calculated field name.

  2. Click the Delete button.

manage calculated fields dialog box appear

删除的确认消息出现。

Confirmation message for deletion appears.

manage calculated fields dialog

  1. Click Yes. Information message that the Data Model is changed appears at the top.

  2. Click the Close button in the dialog box.

change data model

明确的计算字段将被删除，并且在工作簿中的数据透视表/数据透视图字段列表中将不会看到它。

The explicit calculated field will get deleted and it will not be seen in the PivotTable/PivotChart Fields list in the workbook.

Deleting an Implicit Calculated Field

您可以在数据视图或数据模型中的图表视图中删除一个隐式计算字段。

You can delete an implicit calculated field either in the data view or the diagram view in the Data Model.

Deleting an Implicit Calculated Field in the Data View

  1. Locate the calculated field in the calculations area in the Data View.

  2. Right-click the calculated field.

  3. Click Delete in the dropdown list.

deleting implicit calculated field in data view

将出现一条消息以进行删除确认。

Message appears for delete confirmation.

implicit calculated field delete confirmation

  1. Click Delete from Model. The implicit calculated field will get deleted.

Deleting an Implicit Calculated Field in the Diagram View

  1. Locate the calculated field in the data table in the Diagram View.

  2. Right-click the calculated field name.

  3. Click Delete in the dropdown list.

deleting implicit calculated field in diagram view

将出现一条消息以进行删除确认。

Message appears for delete confirmation.

deleting implicit calculated field confirmation

单击“从模型中删除”。隐式计算字段将被删除，并且在数据表的字段列表中将不会看到它。

Click Delete from Model. The implicit calculated field will get deleted and it will not be seen in the fields list of the data table.

Excel DAX - Syntax

正如前面所讨论的，DAX 是一种由运算符、值、函数和公式组成的公式语言。在本章中，您将了解 DAX 语法。

As discussed earlier, DAX is a formula language comprising of operators, values, functions, and formulas. In this chapter, you will learn about DAX Syntax.

DAX Syntax 可以分类为 -

DAX Syntax can be categorized as −

  1. Syntax for DAX Formulas

  2. DAX Naming Requirements

  3. DAX Special Values

  4. DAX Functions

  5. DAX Operators

  6. DAX Data Types

在您继续学习 DAX 语法之前，您必须了解 Excel 公式和 DAX 公式之间的区别。

Before you proceed to learning DAX Syntax, you have to understand the difference between Excel formulas and DAX formulas.

Differences between Excel Formulas and DAX Formulas

DAX 公式与 Excel 公式类似，您可以它们输入到公式栏中。但是，两者之间存在一些重要的区别。

DAX formulas are similar to the Excel formulas and you can type them in the formula bar. However, there are some vital differences between the two.

Excel Formula

DAX Formula

Excel formulas are typed in the formula bar in the Excel window.

DAX formulas are typed in the formula bar in the Power Pivot window.

In Excel formulas, you can reference individual cells or arrays for data.

In DAX formulas, you can reference only complete tables or columns of data, i.e. references can be only to tables and fields in the tables. However, if at all you have to perform a calculation only on a part of the column data, you can do so with the DAX functions that filter and provide the required unique data values for calculation.

Excel formulas support certain data types.

DAX provides more data types than Excel does. Hence, DAX formulas can use the additional data types also.

Excel does not support any implicit data conversions.

DAX performs implicit data type conversions during calculations.

Excel DAX - Operators

DAX 是一种由函数、运算符和值组成的公式语言，可以用于公式或表达式中，以计算并返回一个或多个值。

DAX is a formula language comprising of functions, operators, and values that can be used in a formula or expression, to calculate and return one or more values.

您可以使用 DAX operators 来比较值、执行算术计算和连接字符串。在本章中，您将了解 DAX 运算符以及如何使用它们。

You can use DAX operators to compare values, perform arithmetic calculations, and concatenate strings. In this chapter, you will learn about DAX operators and how to use them.

Types of DAX Operators

DAX 支持以下类型的运算符：

DAX supports the following types of operators −

  1. DAX Arithmetic Operators

  2. DAX Comparison Operators

  3. DAX Text Concatenation Operator

  4. DAX Logical Operators

DAX Operator Precedence Order

可以使用一个包含多个 DAX 运算符的 DAX 公式，来组合多个值或表达式。在这种情况下，最终结果将取决于执行运算的顺序。DAX 提供了默认运算符优先级顺序以及替代默认优先级顺序的方法。

You can have a DAX formula with many DAX operators combining several values or expressions. In such a case, the final result will depend on the order in which the operations are performed. DAX provides you with the default operator precedence order and also ways of overriding the default precedence order.

DAX 默认运算符优先级列在以下表格中。

DAX default operator precedence is listed in the following table.

Precedence Order

Operator(s)

Operation

1

^

Exponentiation

2

–

Sign

3

* and /

Multiplication and Division

4

!

NOT

5

+ and –

Addition and Subtraction

6

&

Concatenation

7

=, <, >, ⇐, >= and <>

Equal to, Less than, Greater than, Less than or equal to, Greater than or equal to and Not equal to

DAX Expression Syntax

您需要首先了解 DAX 表达式语法以及如何使用操作数和运算符对表达式进行求值。

You need to first understand the DAX expression syntax and how the expression evaluation is done with the operands and operators.

  1. All expressions always begin with an equal sign (=). The equal sign indicates that the succeeding characters constitute an expression.

  2. To the right of the equal sign, you will have the operands connected by the DAX operators. For example, = 5 + 4 > 5. = 5 * 6 - 3.

  3. Expressions are always read from left to right, and the calculations are done in that sequence, based on the DAX operator precedence given in the previous section.

  4. If the DAX operators have equal precedence value, they are evaluated from the left to right. For example, =5*6/10. Both * and / have same the precedent order. Hence, the expression is evaluated as 30/10 = 3.

  5. If the DAX operators in the expression have different precedence values, then they are evaluated in the precedence order from the left to right. = 5 + 4 > 7. Default precedence is + first and > next. Hence, the expression is calculated from the left to right. - 5 + 4 is calculated first resulting in 9 and then 9 > 5 is evaluated that results in TRUE. = 5 * 6 - 3. Default precedence is * first and - next. Hence, the expression is calculated from the left to right. - 5 * 6 is calculated first resulting in 30 and then 30 - 3 is calculated that results in 27. = 2 * 5 - 6 * 3. Default precedence is * first, * next and then -. Hence, the expression evaluates as 10 – 18 and then as -8. Note, that it is not 10 - 6 resulting in 4 and then 4*3 that is 12.

Using Parentheses to Control DAX Calculation Order

您可以使用括号来更改 DAX 默认运算符优先级顺序，对操作数和运算符进行分组以控制计算顺序。

You can change the DAX default operator precedence order by using parentheses, grouping the operands and the operators to control the calculation sequence.

例如，= 5 * 6 - 3 在 DAX 默认运算符优先级顺序下计算结果为 27。如果您使用括号对操作数和运算符进行分组，如 = 5 * (6 - 3)，则首先计算 6 - 3，结果为 3，然后计算 5 * 3，结果为 15。

For example, = 5 * 6 - 3 evaluates to 27 with the DAX default operator precedence order. If you use parenthesis to group the operands and operators as = 5 * (6 - 3), then 6 - 3 is calculated first resulting in 3 and then 5 * 3 is calculated which results in 15.

2 * 5 - 6 * 3 evaluates to -8 with the DAX default operator precedence order. If you use parenthesis to group the operands and operators as = 2 * (5 - 6) * 3, then 5 - 6 is calculated first resulting in -1 and then 2 * (-1) * 3 is calculated which results in -6.

如您所见，对于相同的操作数和运算符，可以通过分组方式得到不同的结果。因此，当您在 DAX 公式中使用 DAX 运算符时，您应该注意计算顺序。

As you can see, with the same operands and operators, different results are possible by the way you group them. Hence, when you use the DAX operators in the DAX formulas, you should pay attention to how the computation sequence is to be.

Differences Between Excel and DAX

虽然 DAX 与 Excel 公式有相似之处，但两者之间存在一些重要的差异。

Though DAX has similarities with Excel formulas, there are certain significant differences between the two.

  1. DAX is more powerful than Excel because of its underlying memory resident computation engine.

  2. DAX supports more data types than Excel.

  3. DAX provides additional advanced features of a relational database, Data Model, including richer support for date and time types.

在某些情况下，DAX 中的计算结果或函数行为可能与 Excel 中的不相同。这是由于以下差异造成的：

In some cases, the results of calculations or the behavior of functions in DAX may not be the same as in Excel. This is due to the differences in the following −

  1. Data type casting

  2. Data types

Difference in Data Type Casting

在 DAX 中，当您有表达式 =value1 operator value2 时，两个操作数 value1 和 value2 应为相同的数据类型。如果数据类型不同，DAX 会首先将它们隐式地转换为公共数据类型。有关详细信息，请参阅章节 - DAX 语法。

In DAX, when you have an expression =value1 operator value2, the two operands value1 and value2 should be of the same data type. If the data types are different, DAX will convert them first to a common data type implicitly. Refer to the chapter – DAX Syntax for details.

例如，您必须比较不同数据类型两个操作数，比如说某个公式产生的数字，如 =[Amount] * 0.08 和一个整数。第一个数字可以是有许多小数位的小数，而第二个数字是一个整数。然后，DAX 会这样处理：

For example, you have to compare two operands of different data types, say a number resulting from a formula, such as =[Amount] * 0.08 and an integer. The first number can be a decimal number with many decimal places, whereas the second number is an integer. Then DAX handles it as follows −

  1. First, DAX will convert both the operands to real numbers using the largest numeric format that can store both kinds of numbers.

  2. Next, DAX will compare the two real numbers.

相反，Excel 会尝试比较不同数据类型的值，而不用首先将这些值强制转换为公共数据类型。由于此原因，您可能会发现在 DAX 和 Excel 中，同一个比较表达式的结果不同。

In contrast, Excel tries to compare values of different data types without first coercing them to a common data type. For this reason, you might find different results in DAX and in Excel for the same comparison expression.

Difference in Data Types

DAX 和 Excel 中的操作符优先级顺序相同。但是，DAX 不支持 Excel 支持的百分号 (%) 操作符和数据范围。此外，DAX 支持表作为数据类型，而这在 Excel 中不行。

The operator precedence order in DAX and Excel is the same. However, the operator percent (%) and data ranges that Excel supports are not supported by DAX. Moreover, DAX supports table as a data type, which is not the case in Excel.

此外，在 Excel 公式中，您可以引用一个单元格，或一个数组或一系列单元格。在 DAX 公式中，您不能引用任何这些。DAX 公式引用到数据时应采用表、列、计算字段和计算列的方式。

Further, in Excel formulas, you can refer to a single cell, or an array or a range of cells. In DAX formulas, you cannot refer to any of these. The DAX formula references to data should be by tables, columns, calculated fields, and calculated columns.

如果您从 Excel 复制公式并将其粘贴在 DAX 中，确保 DAX 公式的正确性，因为 DAX 语法不同于 Excel 公式语法。此外，即使一个函数在 DAX 和 Excel 中具有相同名称，其参数也可能不同，函数的结果也可能不同。

If you copy formulas from Excel and paste them in DAX, ensure the correctness of the DAX formula as DAX syntax is different from Excel formula syntax. Also, even if a function has the same name in DAX and Excel, its parameters might be different and the result of the function can also be different.

您将在后面的章节中了解有关所有这些内容的更多信息。

You will learn more about all these in the subsequent chapters.

Excel DAX - Standard Parameters

DAX 有 standard parameter 名称，以促进使用和理解 DAX 函数。此外，您可以对参数名称使用某些前缀。如果前缀足够明确，您可以将前缀本身用作参数名称。

DAX has standard parameter names to facilitate the usage and understanding of the DAX functions. Further, you can use certain prefixes to the parameter names. If the prefix is clear enough, you can use the prefix itself as the parameter name.

Standard Parameter Names

以下为 DAX 的标准参数名称：

Following are the DAX standard parameter names −

Sr.No.

Parameter Name & Description

1

expression Any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context).

2

value Any DAX expression that returns a single scalar value where the expression is to be evaluated exactly once before all other operations.

3

table Any DAX expression that returns a table of data.

4

tableName The name of an existing table using standard DAX syntax. It cannot be an expression.

5

columnName The name of an existing column using standard DAX syntax, usually fully qualified. It cannot be an expression.

6

name A string constant that will be used to provide the name of a new object.

7

order An enumeration used to determine the sort order.

8

ties An enumeration used to determine the handling of tie values.

9

type An enumeration used to determine the data type for PathItem and PathItemReverse.

Prefixing Parameter Names

您可以用前缀来限定参数名称：

You can qualify a parameter name with a prefix −

  1. The prefix should be descriptive of how the argument is used.

  2. The prefix should be in such a way that ambiguous reading of the parameter is avoided.

例如，

For example,

  1. Result_ColumnName − Refers to an existing column used to get the result values in the DAX LOOKUPVALUE () function.

  2. Search_ColumnName − Refers to an existing column used to search for a value in the DAX LOOKUPVALUE () function.

Using Only the Prefix as a Parameter

如果您前缀足够明确，以至于可以描述参数，则可以省略参数名称，仅使用前缀。省略参数名称，仅使用前缀有时有助于避免在阅读时混乱。

You can omit the parameter name and use only the prefix, if the prefix is clear enough to describe the parameter. Omitting the parameter name and using only the prefix can sometimes help in avoiding the clutter in reading.

例如，考虑 DATE (Year_value, Month_value, Day_value)。您可以省略参数名称 - value，它重复了三次，并将其写成 DATE (Year, Month, Day)。如您所见，仅使用前缀，函数更具可读性。

For example, Consider DATE (Year_value, Month_value, Day_value). You can omit the parameter name – value, that is repeated thrice and write it as DATE (Year, Month, Day). As you can observe, by using only the prefixes, the function is more readable.

但是，有时必须存在参数名称和前缀才能使之清晰。

However, sometimes the parameter name and the prefix have to be present for clarity.

例如，考虑 Year_columnName。参数名称是 ColumnName，前缀是 Year。两者都是必需的，以使用户了解参数需要对年份的现有列进行引用。

For example, Consider Year_columnName. The parameter name is ColumnName and the prefix is Year. Both are required to make the user understand that the parameter requires a reference to the existing column of years.

Excel DAX - Functions

大多数 DAX functions 具有与 Excel 函数相同名称和功能。但是，已修改 DAX 函数，以使用 DAX 数据类型，并使用表和列。

Most of the DAX functions have the same names and functionality as that of Excel functions. However, DAX functions have been modified to use DAX data types and to work with tables and columns.

DAX 有一些附加函数，这些函数在 Excel 中找不到。这些 DAX 函数用于特定目的，例如基于与数据模型的关系数据库方面相关的关系查找，对表进行迭代以执行递归计算，执行动态聚合，以及利用时间智能执行计算。

DAX has some additional functions that you will not find in Excel. These DAX functions are provided for specific purposes such as lookups based on relationships associated with the relational database aspects of the Data Model, the ability to iterate over a table to perform recursive calculations, to perform dynamic aggregation, and for calculations utilizing time intelligence.

在本章中，您将了解 DAX 语言中支持的函数。有关这些 DAX 函数的用法，请参阅本教程库中的教程 - DAX 函数。

In this chapter, you will learn about the functions supported in the DAX language. For more information on the usage of these DAX functions, refer to the tutorial – DAX Functions in this tutorials library.

What is a DAX Function?

DAX 函数是 DAX 语言中提供的内置函数，使您能够对数据模型中的表中的数据执行各种操作。如前所述，DAX 用于数据分析和商业智能目的，这些目的需要支持从数据中提取、吸收和得出见解。基于数据模型的 DAX 函数为您提供了这些实用工具，一旦您掌握了 DAX 语言和使用 DAX 函数，它将使您的工作更简单。

A DAX function is an in-built function provided in the DAX language to enable you to perform various actions on the data in the tables in your Data Model. As discussed earlier, DAX is used for data analysis and business intelligence purposes that require support to extract, assimilate, and derive insights from the data. The DAX functions that are based on the Data Model provide you with these utilities that make your job simpler, once you get a grasp on the DAX language and the usage of the DAX functions.

Excel Functions vs. DAX Functions

您所熟悉的 Excel 函数与 DAX 函数之间存在某些相似之处。但是，也存在某些差异。您需要了解这些差异，以便在使用 DAX 函数和编写包括 DAX 函数的 DAX 公式时避免出错。

There are certain similarities between Excel functions that you are aware of and the DAX functions. However, there are certain differences too. You need to get a clarity on these, so that you can avoid making mistakes in the usage of DAX functions and in writing DAX formulas that include DAX functions.

Similarities between Excel Functions and DAX Functions

  1. Many DAX functions have the same name and the same general behavior as Excel functions.

  2. DAX has lookup functions that are similar to the array and vector lookup functions in Excel.

Differences between Excel Functions and DAX Functions

  1. DAX functions have been modified to take different types of inputs and some of the DAX functions might return a different data type. Hence, you need to understand the usage of these functions separately though they have the same name. In this tutorial, you will find every DAX function prefixed with DAX so as to avoid confusion with the Excel functions.

  2. You cannot use DAX functions in an Excel formula or use Excel formulas/functions in DAX, without the required modifications.

  3. Excel functions take a cell reference or a range of cells as reference. DAX functions never take a cell reference or a range of cells as reference, but instead take a column or table as reference.

  4. Excel date and time functions return an integer that represents a date as a serial number. DAX date and time functions return a datetime data type that is in DAX but not in Excel.

  5. Excel has no functions that return a table, but some functions can work with arrays. Many of the DAX functions can easily reference complete tables and columns to perform calculations and return a table or a column of values. This ability of DAX adds power to the Power Pivot, Power View and Power BI, where DAX is used.

  6. DAX lookup functions require that a relationship is established between the tables.

  7. Excel supports variant data type in a column of data, i.e. you can have data of different data types in a column. Whereas, DAX expects the data in a column of a table to be always of the same data type. If the data is not of the same data type, DAX changes the entire column to the data type that best accommodates all the values in the column. However, if the data is imported and this issue arises, DAX can flag an error.

要了解 DAX 数据类型和数据类型转换，请参阅该章节 – DAX 语法参考。

To learn about DAX data types and data type casting, refer to the chapter – DAX Syntax Reference.

Types of DAX Functions

DAX 支持以下类型的函数。

DAX supports the following types of functions.

  1. DAX Table Valued Functions DAX Filter FunctionsDAX Aggregation FunctionsDAX Time Intelligence Functions

  2. DAX Date and Time Functions

  3. DAX Information Functions

  4. DAX Logical Functions

  5. DAX Math and Trig Functions

  6. DAX Other Functions

  7. DAX Parent and Child Functions

  8. DAX Statistical Functions

  9. DAX Text Functions

在本节中，你将了解函数类别级别的 DAX 函数。有关 DAX 函数语法以及 DAX 函数返回和执行内容的详细信息，请参阅本教程库中的 DAX 函数教程。

In this section, you will learn about DAX functions at the functions category level. For details on the DAX Function Syntax and what the DAX function returns and does - refer to the DAX Functions tutorial in this tutorials library.

DAX 时间智能函数和 DAX 筛选器函数功能强大，需要专门提及。有关详细信息，请参阅章节 - 了解 DAX 时间智能和 DAX 筛选器函数。

DAX time intelligence functions and DAX filter functions are powerful and require a special mention. Refer to the chapters - Understanding DAX Time Intelligence and DAX Filter Functions for details.

DAX Table Valued Functions

许多 DAX 函数将表作为输入或输出表或两者兼而有之。这些 DAX 函数称为 DAX 表值函数。由于表可以具有单列，因此 DAX 表值函数也采用单列作为输入。你具有以下类型的 DAX 表值函数 -

Many DAX functions take tables as input or output tables or do both. These DAX functions are called DAX table valued functions. Because a table can have a single column, DAX table valued functions also take single columns as inputs. You have the following types of DAX table valued functions −

  1. DAX Aggregation functions

  2. DAX Filter functions

  3. DAX Time intelligence functions

了解 DAX 表值函数可帮助你有效地编写 DAX 公式。

Understanding DAX table valued functions helps you in writing DAX formulas effectively.

DAX Aggregation Functions

DAX 聚合函数汇总表各行中的任何表达式，并且在计算中很有用。

DAX Aggregation functions aggregate any expression over the rows of a table and are useful in calculations.

以下是一些 DAX 聚合函数 -

Following are some DAX Aggregation functions −

DAX Filter Functions

DAX 筛选器函数返回与当前行相关的列、表或值。你可以使用 DAX 筛选器函数返回特定数据类型，查找相关表中的值，以及按相关值筛选。DAX 查找函数通过使用表及其之间的关系来工作。DAX 筛选器函数使你能够操作数据上下文以创建动态计算。

DAX Filter functions return a column, a table, or values related to the current row. You can use DAX Filter functions to return specific data types, look up values in related tables, and filter by related values. DAX Lookup functions work by using tables and relationships between them. DAX Filter functions enable you to manipulate the data context to create dynamic calculations.

以下是某些 DAX 筛选器函数 -

Following are some DAX Filter functions −

DAX Time Intelligence Functions

DAX 时间智能函数返回日期表或使用日期表来计算聚合。这些 DAX 函数可帮助你创建支持商业智能分析需求的计算，使你能够使用时间段（包括天、月、季度和年）来操作数据。

DAX Time Intelligence functions return a table of dates or use a table of dates to calculate an aggregation. These DAX functions help you create calculations that support the needs of Business Intelligence analysis by enabling you to manipulate data using time periods, including days, months, quarters and years.

以下是某些 DAX 时间智能函数 -

Following are some DAX Time Intelligence functions −

DAX Date and Time Functions

DAX 日期和时间函数与 Excel 日期和时间函数类似。但是，DAX 日期和时间函数基于 DAX 的 datetime 数据类型。

DAX Date and Time functions are similar to the Excel date and time functions. However, DAX Date and Time functions are based on the datetime data type of DAX.

以下是 DAX 日期和时间函数 -

Following are DAX Date and Time functions −

DAX Information Functions

DAX 信息函数查看以参数形式提供的单元格或行，并告知您该值是否与预期类型匹配。

DAX Information functions look at the cell or row that is provided as an argument and tell you whether the value matches the expected type.

以下是某些 DAX 信息函数 -

Following are some DAX Information functions −

DAX Logical Functions

DAX 逻辑函数返回表达式中值的信息。例如，DAX TRUE 函数让您知道正在计算的表达式是否返回 TRUE 值。

DAX Logical Functions return information about values in an expression. For example, DAX TRUE function lets you know whether an expression that you are evaluating returns a TRUE value.

以下一些 DAX 逻辑函数 −

Following are DAX Logical functions −

DAX Math and Trig Functions

DAX 数学和三角函数与 Excel 的数学和三角函数非常相似。

DAX Mathematical and Trigonometric functions are very similar to the Excel mathematical and trigonometric functions.

以下是一些 DAX 数学和三角函数 −

Following are some DAX Math and Trig functions −

DAX Other Functions

这些 DAX 函数执行的独特操作无法由其他大多数函数所属的任何类别定义。

These DAX functions perform unique actions that cannot be defined by any of the categories most other functions belong to.

以下是一些 DAX 其他函数 −

Following are some DAX Other functions −

DAX Parent and Child Functions

DAX 父子函数用于管理以数据模型中父子层次结构呈现的数据。

DAX Parent and Child functions are useful in managing data that is presented as a parent/child hierarchy in the Data Model.

以下是一些 DAX 父母函数 −

Following are some DAX Parent and Child functions −

DAX Statistical Functions

DAX 统计函数与 Excel 统计函数非常相似。

DAX Statistical functions are very similar to the Excel Statistical functions.

以下是一些 DAX 统计函数 −

Following are some DAX Statistical functions −

DAX Text Functions

DAX 文本函数适用于表和列。利用 DAX 文本函数，您可以返回字符串の一部、在字符串中查找文本或者串联字符串值。还可以控制日期、时间和数字的格式。

DAX Text functions work with tables and columns. With DAX Text functions you can return the part of a string, search for text within a string or concatenate string values. You can also control the formats for dates, times, and numbers.

以下是一些 DAX 文本函数 −

Following are some DAX Text functions −

Excel DAX - Understanding DAX Functions

在 Excel 2013 中，DAX 具有 246 个函数。您已经在第 – DAX 函数的章节中了解了不同类型的 DAX 函数。但是，如果要在 DAX 公式中使用 DAX 函数，您需要详细理解该函数。您应该知道函数的语法、参数类型、函数的返回值等。

In Excel 2013, DAX has 246 functions. You have already learnt about the different types of DAX functions in the chapter – DAX Functions. However, if you have to use a DAX function in a DAX formula, you need to understand the function in detail. You should know the syntax of the function, the parameter types, what the function returns, etc.

如果您写 DAX 公式，建议在适用情况下使用 DAX 函数。为此，您可以参考本教程库中的教程 – DAX 函数，以深入理解 246 个 DAX 函数各自的使用位置和方式。您始终可以在本教程与 DAX 函数教程之间来回学习，以掌握 DAX。

If you are writing a DAX formula, it is suggested to use the DAX functions where applicable. For this, you can refer to the tutorial – DAX Functions in this tutorials library for an indepth understanding of where and how to use each of the 246 DAX functions. You can always go back and forth between this tutorial and DAX Functions tutorial to obtain mastery on DAX.

在本教程中，您会了解如何在 DAX 函数教程中阅读和解读 DAX 函数。

In this chapter, you will understand how to read and interpret the DAX functions in the DAX Functions tutorial.

DAX Function – Explanation Structure

在 DAX 函数教程中，每个 DAX 函数都使用标准结构来解析，包括以下部分 −

In the DAX functions tutorial, each DAX function is explained in a standard structure, comprising of the following sections −

  1. Description

  2. Syntax

  3. Parameters

  4. Return Value

  5. Remarks

  6. Example

您将在以下教程中学习每一个部分。

You will learn about each of these in the following sections.

Description

在说明部分，您将学习 DAX 函数是什么以及它可以在哪里使用。

In the Description section, you will learn what the DAX function is about and where it can be used.

Syntax

在语法部分，你将了解确切的功能名称和相关参数。

In the Syntax section, you will learn the exact function name and the respective parameters.

Parameters

在参数部分，您将了解每个 DAX 函数的各个参数，是否为输入或输出参数，是否有任何选项。正如在第 – DAX 标准参数一章中所见，将只使用标准参数名称。

In the Parameters section, you will learn about each of the parameters of the specific DAX function, whether a parameter is an input or an output and if there are any options. As seen in the chapter - DAX Standard Parameters, only the standard parameter names will be used.

Return Value

在“返回值”部分，您将了解 DAX 函数将返回什么值及其数据类型。

In the Return Value section, you will learn about what value the DAX function will return and its data type.

Remarks

在备注部分，您将了解需要了解的关于 DAX 函数使用情况的任何其他信息。

In the Remarks section, you will learn about any extra information that you need to know about the usage of the DAX function.

Example

DAX 函数描述将以函数使用方法的示例结束。

The DAX function description will be ended with an example of the usage of the function.

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.

Excel DAX - Formulas

DAX 是用于在 Power PivotTables 中创建自定义计算的公式语言。您可以使用专用于处理关系数据并在 DAX 公式中执行动态聚合的 DAX 函数。

DAX is a formula language for creating custom calculations in Power PivotTables. You can use the DAX functions that are designed to work with relational data and perform dynamic aggregation in DAX formulas.

DAX formulas 与 Excel 公式很相似。要创建 DAX 公式，您需要键入一个等号，之后是函数名称或表达式以及任何必需的值或参量。

DAX formulas are very similar to Excel formulas. To create a DAX formula, you type an equal sign, followed by a function name or expression and any required values or arguments.

DAX Functions vs. DAX Formulas

DAX 公式可以包括 DAX 函数并利用它们。这就是 DAX 公式在重要方式上与 DAX 函数有所不同的原因。

DAX formulas can include DAX functions and leverage their usage. This is where DAX formulas tend to differ from DAX functions in important ways.

  1. A DAX function always reference a complete column or a table. If you want to use only particular values from a table or column, you can add filters to the formula.

  2. If you want to customize calculations on a row by row basis, Power Pivot provides functions that let you use the current row value or a related value to perform calculations that vary by context.

  3. DAX includes a type of function that returns a table as its result, rather than a single value. These functions can be used to provide input to other functions, thus calculating values for entire tables or columns.

  4. Some DAX functions provide time intelligence, which lets you create calculations using meaningful ranges of dates and compare the results across parallel periods.

Understanding DAX Formula Syntax

每个 DAX 公式都有以下语法 −

Every DAX formula has the following syntax −

  1. Each formula must begin with an equal sign.

  2. To the right of the equal sign, you can either type or select a function name, or type an expression. The expression can contain table names and column names connected by DAX operators.

以下是部分有效的 DAX 公式 −

Following are some valid DAX formulas −

  1. [column_Cost] + [column_Tax]

  2. = Today ()

Understanding IntelliSense Feature

DAX 提供了 IntelliSense 功能，该功能可帮助您迅速准确地编写 DAX 公式。依托于此功能，您不必完整键入表、列和函数名称，而是在编写 DAX 公式时从下拉列表中选择相关的名称即可。

DAX provides the IntelliSense feature that will enable you to write DAX formulas promptly and correctly. With this feature, you need not type the table, column, and function names completely, but select the relevant names from the dropdown list while writing a DAX formula.

  1. Begin to type the first few letters of the function name. AutoComplete displays a list of available functions with the names beginning with those letters.

  2. Place the pointer on any of the function names. IntelliSense tooltip will be displayed giving you the use of the function.

  3. Click the function name. The function name appears in the formula bar and the syntax is displayed, which will guide you as you select the arguments.

  4. Type the first letter of the table name that you want. AutoComplete displays a list of available tables and columns with the names beginning with that letter.

  5. Press TAB or click the name to add an item from the AutoComplete list to the formula.

  6. Click the Fx button to display a list of available functions. To select a function from the dropdown list, use the arrow keys to highlight the item and click OK to add the function to the formula.

  7. Supply the arguments to the function by selecting them from a dropdown list of possible tables and columns or by typing in required values.

强烈建议使用这个好用的 IntelliSense 功能。

Usage of this handy IntelliSense feature is highly recommended.

Where to Use DAX Formulas?

您可以在创建计算列和计算字段时使用 DAX 公式。

You can use DAX formulas in creating calculated columns and calculated fields.

  1. You can use DAX formulas in calculated columns, by adding a column and then typing an expression in the formula bar. You create these formulas in the PowerPivot window.

  2. You can use DAX formulas in calculated fields. You create these formulas − In the Excel window in the Calculated Field dialog box, or In the Power Pivot window in the calculation area of a table.

相同公式在计算列或计算字段中使用的行为方式可能会不同。

The same formula can behave differently depending on whether the formula is used in a calculated column or a calculated field.

  1. In a calculated column, the formula is always applied to every row in the column, throughout the table. Depending on the row context, the value might change.

  2. In a calculated field, however, the calculation of results is strongly dependent on the context. That is, the design of the PivotTable and the choice of row and column headings affects the values that are used in calculations.

理解 DAX 中上下文的概念对于编写 DAX 公式非常重要。在您的 DAX 旅程之初，这可能有些困难，但一旦您掌握了它，您就能编写出适用于复杂和动态数据分析的有效 DAX 公式。有关详细信息，请参阅章节 – DAX 上下文。

It is important to understand the concept of context in DAX to write DAX formulas. This can be a bit difficult in the beginning of your DAX journey, but once you get a grasp on it, you can write effective DAX formulas that are required for complex and dynamic data analysis. For details, refer to the chapter – DAX Context.

Creating a DAX Formula

您已经在上一节中学习了 IntelliSense 功能。请记住在创建任何 DAX 公式时使用它。

You have already learnt about the IntelliSense feature in a previous section. Remember to use it while creating any DAX formula.

使用以下步骤来创建 DAX 公式 -

To create a DAX formula, use the following steps −

  1. Type an equal sign.

  2. To the right of the equal sign, type the following − Type the first letter of a function or table name and select the complete name from the dropdown list. If you have chosen a function name, type parenthesis ‘(‘. If you have chosen the table name, type bracket ‘[‘. Type the first letter of the column name and select the complete name from the dropdown list. Close the column names with ‘]’ and function names with ‘)’. Type a DAX operator between expressions or type ‘,’ to separate function arguments. Repeat steps 1 - 5 till the DAX formula is complete.

例如，您希望找到东部地区的总销售额。您可以编写如下所示的 DAX 公式。East_Sales 是表的名称。Amount 是表中的列。

For example, you want to find the total sales amount in the East region. You can write a DAX formula as shown below. East_Sales is the name of the table. Amount is a column in the table.

SUM ([East_Sales[Amount])

正如在章节 – DAX 语法中已经讨论过的，建议在每次引用任何列名时使用表名连同列名。这被称为 - “完全限定的名称”。

As already discussed in the chapter – DAX Syntax, it is a recommended practice to use the table name along with the column name in every reference to any column name. This is termed as – “the fully qualified name”.

DAX 公式会根据它是用于计算字段还是计算列而有所不同。有关详细信息，请参阅以下章节。

The DAX formula can vary based on whether it is for a calculated field or calculated column. Refer to the sections below for details.

Creating a DAX Formula for a Calculated Column

您可以在 Power Pivot 窗口中为计算列创建 DAX 公式。

You can create a DAX formula for a calculated column in the Power Pivot window.

  1. Click the tab of the table in which you want to add the calculated column.

  2. Click the Design tab on the Ribbon.

  3. Click Add.

  4. Type the DAX formula for the calculated column in the formula bar.

= DIVIDE (East_Sales[Amount], East_Sales[Units])

此 DAX 公式对 East_Sales 表中的每一行执行以下操作 -

This DAX formula does the following for every row in the table East_Sales −

  1. Divides the value in Amount column of a row by the value in Units column in the same row.

  2. Places the result in the new added column in the same row.

  3. Repeats steps 1 and 2 iteratively till it completes all the rows in the table.

您已使用上述公式添加了这些单位所售单价的列。

You have added a column for Unit Price at which those units are sold with the above formula.

  1. As you can observe, calculated columns require computation and storage space as well. Hence, use calculated columns only if necessary. Use calculated fields where possible and sufficient.

有关详细信息，请参阅章节 - 计算列。

Refer to the chapter - Calculated Columns for details.

Creating a DAX Formula for a Calculated Field

可以在 Excel 窗口或 Power Pivot 窗口中创建已计算字段的 DAX 公式。对于已计算字段，需要预先提供名称。

You can create a DAX formula for a calculated field either in the Excel window or in the Power Pivot window. In the case of calculated field, you need to provide the name beforehand.

  1. To create a DAX formula for a calculated field in the Excel window, use the Calculated Field dialog box.

  2. To create a DAX formula for a calculated field in the Power Pivot window, click a cell in the calculation area in the relevant table. Start the DAX formula with CalculatedFieldName:=.

例如，Total East Sales Amount:=SUM ([East_Sales[Amount])

For example, Total East Sales Amount:=SUM ([East_Sales[Amount])

如果在 Excel 窗口中使用计算字段对话框，则可以在保存公式之前检查公式，并养成强制性习惯以确保使用正确的公式。

If you use Calculated Field dialog box in the Excel window, you can check the formula before you save it and make it as a mandatory habit to ensure the use of correct formulas.

有关这些选项的更多详细信息，请参阅章节 - 计算字段。

For more details on these options, refer to the chapter – Calculated Fields.

Creating DAX Formulas Using the Formula Bar

Power Pivot 窗口还具有类似于 Excel 窗口公式栏的公式栏。公式栏使用自动完成功能使得使用最小语法错误更容易创建和编辑公式。

Power Pivot window also has a formula bar that is like Excel window formula bar. Formula bar makes it easier to create and edit formulas, using the AutoComplete functionality so as to minimize syntax errors.

  1. To enter the name of a table, begin typing the name of the table. Formula AutoComplete provides a dropdown list containing valid table names that begin with those letters. You can start with one letter and type more letters to narrow down the list if required.

  2. To enter the name of a column, you can select it from the list of column names in the selected table. Type a bracket ‘[‘, to the right of the table name, and then choose the column from the list of columns in the selected table.

Tips for Using AutoComplete

以下是使用自动完成的其中一些技巧 -

Following are some tips for using AutoComplete −

  1. You can nest functions and formulas in a DAX formula. In such a case, you can use Formula AutoComplete in the middle of an existing formula with nested functions. The text immediately before the insertion point is used to display values in the dropdown list and all of the text after the insertion point remains unchanged.

  2. Defined names that you create for constants do not get displayed in the AutoComplete dropdown list, but you can still type them.

  3. The closing parenthesis of functions is not automatically added. You need to do it by yourself.

  4. You must make sure that each function is syntactically correct.

Understanding Insert Function Feature

您可以在 Power Pivot 窗口和 Excel 窗口中找到标记为 fx 的插入函数按钮。

You can find the Insert Function button labelled as fx, both in the Power Pivot window and Excel window.

  1. The Insert Function button in the Power Pivot window is to the left of formula bar.

  2. The Insert Function button in the Excel window is in the Calculated Field dialog box to the right of Formula.

单击 fx 按钮时，插入函数对话框会出现。插入函数对话框是找到与您的 DAX 公式相关的 DAX 函数最简单的方法。

When you click on the fx button, Insert Function dialog box appears. The Insert Function dialog box is the easiest way to find a DAX function that is relevant to your DAX formula.

插入函数对话框可以按类别帮助您选择函数，并为每个函数提供一个简短的描述。

The Insert Function dialog box helps you select functions by category and provides short descriptions for each function.

understanding insert function

Using Insert Function in a DAX Formula

假设您希望创建以下计算域：

Suppose you want to create the following calculated field −

Medal Count: = COUNTA (]Medal])

您可以按照以下步骤使用插入函数对话框：

You can use Insert Function dialog box using the following steps −

  1. Click the calculation area of the Results table.

  2. Type the following in the formula bar −

Medal Count: =

  1. Click the Insert Function button (fx).

“插入函数”对话框随即出现。

Insert Function dialog box appears.

  1. Select Statistical in the Select a category box as shown in the following screenshot.

  2. Select COUNTA in the Select a function box as shown in the following screenshot.

using insert function

正如您观察到的，会显示所选 DAX 函数语法和函数描述。这能确保它正是您想要插入的函数。

As you can observe, the selected DAX function syntax and the function description are displayed. This enables you to make sure that it is the function that you want to insert.

  1. Click OK. Medal Count:=COUNTA( appears in the formula bar and a tooltip displaying the function syntax also appears.

  2. Type [. This means you are about to type a column name. The names of all the columns and the calculated fields in the current table will be displayed in the dropdown list. You can use IntelliSense to complete the formula.

  3. Type M. The displayed names in the dropdown list will be limited to those starting with ‘M’.

  4. Click Medal.

click medal

  1. Double-click Medal. Medal Count: = COUNTA([Medal] will be displayed in the formula bar. Close the parenthesis.

  2. Press Enter. You are done. You can use the same procedure to create a calculated column also. You can also follow the same steps to insert a function in the Calculated Field dialog box in the Excel window using the Insert Function feature.

  3. Click the Insert Function (fx) button to the right of Formula.

“插入函数”对话框随即出现。其余的步骤与以上步骤相同。

Insert Function dialog box appears. The rest of the steps are the same as above.

Using Multiple Functions in a DAX Formula

DAX 公式最多可以包含 64 个嵌套函数。但是，DAX 公式不太可能包含这么多嵌套函数。

DAX formulas can contain up to 64 nested functions. But, it is unlikely that a DAX formula contains so many nested functions.

如果 DAX 公式包含多个嵌套函数，其具有以下缺点：

If a DAX formula has many nested functions, it has the following disadvantages −

  1. The formula would be very difficult to create.

  2. If the formula has errors, it would be very difficult to debug.

  3. The formula evaluation would not be very fast.

在此类情况下，你可以将公式分割为更小的、更易于管理的公式，并逐渐建立大的公式。

In such cases, you can split the formula into smaller manageable formulas and build the large formula incrementally.

Creating a DAX Formula Using Standard Aggregations

当你执行数据分析时，你将对汇总数据进行计算。有几种 DAX 聚合函数，例如，你在 DAX 公式中可以使用 SUM、COUNT、MIN、MAX、DISTINCTCOUNT 等函数。

When you perform data analysis, you will perform calculations on aggregated data. There are several DAX aggregation functions, such as SUM, COUNT, MIN, MAX, DISTINCTCOUNT, etc. that you can use in DAX formulas.

你可以使用 Power Pivot 窗口中的自动求和功能，通过使用标准聚合自动创建公式。

You can automatically create formulas using standard aggregations by using the AutoSum feature in the Power Pivot window.

  1. Click the Results tab in the Power Pivot window. Results table will be displayed.

  2. Click the Medal column. The entire column – Medal will be selected.

  3. Click the Home tab on the Ribbon.

  4. Click the down arrow next to AutoSum in the Calculations group.

creating dax formula using standard aggregations

  1. Click COUNT in the dropdown list.

click count

正如你可以观察到的，在列（奖牌）下面的计算区域中，显示了计算字段奖牌数。DAX 公式还出现在公式栏中：

As you can observe, the calculated field Count of Medal appears in the calculation area below the column – Medal. The DAX formula also appears in the formula bar −

Count of Medal: = COUNTA([Medal])

自动求和功能已为你完成了工作，为数据聚合创建了计算字段。此外，自动求和已采用 DAX 函数 COUNT 的适当变体，即 COUNTA（DAX 具有 COUNT、COUNTA、COUNTAX 函数）。

The AutoSum feature has done the work for you – created the calculated field for data aggregation. Further, AutoSum has taken the appropriate variant of the DAX function COUNT, i.e. COUNTA (DAX has COUNT, COUNTA, COUNTAX functions).

谨记一点，要在使用自动求和功能，你需要在功能区上单击自动求和旁边的向下箭头。如果你单击自动求和本身，你会得到：

A word of caution – To use AutoSum feature, you need to click the down arrow next to AutoSum on the Ribbon. If you click on the AutoSum itself instead, you will get −

Sum of Medal: = SUM([Medal])

并且，因为奖牌不是数值数据列，列中的文本无法转换为数字，所以会标记为错误。

And an error is flagged as Medal is not a numeric data column and the text in the column cannot be converted to numbers.

error

你可以参阅章节 DAX Error Reference ，了解有关 DAX 错误的详细信息。

You can refer to the chapter - DAX Error Reference for details on DAX errors.

DAX Formulas and the Relational Model

正如你所知，在 Power Pivot 数据模型中，你可以使用多张数据表，并通过定义关系连接这些表。这将使你能够创建有趣的 DAX 公式，该公式使用相关表中列之间的关联来进行计算。

As you are aware, in the Data Model of Power Pivot, you can work with multiple tables of data and connect the tables by defining relationships. This will enable you to create interesting DAX formulas that use the correlations of the columns among the related tables for calculations.

当你创建两个表之间的关系时，你预期要确保用作键的两个列具有匹配的值，如果不是全部的话，至少也应针对大多数行匹配。在 Power Pivot 数据模型中，可以在键列中拥有不匹配的值，仍然可以创建关系，因为 Power Pivot 不会强制参考完整性（查看下一部分了解详细信息）。但是，键列中存在空白值或不匹配值可能会影响 DAX 公式的结果和数据透视表的显示。

When you create a relationship between two tables, you are expected to make sure that the two columns used as keys have values that match, at least for most of the rows, if not completely. In the Power Pivot Data Model, it is possible to have non-matching values in a key column and still create a relationship, because Power Pivot does not enforce referential integrity (look at the next section for details). However, the presence of blank or non-matching values in a key column might affect the results of the DAX formulas and the appearance of PivotTables.

Referential Integrity

建立参考完整性涉及构建一组规则，以便在你输入或删除数据时，保留在表之间定义的关系。如果你没有专门确保这一点，由于 Power Pivot 不会强制执行，因此在进行数据更改之前创建的 DAX 公式可能无法得到正确的结果。

Establishing referential integrity involves building a set of rules to preserve the defined relationships between tables when you enter or delete data. If you do not exclusively ensure this, as Power Pivot does not enforce it, you might not get correct results with the DAX formulas created before data changes are made.

如果你强制执行参考完整性，你可以防止以下陷阱：

If you enforce referential integrity, you can prevent the following pitfalls −

  1. Adding rows to a related table when there is no associated row in the primary table (i.e. with matching values in the key columns).

  2. Changing data in a primary table that would result in orphan rows in a related table (i.e. rows with a data value in the key column that does not have a matching value in the primary table key column).

  3. Deleting rows from a primary table when there are matching data values in the rows of the related table.

Updating the Results of DAX Formulas

DAX 公式用于涉及海量数据（包括来自外部数据源的数据）的计算中。此类数据会因 DAX 计算针对实时数据而时常发生改变。

DAX formulas are used in calculations involving large data, including data from external data sources. The data can be subjected to changes from time to time as the DAX calculations are meant for live data as well.

在以下两种情况下，需要更新 DAX 公式的结果：

The results of DAX formulas need to get updated on two occasions −

  1. Data Refresh − When the data is refreshed.

  2. Recalculation − When there are changes in the DAX formula.

Understanding Data Refresh vs. Recalculation

数据刷新和重新计算是两个独立，但相互关联的操作。

Data refresh and recalculation are two separate but related operations.

  1. Data refresh is the process of updating the data in the Data Model in your workbook obtaining up-to-date data from external data sources.

  2. Recalculation is the process of updating all the columns, tables, and PivotTables in your workbook that contain DAX formulas, to reflect the changes in the underlying data that result from the changes to DAX formulas themselves.

在其中的 DAX 公式未重新计算之前，不应保存或发布工作簿。

You should not save or publish the workbook until the DAX formulas in it have been recalculated.

Different Ways to Update Data in Data Model

Power Pivot 不会自动检测外部数据源中的更改。

Power Pivot does not automatically detect changes in external data sources.

  1. You can refresh data manually from the Power Pivot window at intervals that you can specify.

  2. You can schedule an automatic data refresh from external sources, if you have published the workbook to a SharePoint site.

有关这些内容的详细信息，请参阅章节 Updating Data in Data Model 。

For details on these, refer to the chapter – Updating Data in Data Model.

Recalculation of DAX Formulas

DAX 公式的重新计算是一项重要任务，因为在重新计算期间，会检查列依赖关系，如果没有列已更改、数据无效，或者过去可以正常工作的 DAX 公式中出现错误，系统会通知您。

Recalculation of a DAX formula is an important task, because 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.

重新计算可能会通过以下方式影响性能：

Recalculation can affect performance in the following ways −

  1. For a calculated column, the result of DAX formula should always be recalculated for the entire column, whenever you change the DAX formula.

  2. For a calculated field, the result of DAX formula is not calculated until the calculated field is placed in the context of a PivotTable or a PivotChart. The DAX formula will be recalculated when you change any row or column heading that affects the filters on the data or when you manually refresh the PivotTable.

在 DAX 中，重新计算公式可以自动或手动完成。

In DAX, recalculating formulas can be done automatically or manually.

要了解有关重新计算的更多信息，请参阅章节—— Recalculating DAX Formulas 。

To learn more about recalculation, refer to the chapter – Recalculating DAX Formulas.

Excel DAX - Updating Data in the Data Model

DAX 用于对 Excel Power Pivot 中数据模型中的数据进行计算。DAX 使数据建模和报告活动得到有效处理。但是，这需要时不时更新数据模型中的数据，以便反映当前数据。

DAX is used for calculations on the data in the Data Model in Excel Power Pivot. DAX enables data modeling and reporting activities to be handled in an effective way. However, this requires updating the data in the Data Model from time to time so as to reflect the current data.

您可以通过建立数据连接，从外部数据源将数据导入到工作簿的数据模型中。您可以随时从源更新数据。如果您要获取来自包含实时销售信息或每天更新多次的数据源的关系数据库中的数据，那么此选项非常方便。

You can import data from an external data source into the Data Model of your workbook by establishing a data connection. You can update the data from the source whenever you choose. This option is handy if you are getting data from relational databases that contain live sales information or data feeds that are updated several times a day.

Different Ways of Updating Data in the Data Model

您可以通过以下方式更新数据模型中的数据 -

You can update the data in the Data Model in the following ways −

  1. Refreshing data in the Data Model from time to time.

  2. Making changes to data sources, such as connection properties.

  3. Updating the data in the Data Model after the source data has changed.

  4. Filtering the data to selectively load rows from a table in the data source.

Refreshing Data in the Data Model

除了从现有源获取更新的数据外，每当您对源数据的架构进行更改时，您还需刷新工作簿中的数据。这些更改可包括添加列或表，或更改导入的行。

In addition to getting updated data from an existing source, you will need to refresh data in your workbook whenever you make changes to the schema of the source data. These changes can include adding columns or tables, or changing the rows that are imported.

请注意，添加数据、更改数据或编辑筛选器始终会触发依赖于该数据源的 DAX 公式的重新计算。有关详细信息，请参阅章节 - 重新计算 DAX 公式。

Note that addition of data, changing data, or editing filters always triggers recalculation of DAX formulas that depend on that data source. Refer to the chapter – Recalculating DAX Formulas for details.

数据模型中有两种类型的数据刷新 -

You have two types of data refresh in Data Model −

Manual Refresh

如果您选择手动刷新选项，则可以随时手动刷新数据模型中的数据。您可以刷新所有数据（这是默认设置），或者可以手动选择要为各个数据源刷新的表和列。

If you choose manual refresh option, you can refresh the data in the Data Model manually at any time. You can refresh all data, which is the default, or you can manually choose the tables and columns to refresh for individual data sources.

Automatic or Scheduled Refresh

如果您已将工作簿发布到 PowerPivot 库或支持 PowerPivot 的 SharePoint 网站，那么您或 SharePoint 管理员可以创建用于自动更新工作簿中数据的计划。在这种情况下，您可以在服务器上计划无人值守的数据刷新。

If you have published your workbook to a PowerPivot Gallery or SharePoint site that supports PowerPivot, you or the SharePoint administrator can create a schedule for automatically updating the data in the workbook. In such a case, you can schedule unattended data refresh on the server.

Manually Refreshing an Existing Data Source

如果需要更新来自现有数据源的数据或获取用于设计新 DAX 公式的最新数据，你可以随时手动刷新你的数据。你可以刷新单个表格、共享相同数据连接的所有表格或数据模型中的所有表格。

You can manually refresh your data any time, if you need to update the data from an existing data source or get the recent data for designing new DAX formulas. You can refresh a single table, all tables that share the same data connection or all tables in the Data Model.

如果你从关系数据源（如 SQL Server 和 Oracle）导入了数据，你可以在一次操作中更新所有相关表格。将新数据或更新的数据加载到数据模型的操作通常会触发 DAX 公式的重新计算，而这两项操作都可能需要一些时间才能完成。因此，你应该在更改数据源或刷新从数据源获取的数据之前意识到潜在的影响。

If you have imported data from a relational data source, such as SQL Server and Oracle, you can update all the related tables in one operation. The operation of loading new or updated data into the Data Model often triggers recalculation of DAX formulas, both of which might require some time to complete. Hence, you should be aware of the potential impact before you change data sources or refresh the data that is obtained from the data source.

若要刷新数据模型中单个表格或所有表格的数据，请执行以下操作：

To refresh data for a single table or all tables in a Data Model, do the following −

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

  2. Click Refresh.

  3. Click Refresh in the dropdown list for refreshing the selected table.

  4. Click Refresh All in the dropdown list for refreshing all the tables.

manually resfreshing

若要刷新数据模型中使用相同连接的所有表格的数据，请执行以下操作：

To refresh data for all tables that use the same connection in a Data Model, do the following −

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

  2. Click the Existing Connections in the Get External Data group.

出现“现有连接”对话框。

Existing Connections dialog box appears.

  1. Select a connection.

  2. Click the Refresh button.

existing connections

出现“数据刷新”对话框，并且将显示数据刷新进度信息，因为 PowerPivot 引擎正在从所选表格或数据源中的所有表格重新加载数据。

Data Refresh dialog box appears and data refresh progress information is displayed as the PowerPivot engine reloads data from the selected table or from all tables from the data source.

有三种可能的结果：

There are three possible outcomes −

  1. Success − Reports on the number of rows imported into each table.

  2. Error − An error can occur if the database is offline, you no longer have permissions. A table or column is deleted or renamed in the source.

  3. Cancelled − This means Excel did not issue the refresh request, probably because refresh is disabled on the connection.

possible outcomes

单击“关闭”按钮。

Click the Close button.

Changing a Data Source

若要更改数据模型中的数据，你可以在 Power Pivot 窗口中编辑连接信息或更新数据模型中所用表格和列的定义。

To change the data in your Data Model, you can edit the connection information or update the definition of the tables and columns used in your Data Model in the Power Pivot window.

你可以对现有数据源进行以下更改：

You can make the following changes to the existing data sources −

Connections

  1. Edit the database name or the server name.

  2. Change the name of the source text file, spreadsheet, or data feed.

  3. Change the location of the data source.

  4. For relational data sources, change the default catalog or initial catalog.

  5. Change the authentication method or the credentials used to access the data.

  6. Edit advanced properties on the data source.

Tables

  1. Add or remove a filter on the data.

  2. Change the filter criteria.

  3. Add or remove tables.

  4. Change the table names.

  5. Edit mappings between tables in the data source and tables in the Data Model.

  6. Select different columns from the data source.

Columns

  1. Change the column names.

  2. Add new columns.

  3. Delete columns from the Data Model (does not affect the data source).

可通过以下方式编辑现有数据源的属性：

You can edit the properties of an existing data source in the following ways −

  1. You can change the connection information, including the file, feed, or database used as a source, its properties or other provider specific connection options.

  2. You can change the table and column mappings and remove references to columns that are no longer used.

  3. You can change the tables, views, or columns that you get from the external data source.

Modifying a Connection to an Existing Data Source

可以通过更改当前连接所用的外部数据源，来修改到外部数据源中创建的连接。但是，需要遵循的过程取决于数据源类型。

You can modify the connection that you have created to an external data source by changing the external data source used by the current connection. However, the procedure to be followed depends on the data source type.

  1. Click the Home tab on the Ribbon in the PowerPivot window.

  2. Click the Existing Connections in the Get External Data group.

modifying connection

出现“现有连接”对话框。选择要修改的连接。

Existing Connections dialog box appears. Select the connection that you want to modify.

根据所更改数据源的类型，供应商可能不同。另外，可用的属性可能需要更改。考虑一个连接到包含数据的 Excel 工作簿的简单示例。

Depending on the type of the data source you are changing, the provider might be different. Also the properties that are available may require change. Consider a simple example of a connection to an Excel workbook that contains the data.

excel workbook contains data

  1. Click the Edit button. Edit Connection dialog box appears.

  2. Click the Browse button to locate another database of the same type (Excel workbook in this example), but with a different name or location.

  3. Click the Open button.

新文件将被选中。会出现一条消息，说明您已修改连接信息，并且需要保存并刷新表以验证连接。

The new file will get selected. A message appears stating that you have modified connection information and you need to save and refresh the tables to verify the connection.

message

  1. Click the Save button. You will be back in the Existing Connections dialog box.

  2. Click the Refresh button. Data Refresh dialog box appears displaying the data refresh progress. The status of data refresh will be displayed. Refer to the section - Manually Refreshing an Existing Data Source for details.

  3. Click Close, once the data refresh is a success.

  4. Click Close in the Existing Connections dialog box.

Editing Table and Column Mappings (Bindings)

要在数据源更改时编辑列映射，请执行以下操作：

To edit the column mappings when a data source changes, do the following −

  1. Click the tab that contains the table you want to modify in the Power Pivot window.

  2. Click the Design tab on the Ribbon.

  3. Click the Table Properties.

editing table

将出现“编辑表属性”对话框。

Edit Table Properties dialog box appears.

edit table properties

您可以观察到以下内容 −

You can observe the following −

  1. The name of the selected table in the Data Model is displayed in the Table Name box.

  2. The name of the corresponding table in the external data source is displayed in the Source Name box.

  3. There are two options for column names from – Source and Modal.

  4. If the columns are named differently in the data source and in the Data Model, you can toggle between the two sets of column names by selecting these options.

  5. Preview of the selected table appears in the dialog box.

您可以编辑以下内容：

You can edit the following −

  1. To change the table that is used as a data source, select a different table than the selected one in the Source Name dropdown list.

  2. Change the column mappings if needed − To add a column that is present in the source but not in the Data Model, select the checkbox beside the column name. Repeat for all the columns that are to be added. The actual data will be loaded into the Data Model, the next time you refresh. If some columns in the Data Model are no longer available in the current data source, a message appears in the notification area that lists the invalid columns. You do not need to do anything.

  3. Click the Save button.

当您保存当前表属性集时，您将收到一条消息 - 请稍候。然后将显示检索到的行数。

When you save the current set of table properties, you will get a message – Please wait. Then the number of rows retrieved will be displayed.

在数据模型中的表中，任何无效列将自动删除，并将添加新列。

In the table in the Data Model, any invalid columns are automatically removed and new columns are added.

Changing a Column Name and Data Type

您可以按如下方式更改数据模型中表中的列名称：

You can change the name of a column in a table in the Data Model as follows −

  1. Double-click on the header of the column. The name of the column in the header will get highlighted.

  2. Type the new column name, overwriting the old name. Alternatively, you can change the name of a column in a table in the Data Model as follows:

  3. Select the column by clicking on its header.

  4. Right-click the column.

  5. Click Rename Column in the dropdown list.

changing column name

标题中列标题的名称将被高亮显示。输入新的列名称，覆盖旧名称。

The name of the column in the header will get highlighted. Type the new column name, overwriting the old name.

正如您所了解的，数据模型中表中列中的所有值必须具有相同的数据类型。

As you have learnt, all the values in a column in a table in the Data Model must be of the same data type.

若要更改列的数据类型，请执行以下操作：

To change the data type of a column, do the following −

  1. Select the column that you want to change by clicking its header.

  2. Click the Home tab on the Ribbon.

  3. Click the controls in the Formatting group to modify the column’s data type and format.

check the controls

Adding / Changing a Filter to a Data Source

您可以向数据源添加筛选器，当您导入数据时，可以限制数据模型中表中的行数。在以后，可以通过更改您之前定义的筛选器向数据模型中的表中添加更多行，或减少行数。

You can add a filter to a data source when you import data to restrict the number of rows in the table in the Data Model. Later, you can add more rows or decrease the number of rows in the table in the Data Model by changing the filter that you defined earlier.

Adding a Filter to a Data Source During Import

若要在数据导入期间向数据源添加新筛选器，请执行以下操作：

To add a new filter to a data source during data import, do the following −

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

  2. Click one of the data sources in the Get External Data group.

表导入向导对话框出现。

Table Import Wizard dialog box appears.

  1. Proceed to the step – Select Tables and Views.

  2. Select a table and then click Preview & Filter.

adding a filter to data source

预览选定表对话框将出现。

Preview Selected Table dialog box appears.

  1. Click the column on which you want to apply filter.

  2. Click the down arrow to the right of the column heading.

preview selected table

若要添加筛选器，请执行以下操作之一：

To add a filter, do one of the following −

  1. In the list of column values, select or clear one or more values to filter by and then click OK. However, if the number of values is extremely large, individual items might not be shown in the list. Instead, you will see the message - "Too many items to show."

  2. Click Number Filters or Text Filters (depending on the data type of the column). Then, click one of the comparison operator commands (such as Equals), or click Custom Filter. In the Custom Filter dialog box, create the filter and then click OK.

@{s0} − 如果您在任何阶段出错，请单击“清除行筛选器”按钮并重新开始。

Note − If you make a mistake at any stage, click the Clear Row Filters button and start over.

  1. Click OK. You will be back to Select Tables and Views page of Table Import Wizard.

view page

您可以看到，在列“筛选器详细信息”中，应用筛选器链接出现在您定义筛选器的列中。

As you can observe, in the column – Filter Details, a link Applied Filters appears for the column on which you defined the filter.

您可以单击链接以查看由向导建立的筛选器表达式。但是，每个筛选器表达式的语法取决于提供程序，您无法编辑它。

You can click the link to view the filter expression that was built by the wizard. But, the syntax for each filter expression depends on the provider and you cannot edit it.

link to view

  1. Click Finish to import the data with filters applied.

  2. Close the Table Import Wizard.

Changing a Filter to an Existing Data Source

在导入数据后，您可能必须定期更新它，方法是添加更多行或通过限制表中的现有行。在这种情况下，您可以更改表上的现有筛选器或添加新筛选器。

After you have imported the data, you might have to update it from time to time, by either adding more rows or by restricting the existing rows in the table. In such a case, you can change the existing filters on the table or add new filters.

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

  2. Click the Existing Connections in the Get External Data group. Existing Connections dialog box appears.

  3. Click the connection that contains the table on which you have to change the filter.

  4. Click the Open button.

changing a filter

您将进入表导入向导对话框。重复上一部分中的步骤以筛选列。

You will get into Table Import Wizard dialog box. Repeat the steps in the previous section to filter the columns.

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

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.

Excel DAX - Formula Errors

使用具有错误语法的 DAX formulas 时，可能会出错。计算字段和计算列可能包含需要特定类型参数的 DAX 函数。DAX 函数的参数可以是表、列或其他 DAX 函数（嵌套 DAX 函数）。由于 DAX 函数可以返回表和列，因此应仔细检查是否将正确类型的参数传递给 DAX 函数。

You can get errors when you write DAX formulas with wrong syntax. Calculated fields and calculated columns can contain DAX functions that require a specific type of arguments. Arguments of DAX functions can be tables, columns, or other DAX functions (nested DAX functions). As DAX functions can return tables and columns, care should be taken to check that the right type of arguments are passed to the DAX functions.

DAX 公式错误可以是语法错误或语义错误。这些错误可以在设计时或运行时发生。

DAX formula errors can be either syntax errors or semantic errors. The errors can occur either at design time or at run time.

在本章中，将了解一些常见的 DAX 错误，其原因以及如何修复这些错误。

In this chapter, you will learn about some common DAX errors, their causes, and how to fix those errors.

DAX Error: Calculation Aborted

当尝试创建（设计时）或使用（运行时）带有 DAX 时间智能函数的计算字段时，可能会发生以下错误。在每种情况下，都会向时间智能函数传递一个非连续日期范围。

The following error can occur when attempting to create (design-time) or use (run-time) a calculated field with a DAX time-intelligence function. In each case, a noncontiguous date range is being passed to the time intelligence function.

“DAX 错误：计算中止：MdxScript（实例）（00，0）函数“DATEADD”仅适用于连续日期选择。”

“DAX Error: CALCULATION ABORTED: MdxScript (instance) (00, 0) Function ‘DATEADD’ only works with contiguous date selections.”

Cause at Run-time

当计算字段带 DAX 时间智能函数被放置在透视表的 VALUES 区域中，但在选择年份之前选择了诸如月份或季度的日期字段作为切片器或筛选器时，可能会显示该错误。例如，如果您有 2014、2015 和 2016 年这三年数据，并且您尝试仅在不选择年字段的情况下使用 3 月份，那么这些值就不是连续的数据值，您将收到错误。

This error can be displayed when a calculated field with a DAX time intelligence function is placed in the VALUES area of a PivotTable and date fields such as the month or the quarter are selected as slicers or filters before selecting a year. For example, if you have data of three years – 2014, 2015, and 2016 and you try to use only the month March without selecting the Year field, then the values are not contiguous data values and you will get an error.

How to Fix the Error at Run-time?

在上面示例中，

In the above example,

  1. First add Year as a slicer or a filter and select a year.

  2. Then, add Month or Quarter as a slicer or a filter.

  3. Then, select one or more months or quarters to slice or filter on for the year selected.

Cause at Design-time

DAX 时间智能函数需要为日期参数指定一个日期列。日期列必须具有连续的日期范围。如果日期列中一行或多行中的日期值与前一行和后一行的日期值不连续，则可能会返回该错误。

DAX time intelligence functions require a date column specified for the date argument. The date column must have a contiguous range of dates. This error can be returned, if there is a date value in one or more rows in the date column that is not contiguous with the data values in the previous and successive rows.

如果您从数据源导入了包含日期的表，请记住，许多组织运行特殊的进程来扫描数据库中的表以查找无效值，并用特定值替换这些值。也就是说，如果找到无效日期，它将被指定为特定日期值，该日期值可能与列中的其他日期值不连续。

If you imported your table containing dates from a data source, remember that many organizations run special processes that scan tables in databases for invalid values and replace those with a particular value. That is, if an invalid date is found, it is assigned a particular date value that may not be contiguous with other data values in the column.

How to Fix This Error at Design-time?

采取以下措施以在设计时修复该错误−

Do the following to fix the error at design time −

  1. If your date table is imported from a data source, use Refresh in Power Pivot window to reimport any changes found at the source.

  2. Check the values in your date column to make sure they are in a contiguous order. If any value is found not to be in place, it will have to be corrected at the source and the date table will have to be refreshed.

  3. Create a separate date table and date column in your Data Model. Specify the new date column as the date argument in the formula causing the error. Date tables are easy to create and add to a Data Model.

DAX Semantic Error - An Example

以下 DAX 错误是语义错误−

The following DAX error is a semantic error −

“函数 ‘CALCULATE’ 已用于真假表达式，该表达式用作表筛选器表达式。不允许这样做。”

“A function ‘CALCULATE’ has been used in a true-false expression that is used as a table filter expression. This is not allowed.”

Cause

当一个或多个筛选器表达式无法在计算字段或计算列表达式的上下文中使用时，可能会出现该错误。

This error can appear when one or more filter expressions cannot be used in context of the calculated field or calculated column expression.

在大多数情况下，此错误是由指定为 DAX CALCULATE 函数参数的筛选器表达式导致的。CALCULATE 函数需要将筛选器定义为布尔表达式或表表达式。

In most of the cases, this error is caused by a filter expression specified as an argument to the DAX CALCULATE function. The CALCULATE function requires filters defined as a Boolean expression or a table expression.

How to Fix Such Errors?

可以通过使用 DAX FILTER 函数将筛选器定义为表表达式（该表表达式随后可以用作 DAX CALCULATE 函数的参数）来修复此类错误。

You can fix such errors by using the DAX FILTER function to define filters as a table expression, which can then be used as an argument to the DAX CALCULATE function.

Excel DAX - Time Intelligence

DAX 有一项重要且强大的功能，称为 Time Intelligence 。时间智能让您可以编写引用时间段的 DAX 公式，以便在数据透视表中使用。

DAX has an important and powerful feature, referred to as Time Intelligence. Time intelligence enables you to write DAX formulas that refer to the time periods for use in the PivotTables.

DAX 有 35 个时间智能函数，专门用于随着时间的推移聚合和比较数据。但是，这些 DAX 函数对您需要了解和谨慎使用以避免错误的数据有一些限制。

DAX has 35 time-intelligence functions specifically for aggregating and comparing data over time. However, these DAX functions have some constraints on the data that you need to understand and work with caution to avoid errors.

Why Time Intelligence Makes DAX Powerful?

时间智能函数处理不断变化的数据，具体取决于您在数据透视表和 Power View 可视化中选择的上下文。正如您所知，大多数数据分析涉及按时间段对数据进行汇总，比较跨时间段的数据值，了解趋势并根据未来预测做出决策。

The time intelligence functions work with data that is constantly changing, depending on the context you select in PivotTables and Power View visualizations. As you are aware, most of the data analysis involves summarization of data over time periods, comparing data values across the time periods, understanding the trends and making decisions based on future projections.

例如，您可能希望按产品汇总过去一个月的销售额，并将其与财政年度其他月份的总数进行比较。这意味着，您必须使用日期来按特定时间段对销售交易进行分组和汇总。

For example, you might want to sum sales amounts for the past month product-wise and compare the totals with those of other months in the fiscal year. This means, you have to use the dates as a way to group and aggregate sales transactions for a particular period in time.

这里您可以观察到 DAX 的强大功能。您可以使用 DAX 时间智能函数定义计算字段，从而帮助您随着时间的推移分析数据，而无需更改数据透视表中的日期选择。这使得您的工作更轻松。此外，您可以构建以其他方式无法构建的数据透视表。

This is where you can observe the power of DAX. You can use DAX time intelligence functions to define calculated fields that help you in analyzing the data over time, without having to change the date selections in the pivot tables. This makes your job easier. Moreover, you can build PivotTables that would not be possible any other way.

Requirements for DAX Time Intelligence Functions

DAX 时间智能函数有特定要求。如果未满足这些要求，您可能会收到错误或它们可能无法正常工作。因此，您还可将这些要求称为规则或限制。以下是某些 DAX 时间智能函数要求/规则/限制 −

DAX time intelligence functions have certain requirements. If these requirements are not met, you might get errors or they may not work properly. Hence, you can refer to these requirements as rules or constraints as well. Following are certain DAX time intelligence functions requirements/rules/constraints −

  1. You need to have a date table in your Data Model.

  2. The date table must include a column considered to be the Date column by DAX. You can name the column the way you want, but it should comply with the following conditions: o The date column should contain a contiguous set of dates that covers every day in the time period you are analyzing the data. Every date must exist once and only once in the date column. You cannot skip any dates (For e.g. you cannot skip weekend dates).

  3. DAX time intelligence functions work only on a standard calendar and assume the start of the year as January 1 and the end of the year as December 31, with the months in the year and days in each month as of a calendar year.

但是，您可以为不同的财政年度自定义标准日历。在使用任何时间智能函数之前，验证上述要求是一个好习惯。

However, you can customize a standard calendar for different financial years. It is a good practice to verify the above requirements before any time intelligence function is used.

有关日期表及其在 DAX 公式中使用的更多详细信息，请参阅本教程库中的教程 = DAX 中的数据建模。

For more details on date tables and their usage in DAX formulas, refer to the tutorial = Data Modeling with DAX in this tutorials library.

DAX Time Intelligence Functions – Categories

DAX 时间智能函数可分类如下 −

DAX Time Intelligence functions can be categorized as follows −

  1. DAX functions that return a single date.

  2. DAX functions that return a table of dates.

  3. DAX functions that evaluate expressions over a time period.

DAX Functions That Return a Single Date

此类别的 DAX 函数返回一个日期。

DAX functions in this category return a single date.

此类别有 10 个 DAX 函数 −

There are 10 DAX functions in this category −

Sr.No.

DAX Function & Return Value

1

FIRSTDATE (Date_Column) Returns the first date in the Date_Column in the current context.

2

LASTDATE (Date_Column) Returns the last date in the Date_Column in the current context.

3

FIRSTNONBLANK (Date_Column, Expression) Returns the first date where an expression has a non-blank value.

4

LASTNONBLANK (Date_Column, Expression) Returns the last date where an expression has a non-blank value.

5

STARTOFMONTH (Date_Column) Returns the first date of a month in the current context.

6

ENDOFMONTH (Date_Column) Returns the last date of a month in the current context.

7

STARTOFQUARTER (Date_Column) Returns the first date of a quarter in the current context.

8

ENDOFQUARTER (Date_Column) Returns the last date of a quarter in the current context.

9

STARTOFYEAR (Date_Column, [YE_Date]) Returns the first date of a year in the current context.

10

ENDOFYEAR (Date_Column, [YE_Date]) Returns the last date of a year in the current context.

DAX Functions That Return a Table of Dates

此类别的 DAX 函数返回日期表。这些函数主要用作 DAX 函数 CALCULATE 的 SetFilter 参数。

DAX Functions in this category return a table of dates. These functions will be mostly used as a SetFilter argument to the DAX function - CALCULATE.

此类别有 16 个 DAX 函数。其中 8 个 DAX 函数是“previous”和“next”函数。

There are 16 DAX functions in this category. Eight (8) of these DAX functions are the “previous” and “next” functions.

  1. The “previous” and “next” functions start with a date column in the current context and calculate the previous or next day, month, quarter or year.

  2. The “previous” functions work backward from the first date in the current context and the “next” functions move forward from the last date in the current context.

  3. The “previous” and “next” functions return the resulting dates in the form of a single column table.

Sr.No.

DAX Function & Return Value

1

PREVIOUSDAY (Date_Column) Returns a table that contains a column of all dates representing the day that is previous to the first date in the Date_Column in the current context.

2

NEXTDAY (Date_Column) Returns a table that contains a column of all dates from the next day, based on the first date specified in the Date_Column in the current context.

3

PREVIOUSMONTH (Date_Column) Returns a table that contains a column of all dates from the previous month, based on the first date in the Date_Column in the current context.

4

NEXTMONTH (Date_Column) Returns a table that contains a column of all dates from the next month, based on the first date in the Date_Column in the current context.

5

PREVIOUSQUARTER (Date_Column) Returns a table that contains a column of all dates from the previous quarter, based on the first date in the Date_Column in the current context.

6

NEXTQUARTER (Date_Column) Returns a table that contains a column of all dates in the next quarter, based on the first date specified in the Date_Column in the current context.

7

PREVIOUSYEAR (Date_Column, [YE_Date]) Returns a table that contains a column of all dates from the previous year, given the last date in the Date_Column in the current context.

8

NEXTYEAR (Date_Column, [YE_Date]) Returns a table that contains a column of all dates in the next year, based on the first date in the Date_Column in the current context.

四个 (4) DAX 函数计算一个时期的日期集。这些函数使用当前上下文中的最后日期执行计算。

Four (4) DAX functions calculate a set of dates in a period. These functions perform the calculations using the last date in the current context.

Sr.No.

DAX Function & Return Value

1

DATESMTD (Date_Column) Returns a table that contains a column of the dates for the month to date, in the current context.

2

DATESQTD (Date_Column) Returns a table that contains a column of the dates for the quarter to date, in the current context.

3

DATESYTD (Date_Column, [YE_Date]) Returns a table that contains a column of the dates for the year to date, in the current context.

4

SAMEPERIODLASTYEAR (Date_Column) Returns a table that contains a column of dates shifted one year back in time from the dates in the specified Date_Column, in the current context. Note− SAMEPERIODLASTYEAR requires that the current context contains a contiguous set of dates. If the current context is not a contiguous set of dates, then SAMEPERIODLASTYEAR will return an error.

  1. Four (4) DAX functions are used to shift from the set of dates that are in the current context to a new set of dates. These DAX functions are more powerful than the previous ones. DAX functions – DATEADD, DATESINPERIOD and PARALLELPERIOD shift some number of time intervals from the current context. The interval can be day, month, quarter or year, represented by the key words – DAY, MONTH, QUARTER and YEAR respectively. For example:

  2. Shift backward by 2 days.

  3. Move forward by 5 months.

  4. Move forward by one month from today.

  5. Go back to same quarter in the last year. If the function argument - number of intervals (integer value) is positive, shift is forward and if it is negative, shift is backward. DAX function – DATESBETWEEN calculates the set of dates between the specified start date and the end date.

Sr.No.

DAX Function & Return Value

1

DATEADD (Date_Column, Number_of_Intervals, Interval) Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context.

2

DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, Interval) Returns a table that contains a column of dates that begins with the start_date and continues for the specified number_of_intervals.

3

PARALLELPERIOD (Date_Column, Number_of_Intervals, Interval) Returns a table that contains a column of dates that represents a period parallel to the dates in the specified Date_Column in the current context, with the dates shifted a number of intervals either forward or backward in time.

4

DATESBETWEEN (Date_Column, Start_Date, End_Date) Returns a table that contains a column of dates that begins with the start_date and continues until the end_date.

DAX Functions that Evaluate Expressions Over a Time Period

此类别中的 DAX 函数在指定的时间段内对表达式求值。

DAX Functions in this category evaluate an expression over a specified time period.

此类别中有九 (9) 个 DAX 函数 −

There are nine (9) DAX functions in this category −

  1. Three (3) DAX functions in this category can be used to evaluate any given expression over a specified time period.

Sr.No.

DAX Function & Return Value

1

TOTALMTD (Expression, Date_Column, [SetFilter]) Evaluates the value of the expression for the dates in the month to date, in the current context.

2

TOTALQTD (Expression, Date_Column, [SetFilter]) Evaluates the value of the expression for the dates in the quarter to date, in the current context.

3

TOTALYTD (Expression, Date_Column, [SetFilter], [YE_Date]) Evaluates the value of the expression for the dates in the year to date, in the current context

  1. Six (6) DAX functions in this category can be used to calculate the opening and the closing balances. The opening balance for any period is the same as the closing balance for the previous period. The closing balance includes all data through the end of the period, while the opening balance does not include any data from within the current period. These DAX functions always return the value of an expression evaluated for a specific point in time.

  2. The point in time we care about is always the last possible date value in a calendar period.

  3. The opening balance is based on the last date of the previous period, while the closing balance is based on the last date in the current period.

  4. The current period is always determined by the last date in the current date context.

Sr.No.

DAX Function & Return Value

1

OPENINGBALANCEMONTH (Expression, Date_Column, [SetFilter]) Evaluates the expression at the first date of the month in the current context.

2

CLOSINGBALANCEMONTH (Expression, Date_Column, [SetFilter]) Evaluates the expression at the last date of the month in the current context.

3

OPENINGBALANCEQUARTER (Expression, Date_Column, [SetFilter]) Evaluates the expression at the first date of the quarter, in the current context.

4

CLOSINGBALANCEQUARTER (Expression, Date_Column, [SetFilter]) Evaluates the expression at the last date of the quarter in the current context.

5

OPENINGBALANCEYEAR (Expression, Date_Column, [SetFilter], [YE_Date]) Evaluates the expression at the first date of the year in the current context.

6

CLOSINGBALANCEYEAR (Expression, Date_Column, [SetFilter], [YE_Date]) Evaluates the expression at the last date of the year in the current context.

Excel DAX - Filter Functions

DAX 具有强大的功能 filter functions ，这与 Excel 函数有着很大的不同。查找功能通过使用表和关系（如数据库）而起作用。筛选功能允许您操作数据上下文以创建动态计算。

DAX has powerful filter functions that are quite different from Excel functions. The lookup functions work by using tables and relationships, like a database. The filtering functions let you manipulate data context to create dynamic calculations.

Note － 返回表的 DAX 筛选功能不会将该表添加到数据模型中。结果表将用作其他 DAX 函数中的参数。也就是说，此类 DAX 函数用作其他 DAX 函数的嵌套函数。

Note − DAX filter functions that return a table do not add the table to the Data Model. The resulting table is used as an argument in another DAX function. That is, such DAX functions are used as nested functions with other DAX functions.

在下一部分，您将学习您可以使用的 DAX 筛选功能。有关这些函数的更多详细信息，请参阅教程 － 本教程库中的 DAX 函数。

In the next section, you will learn what DAX filter functions you can use. For more details on these functions, refer to the tutorial – DAX Functions in this tutorials library.

DAX Filter Functions

以下为 DAX 筛选功能：

Following are the DAX Filter functions −

Sr.No.

DAX Function & What the Function Does?

1

ADDMISSINGITEMS (<showAllColumn>, [<showAllColumn>] …, <table>, <groupingColumn>, [<groupingColumn>] …, [filterTable] …) Adds combinations of items from multiple columns to a table if they do not already exist. The determination of which item combinations to add is based on referencing source columns which contain all the possible values for the columns. To determine the combinations of items from different columns to evaluate − AutoExist is applied for columns within the same table.CrossJoin is applied across different tables.

2

*ALL ( {<table>

<column>, [<column>], [<column>], … })* Returns all the rows in the given table or all the values in the specified columns in a table, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

3

ALLEXCEPT (<table>, <column>, [<column>], …) Removes all context filters in the table except filters that have been applied to the columns specified as arguments. As against ALL, you can use this function when you want to remove the filters on many, but not all, columns in a table.

4

*ALLNOBLANKROW (<table>

<column>)* From the parent table of a relationship, returns − all rows but the blank rows, orall distinct values of a column but the blank rowsThe function disregards any context filters that might exist.

5

*ALLSELECTED ( [<tableName>

<columnName>] )* Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

6

CALCULATE (<expression>, [<filter1>, <filter2> …)] Evaluates an expression in a context that is modified by the specified filters. Returns the value that is the result of the expression.

7

CALCULATETABLE (<expression>, <filter1>, <filter2>, …) Evaluates a table expression in a context modified by the given filters. Returns a table of values.

8

CROSSFILTER (<columnName1>, <columnName2>, <direction>) Specifies the cross-filtering direction to be used in a calculation for a relationship that exists between two columns. Does not return any value.

9

DISTINCT (<column>) Returns a one-column table that contains the distinct values from the specified column. In other words, duplicate values are removed and only unique values are returned. The resulting column is used as an argument in another DAX function.

10

EARLIER (<column>, <number>) Returns the current value of the specified column in an outer evaluation pass of the mentioned column specified by the number.

11

EARLIEST (<column>) Returns the current value of the specified column in an outer evaluation pass of the specified column.

12

FILTER (<table>, <filter>) Returns a table that contains only the filtered rows. FILTER is used only as a function that is embedded in other functions that require a table as an argument.

13

FILTERS (<columnName>) Returns the values that are directly applied as filters to columnName. FILTERS is used only as a function that is embedded in other functions that require a table as an argument.

14

HASONEFILTER (<columnName>) Returns TRUE when the number of directly filtered values on columnName is one. Otherwise, returns FALSE.

15

HASONEVALUE (<columnName>) Returns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise, returns FALSE.

16

ISCROSSFILTERED (<columnName>) Returns TRUE when columnName or another column in the same or related table is being filtered.

17

ISFILTERED (<columnName>) Returns TRUE when columnName is being filtered directly. If there is no filter on the column or if the filtering happens because a different column in the same table or in a related table is being filtered, then the function returns FALSE.

18

KEEPFILTERS (<expression>) Modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function.

19

RELATED (<column>) Returns a related value from another table.

20

RELATEDTABLE (<tableName>) Evaluates a table expression in a context modified by the given filters.

21

SUBSTITUTEWITHINDEX (<table>, <indexColumnName>, <indexColumnTable>, <orderBy_expression>, [<order>]) Returns a table which represents a left semijoin of the two tables supplied as arguments. The semijoin is performed by using common columns, determined by common column names and common data type. The columns being joined on are replaced with a single column in the returned table which is of type integer and contains an index. The index is a reference into the right join table given a specified sort order.

22

USERELATIONSHIP ( <columnName1>,<columnName2>) Specifies the relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2.

23

Excel DAX - Scenarios

在之前的章节中，您已了解 DAX 语法、DAX 运算符和 DAX 函数的用法。您可能知道，DAX 是一种用于数据建模和数据分析的公式语言。

You have learnt DAX syntax, the usage of DAX operators and DAX functions in the previous chapters. As you are aware, DAX is a formula language used for data modeling and data analysis.

DAX 可用于各种场景。基于 DAX 场景，DAX 优化了性能并产生了准确、有效的结果。在本章中，您将了解一些 DAX 场景。

DAX can be used in various scenarios. Based on the DAX scenarios, DAX optimizes the performance and produces accurate and effective results. In this chapter, you will get to know some of the DAX scenarios.

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

  1. Create custom calculations for a PivotTable.

  2. Apply a filter to a formula.

  3. Remove filters selectively to create a dynamic ratio.

  4. Use a value from an outer loop.

有关详细信息，请参阅“场景 - 执行复杂计算”一章。

For details, refer to the chapter Scenarios - Performing Complex Calculations.

Working with Text and Dates

DAX 可用于涉及文本处理、提取和组合日期时间值或基于条件创建值的场景。您可以使用 DAX 执行以下操作：

DAX can be used in the scenarios involving working along with text, extracting and composing date and time values, or creating values based on a condition. You can do the following with DAX −

  1. Create a key column by concatenation.

  2. Compose a date based on date parts extracted from a text date.

  3. Define a custom date.

  4. Change data types using a formula. Convert real numbers to integers.Convert real numbers, integers, or dates to strings.Convert strings to real numbers or dates.

有关详细信息，请参阅“场景 - 处理文本和日期”一章。

For details, refer to the chapter Scenarios - Working with Text and Dates.

Conditional Values and Testing for Errors

DAX 函数可以测试数据中的值，并根据条件返回不同的值。测试值的 DAX 函数对于检查值的范围或类型也很有帮助，可防止意外数据错误中断计算。您可以使用 DAX 执行以下操作：

DAX functions enable to test values in the data and return a different value based on a condition. DAX functions that test values are also useful for checking the range or type of values, to prevent unexpected data errors from breaking calculations. You can do the following with DAX −

  1. Create a value based on a condition.

  2. Test for errors within a formula.

有关详细信息，请参阅“场景 - 条件值和错误测试”一章。

For details, refer to the chapter Scenarios - Conditional Values and Testing for Errors.

Using Time Intelligence

您已在“了解 DAX 时间智能”一章中了解有关 DAX 时间智能函数的信息。

You have learnt about DAX time intelligence functions in the chapter – Understanding DAX time intelligence.

DAX 时间智能函数包括可以帮助您从数据中检索日期或日期范围的函数。然后，您可以使用这些日期或日期范围计算类似期间的值。时间智能函数还包括使用标准日期间隔的函数，使您能够比较月、年或季度的值。您还可以创建 DAX 公式来比较指定期间内第一个和最后一个日期的值。

DAX time intelligence functions include functions to help you retrieve dates or date ranges from your data. You can then use those dates or date ranges to calculate values across similar periods. The time intelligence functions also include functions that work with standard date intervals, to allow you to compare values across months, years, or quarters. You could also create a DAX formula that compares values for the first and the last date of a specified period.

您还可以了解有关 DAX intelligence 函数的更多信息以及它们可以用于以下方面的内容：

You can learn more about DAX intelligence functions and what they can do for the following −

  1. Calculate Cumulative Sales.

  2. Compare Values over Time.

  3. Calculate a Value over a Custom Date Range.

有关详细信息，请参阅“情景 - 使用时间智能”一章。

For details, refer to the chapter Scenarios - Using Time Intelligence.

Ranking and Comparing Values

如果您只想显示列或数据透视表中前面 n 项，您有以下选项：

If you want to show only the top n number of items in a column or a PivotTable, you have the following options −

  1. Apply a filter to show only the top or bottom few items.

  2. Create a DAX formula that dynamically ranks values and apply a filter.

每个选项都有利有弊。

Each of these options have pros and cons.

有关详细信息，请参阅“情景 - 对值进行排名和比较”一章。

For details, refer to the chapter Scenarios - Ranking and Comparing Values.

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 −

  1. Create custom calculations for a PivotTable.

  2. Apply a filter to a DAX formula.

  3. Remove filters selectively to create a dynamic ratio.

  4. 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.

  1. Define a calculated field – Percentage of Medal Count as shown in the following screenshot.

calculated fields

通过这个 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.

row labels

在上面的截图中，将国家/地区筛选为美国，并且在数据透视表中显示了前 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.

filtering data

使用此计算字段后，透视表会显示在下个屏幕截图中。

With this calculated field, the PivotTable looks as shown in the following screenshot.

calculated field 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.

Using a Value from an Outer Loop

可以使用来自先前循环的一个值来使用 DAX EARLIER 函数创建一组相关计算。此 DAX 函数最多支持两级嵌套循环。

You can use a value from a previous loop in creating a set of related calculations with DAX EARLIER function. This DAX function supports up to two levels of nested loops.

Excel DAX - Working with Text and Dates

DAX 可用于涉及使用文本、提取和组合日期时间值或根据条件创建值的场景中。你可以使用 DAX 做以下事情 -

DAX can be used in the scenarios involving working along with text, extracting and composing date and time values or creating values based on a condition. You can do the following with DAX −

  1. Create a key column in a table by concatenation.

  2. Compose a date based on date parts extracted from a text date.

  3. Define a custom date format.

  4. Change data types using a formula. Convert real numbers to integers.Convert real numbers, integers, or dates to strings.Convert strings to real numbers or dates.

Creating a Key Column by Concatenation

PowerPivot 中的数据模型仅允许使用一个键列。它不支持可能在外部数据源中找到的复合键。因此，如果数据源中表中存在复合键，你需要将它们合并到数据模型中表中的一个键列中。

Data Model in PowerPivot allows only a single key column. It does not support composite keys that you might find in the external data sources. Hence, if any composite keys exist in a table in the data source, you need to combine them into a single key column for the table in the Data Model.

可以使用 DAX 函数 CONCATENATE 将两列合并到数据模型中表的单列中。DAX 函数 CONCATENATE 会将两个文本字符串连接成一个文本字符串。连接的项可以是文本、数字或布尔值（表示为文本）或这些项的组合。如果此列包含适当的值，也可以使用列引用。

You can use the DAX function CONCATENATE to combine two columns into a single column in a table in the Data Model. DAX function CONCATENATE joins two text strings into one text string. The joined items can be text, numbers, or Boolean values represented as text or a combination of those items. You can also use a column reference if the column contains appropriate values.

= CONCATENATE ([Column1], [Column2])

DAX CONCATENATE 函数仅接受两个参数。如果任何参数不是文本数据类型，它将被转换为文本。DAX CONCATENATE 函数返回连接后的字符串。

DAX CONCATENATE function accepts only two arguments. If any of the arguments is not of text data type, it will be converted to text. DAX CONCATENATE function returns the concatenated string.

Date Based on Date Parts Extracted from a Text Date

Power Pivot 中的数据模型支持对日期和时间值使用数据类型 datetime。对日期和/或时间值起作用的 DAX 函数需要对参数使用 datetime 数据类型。

Data Model in Power Pivot supports a data type datetime for date and time values. The DAX functions that work on date and/or time values require the datetime data type for the arguments.

如果数据源包含不同格式的日期，你需要首先使用 DAX 公式提取日期部分，并将这些部分组合以构成一个有效的 DAX 日期时间数据类型。

If your data source contains dates of a different format, you need to first extract the date parts using a DAX formula and combine those parts to constitute a valid DAX datetime data type.

你可以使用以下 DAX 函数来提取和组合日期：

You can use the following DAX functions to extract and compose dates −

DATE - 返回日期时间格式的指定日期。

DATE − Returns the specified date in datetime format.

DATEVALUE - 将文本格式的日期转换为日期时间格式的日期。

DATEVALUE − Converts a date in the form of text to a date in datetime format.

TIMEVALUE - 将文本格式的时间转换为日期时间格式的时间。

TIMEVALUE − Converts a time in text format to a time in datetime format.

Defining a Custom Date Format

假设数据源中的日期未以标准格式表示。你可以定义自定义日期格式，以确保正确处理值。DAX FORMAT 函数使你能够根据指定格式将值转换为文本。

Suppose the dates in your data source are not represented by a standard format. You can define a custom date format to ensure that the values are handled correctly. DAX FORMAT function enables you to convert a value to text according to the specified format.

FORMAT (<value>, <format_string>)

FORMAT 函数返回一个字符串，其中包含按 format_string 定义的格式化值。

FORMAT function returns a string containing value formatted as defined by format_string.

你可以使用预定义的日期和时间格式，也可以为 FORMAT 函数的参数 format_string 创建用户定义的日期和时间格式。

You can either use pre-defined Date and Time formats or you can create user-defined Date and Time formats for the argument format_string of the FORMAT function.

以下是预定义的日期和时间格式名称。如果你使用除以下这些预定义字符串之外的字符串，它们将被解释为自定义日期和时间格式。

Following are the predefined date and time format names. If you use strings other than these predefined strings, they will be interpreted as a custom date and time format.

S. No.

Format_String & Description

1

"General Date" Displays a date and/or time. For example, 2/10/2015 10:10:32 AM

2

"Long Date" or "Medium Date" Displays a date according to long date format. For example, Wednesday, March 07, 2016

3

"Short Date" Displays a date using short date format. For example, 2/03/2016

4

"Long Time" Displays a time using long time format. Typically includes hours, minutes and seconds. For example, 10:10:32 AM

5

"Medium Time" Displays a time in 12-hour format. For example, 09:30 PM

6

"Short Time" Displays a time in 24-hour format. For example, 14:15

或者，你可以使用下表中的字符来创建用户定义的日期/时间格式。

Alternatively, you can use the characters in the following table to create user-defined date/time formats.

S. No.

Character & Description

1

: Time separator. Time separator. Separates hours, minutes, and seconds when time values are formatted.

2

/ Date separator. Separates the day, month, and year when date values are formatted.

3

% Used to indicate that the following character should be read as a single-letter format without regard to any trailing letters. Also used to indicate that a single-letter format is read as a userdefined format.

以下是各个字符的详细信息。

Following are the details of the various characters.

  1. %d − Displays the day as a number without a leading zero (e.g. 5).

  2. %dd − Displays the day as a number with a leading zero (e.g. 05).

  3. %ddd − Displays the day as an abbreviation (e.g. Sun).

  4. %dddd − Displays the day as a full name (e.g. Sunday).

  5. %M − Displays the month as a number without a leading zero (e.g. January is represented as 1).

  6. %MM − Displays the month as a number with a leading zero (e.g. January is represented as 01).

  7. %MMM − Displays the month as an abbreviation (e.g. January is represented as Jan).

  8. %MMMM − Displays the month as a full month name (e.g. January).

  9. %gg − Displays the period/era string (e.g. A.D.).

  10. %h − Displays the hour as a number without leading zeros using the 12-hour clock (e.g. 1:15:15 PM). Use %h if this is the only character in your user-defined numeric format.

  11. %hh − Displays the hour as a number with leading zeros using the 12-hour clock (e.g. 01:15:15 PM).

  12. %H − Displays the hour as a number without leading zeros using the 24-hour clock (e.g. 13:15:15, 1:15:15). Use %H if this is the only character in your user-defined numeric format.

  13. %HH − Displays the hour as a number with leading zeros using the 24-hour clock (e.g. 13:15:15, 1:15:15).

  14. %m − Displays the minute as a number without leading zeros (e.g. 2:1:15). Use %m if this is the only character in your user-defined numeric format.

  15. %mm − Displays the minute as a number with leading zeros (e.g. 2:01:15).

  16. %s − Displays the second as a number without leading zeros (e.g. 2:15:5). Use %s if this is the only character in your user-defined numeric format.

  17. %ss − Displays the second as a number with leading zeros (e.g. 2:15:05).

  18. %f − Displays fractions of seconds. For e.g. ff displays hundredths of seconds, whereas ffff displays ten-thousandths of seconds. You can use up to seven f symbols in your user-defined format. Use %f if this is the only character in your user-defined numeric format.

  19. %t − Uses the 12-hour clock and displays an uppercase A for any hour before noon; displays an uppercase P for any hour between noon and 11:59 P.M. Use %t if this is the only character in your user-defined numeric format.

  20. %tt − For locales that use a 12-hour clock, displays an uppercase AM with any hour before noon; displays an uppercase PM with any hour between noon and 11:59 P.M. For locales that use a 24-hour clock, displays nothing.

  21. %y − Displays the year number (0-9) without leading zeros. Use %y if this is the only character in your user-defined numeric format.

  22. %yy − Displays the year in two-digit numeric format with a leading zero, if applicable.

  23. %yyy − Displays the year in four-digit numeric format.

  24. %yyyy − Displays the year in four-digit numeric format.

  25. %z − Displays the timezone offset without a leading zero (e.g. -8). Use %z if this is the only character in your user-defined numeric format.

  26. %zz − Displays the. timezone offset with a leading zero (e.g. -08)

  27. %zzz − Displays the full timezone offset (e.g. -08:00).

正如您所观察到的，格式化字符串区分大小写。可以通过使用不同的情况来获取不同的格式。

As you can observe, formatting strings are case sensitive. Different formatting can be obtained by using a different case.

Changing Data Types of DAX Formula Outputs

在 DAX 公式中，输出的数据类型由源列决定，并且您不能明确指定结果的数据类型。这是因为最佳的数据类型由 Power Pivot 决定。然而，您可以使用 Power Pivot 执行的隐式数据类型转换来操作输出数据类型。否则，可以使用某些 DAX 函数转换输出数据类型。

In DAX formulas, the data type of the output is determined by the source columns and you cannot explicitly specify the data type of the result. This is because the optimal data type is determined by Power Pivot. However, you can use the implicit data type conversions performed by Power Pivot to manipulate the output data type. Otherwise, you can use certain DAX functions to convert the output data type.

Using the Implicit Data Type Conversions

Using the Implicit Data Type Conversions

  1. To convert a date or a number string to a number, multiply by 1.0. For example, = (TODAY()+5)*1.0. This formula calculates the current date plus 5 days and converts the result to an integer value. To convert a date, number or currency value to a string, concatenate the value with an empty string. For example, = Today() & “”

Using the DAX Functions for Data Type Conversions

Using the DAX Functions for Data Type Conversions

您可以将 DAX 函数用于以下内容 −

You can use DAX functions for the following −

  1. Converting Real Numbers to Integers.

  2. Converting Real Numbers, Integers or Dates to Strings.

  3. Converting Strings to Real Numbers or Dates.

您将在以下部分学习这一点。

You will learn this in the following sections.

Converting Real Numbers to Integers

您可以使用以下 DAX 函数将实数转换成整数−

You can use the following DAX functions for converting real numbers to integers −

ROUND (<number>, <num_digits>) − 将数字舍入到指定位数，并返回一个十进制数。

ROUND (<number>, <num_digits>) − Rounds a number to the specified number of digits and returns a decimal number.

CEILING (<number>, <significance>) − 将数字向上舍入，至最接近的整数或最接近的显着倍数，并返回一个十进制数。

CEILING (<number>, <significance>) − Rounds a number up, to the nearest integer or to the nearest multiple of significance and returns a decimal number.

FLOOR (<number>, <significance>) − 将数字向下舍入（舍入到零），至最接近的显着倍数，并返回一个十进制数。

FLOOR (<number>, <significance>) − Rounds a number down, toward zero, to the nearest multiple of significance and returns a decimal number.

Converting Real Numbers, Integers, or Dates to Strings

您可以使用以下 DAX 函数将实数、整数或日期转换成字符串−

You can use the following DAX functions for converting real numbers, integers, or dates to strings −

FIXED (<number>, [<decimals>], [<no_comma>]) − 舍入一个数字并返回结果的文本。小数点右边的位数为 2 或指定的小数位数。结果带有逗号或可选地不带逗号。

FIXED (<number>, [<decimals>], [<no_comma>]) − Rounds a number and returns the result as text. The number of digits to the right of the decimal point is 2 or the specified number of decimals. The result is with commas or optionally with no commas.

FORMAT (<value>, <format_string>) − 根据指定的格式将值转换为文本。

FORMAT (<value>, <format_string>) − Converts a value to text according to the specified format.

您已经了解了如何使用“Format”函数将日期转换为字符串。

You have already learnt about using Format function for converting dates to strings.

Converting Strings to Real Numbers or Dates

您可以使用以下 DAX 函数将字符串转换成实数或日期−

You can use the following DAX functions for converting strings to real numbers or dates −

VALUE (<text>) − 将表示数值的文本字符串转换为数字。

VALUE (<text>) − Converts a text string that represents a number to a number.

DATEVALUE (date_text) − 将文本格式的日期转换为日期时间格式的日期。

DATEVALUE (date_text) − Converts a date in the form of text to a date in datetime format.

TIMEVALUE (time_text) − 将文本格式的时间转换为日期时间格式的时间。

TIMEVALUE (time_text) − Converts a time in text format to a time in datetime format.

Conditional Values and Testing for Errors

您可以使用 DAX 函数测试数据中的值，并根据条件得到不同的值。例如，您可以测试年销售额，并根据结果，将经销商标注为首选或价值。

You can use DAX functions to test the values in the data that result in different values based on a condition. For e.g., you can test the yearly sales amount and based on the result, label resellers either as Preferred or Value.

您还可以使用 DAX 函数检查值范围或类型，以防止意外数据错误中断计算。

You can also use DAX functions for checking the range or the type of values, to prevent unexpected data errors from breaking calculations.

Creating a Value Based on a Condition

您可以使用嵌套的“IF”条件来测试值和有条件地生成新值。以下 DAX 函数适用于条件处理和条件值−

You can use nested IF conditions to test values and generate new values conditionally. Following DAX functions are useful for conditional processing and conditional values −

IF (<logical_test>,<value_if_true>, [<value_if_false>]) − 检查是否满足一个条件。如果条件为真，则返回一个值；如果条件为假，则返回另一个值。“Value_if_false”是可选的，如果省略且条件为假，则该函数返回 BLANK ()。

IF (<logical_test>,<value_if_true>, [<value_if_false>]) − Checks if a condition is met. Returns one value if the condition is TRUE and returns another value if the condition is FALSE. Value_if_false is optional, and if omitted and the condition is FALSE, the function returns BLANK ().

OR (<logical1>,<logical2>) − 检查其中一个参数是否为真来返回真。如果两个参数都为假，则该函数返回假。

OR (<logical1>,<logical2>) − Checks whether one of the arguments is TRUE to return TRUE. The function returns FALSE if both arguments are FALSE.

CONCATENATE (<text1>, <text2>) − 将两个文本字符串连接成一个文本字符串。连接项可以是文本、数字或表示为文本的布尔值或这些项的组合。如果该列包含适当的值，您还可以使用列引用。

CONCATENATE (<text1>, <text2>) − Joins two text strings into one text string. The joined items can be text, numbers, or Boolean values represented as text or a combination of those items. You can also use a column reference, if the column contains appropriate values.

Testing for Errors within a DAX Formula

在 DAX 中，您不能在一行的计算列中具有有效值，而在另一行中具有无效值。也就是说，如果计算列的任何部分发生错误，则整个列都会被标记为错误，您必须更正 DAX 公式以消除导致无效值的错误。

In DAX, you cannot have valid values in one row of a calculated column and invalid values in another row. That is, if there is an error in any part of a calculated column, the entire column is flagged with an error and you must correct the DAX formula to remove the errors that result in invalid values.

DAX 公式中的一些常见错误：

Some common errors in DAX formulas are −

  1. Division by zero.

  2. Argument to a function is blank while the expected argument is numeric value.

您可以结合使用逻辑函数和信息函数来测试错误，并始终返回有效值，以避免在计算列中返回错误。以下 DAX 函数可以帮助您解决此问题。

You can use a combination of logical and information functions to test for errors and always return valid values to avoid returning errors in a calculated column. Following DAX functions help you in this.

ISBLANK (<value>) - 检查值是否为空白，并返回 TRUE 或 FALSE。

ISBLANK (<value>) − Checks whether a value is blank and returns TRUE or FALSE.

IFERROR (value, value_if_error) - 如果第一个参数中的表达式导致错误，则返回 value_if_error。否则，返回表达式本身的值。

IFERROR (value, value_if_error) − Returns value_if_error if the expression in the first argument results in an error. Otherwise, returns the value of the expression itself.

表达式的返回值和 value_if_error 都必须具有相同的数据类型。否则，您将收到一条错误消息。

Both the return value of the expression and value_if_error must be of same data type. Otherwise, you will get an error.

Excel DAX - Using Time Intelligence

您已在“理解时间智能”一章中了解了 DAX 强大的功能时间智能。在此章节中，您将学习如何在各种场景中使用 DAX 时间智能函数。

You have learnt about the DAX powerful feature Time Intelligence in the chapter – Understanding Time Intelligence. In this chapter, you will learn how to use DAX time intelligence functions in various scenarios.

DAX 时间智能函数包括：

DAX time intelligence functions include −

  1. Functions that help you retrieve dates or date ranges from your data, which are used to calculate values across similar periods.

  2. Functions that work with standard date intervals, to allow you to compare values across months, years, or quarters.

  3. Functions that retrieve the first and last date of a specified period.

  4. Functions that help you work on the opening and closing balances.

Calculating Cumulative Sales

您可以使用 DAX 时间智能函数创建公式来计算累计销售额。以下 DAX 函数可用于计算期末和期初余额：

You can use DAX time intelligence functions to create formulas for calculating cumulative sales. The following DAX functions can be used to calculate closing and opening balances −

CLOSINGBALANCEMONTH (<expression>,<dates>, [<filter>]) - 在当前上下文中，该函数在该月份的最后一天对表达式求值。

CLOSINGBALANCEMONTH (<expression>,<dates>, [<filter>]) − Evaluates the expression at the last date of the month in the current context.

OPENINGBALANCEMONTH (<expression>,<dates>, [<filter>]) - 在当前上下文中，该函数在该月份的第一天对表达式求值。

OPENINGBALANCEMONTH (<expression>,<dates>, [<filter>]) − Evaluates the expression at the first date of the month in the current context.

CLOSINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) - 在当前上下文中，该函数在该季度的最后一天对表达式求值。

CLOSINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) − Evaluates the expression at the last date of the quarter in the current context.

OPENINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) - 在当前上下文中，该函数在该季度的第一对表达式求值。

OPENINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) − Evaluates the expression at the first date of the quarter, in the current context.

CLOSINGBALANCEYEAR (<expression>,<dates>, [<filter>], [<year_end_date>]) - 在当前上下文中，该函数在该年份的最后一天对表达式求值。

CLOSINGBALANCEYEAR (<expression>,<dates>, [<filter>], [<year_end_date>]) − Evaluates the expression at the last date of the year in the current context.

OPENINGBALANCEYEAR (<expression>, <dates>, <filter>], [<year_end_date>]) - 在当前上下文中，该函数在该年份的第一天对表达式求值。

OPENINGBALANCEYEAR (<expression>, <dates>, <filter>], [<year_end_date>]) − Evaluates the expression at the first date of the year in the current context.

您可以使用以下 DAX 函数针对某个特定时间点的产品清单创建以下计算的字段：

You can create the following calculated fields for the product inventory at a specified time by using the following DAX functions −

Month Start Inventory Value: = OPENINGBALANCEMONTH (
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)
Month End Inventory Value: = CLOSINGBALANCEMONTH (
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)
Quarter Start Inventory Value: = OPENINGBALANCEQUARTER (
   SUMX ProductInventory, (ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)
Quarter End Inventory Value: = CLOSINGBALANCEQUARTER (
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)
Year Start Inventory Value: = OPENINGBALANCEYEAR (
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)
Year End Inventory Value: = CLOSINGBALANCEYEAR (
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Comparing Values across Different Time Periods

DAX 支持的默认时间段为月、季度和年。

The default time periods supported by DAX are months, quarters, and years.

您可以使用以下 DAX 时间智能函数比较不同时间段内的总和。

You can use the following DAX time intelligence functions to compare the sums across different time periods.

  1. PREVIOUSMONTH (<dates>) − Returns a table that contains a column of all the dates from the previous month, based on the first date in the dates column, in the current context.

  2. PREVIOUSQUARTER (<dates>) − Returns a table that contains a column of all the dates from the previous quarter, based on the first date in the dates column, in the current context.

  3. PREVIOUSYEAR (<dates>, <year_end_date>]) − Returns a table that contains a column of all dates from the previous year, given the last date in the dates column, in the current context.

您可以使用 DAX 函数创建以下计算的字段，用于计算 West 区域在指定时间段内的销售总和，以进行比较：

You can create the following calculated fields for calculating sum of sales in the West region at the specified time periods for comparison, by using the DAX functions −

Previous Month Sales: = CALCULATE (
   SUM (WestSales[SalesAmount]), PREVIOUSMONTH (DateTime [DateKey])
)
Previous Quarter Sales: = CALCULATE (
   SUM (WestSales[SalesAmount]), PREVIOUSQUARTER (DateTime [DateKey])
)
Previous Year Sales: = CALCULATE (
   SUM (WestSales[SalesAmount]), PREVIOUSYEAR (DateTime [DateKey])
)

Comparing Values Across Parallel Time Periods

您可以使用 DAX 时间智能函数 PARALLELPERIOD 比较在与指定时间段平行的某个时间段内的总和。

You can use the DAX time intelligence function PARALLELPERIOD to compare the sums across a period parallel to the specified time period.

PARALLELPERIOD (<dates>, <number_of_intervals>, <interval>)

此 DAX 函数返回一个包含日期列的表，该日期列表示在当前上下文中与指定日期列中的日期并行的时期的日期，且日期向前或向后偏移了指定数量的间隔。

This DAX function returns a table that contains a column of dates representing a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward or backward in time.

可以使用以下计算字段计算西部地区的去年销售额 −

You can create the following calculated field for calculating the previous year’s sales in West region −

Previous Year Sales: = CALCULATE (
   SUM (West_Sales[SalesAmount]), PARALLELPERIOD (DateTime[DateKey],-1,year)
)

Calculating Running Totals

可以使用以下 DAX 时间智能函数计算 运行总计 或 运行合计。

You can use the following DAX time intelligence functions to calculate running totals or running sums.

  1. TOTALMTD (<expression>,<dates>, [<filter>]) − Evaluates the value of the expression for the month to date in the current context.

  2. TOTALQTD (<expression>,<dates>, <filter>]) − Evaluates the value of the expression for the dates in the quarter to date, in the current context.

  3. TOTALYTD (<expression>,<dates>, [<filter>], [<year_end_date>]) − Evaluates the year-to-date value of the expression in the current context.

可以使用 DAX 函数创建以下计算字段以计算在指定时间段内西部地区的销售额运行合计 −

You can create the following calculated fields for calculating running sum of sales in the West region at specified time periods, by using the DAX functions −

月份运行合计：= TOTALMTD (SUM (West_Sales[SalesAmount]), DateTime[DateKey])

Month Running Sum: = TOTALMTD (SUM (West_Sales[SalesAmount]), DateTime[DateKey])

季度运行合计：= TOTALQTD (SUM (WestSales[SalesAmount]), DateTime[DateKey])

Quarter Running Sum: = TOTALQTD (SUM (WestSales[SalesAmount]), DateTime[DateKey])

年份运行合计：= TOTALYTD (SUM (WestSales[SalesAmount]), DateTime[DateKey])

Year Running Sum: = TOTALYTD (SUM (WestSales[SalesAmount]), DateTime[DateKey])

Calculating a Value over a Custom Date Range

使用 DAX 时间智能函数可以检索一组自定义日期，你可将其用作 DAX 函数的输入，从而执行计算，以跨时间段创建自定义聚合。

You can use DAX time intelligence functions to retrieve a custom set of dates, which you can use as an input to a DAX function that performs calculations, to create custom aggregates across time periods.

DATESINPERIOD (<dates>, <start_date>, <number_of_intervals>, <interval>) − 返回一个表，其中包含一列从 start_date 开始并持续指定 number_of_intervals 个时间间隔的日期。

DATESINPERIOD (<dates>, <start_date>, <number_of_intervals>, <interval>) − Returns a table that contains a column of dates that begins with the start_date and continues for the specified number_of_intervals.

DATESBETWEEN (<dates>, <start_date>, ) − 返回一个表，其中包含一列从 start_date 开始并持续到 end_date 的日期。

DATESBETWEEN (<dates>, <start_date>, ) − Returns a table that contains a column of dates that begins with the start_date and continues until the end_date.

DATEADD (<dates>,<number_of_intervals>,<interval>) − 返回一个表，其中包含一列日期，从当前上下文的日期中向前或向后移动指定时间间隔数。

DATEADD (<dates>,<number_of_intervals>,<interval>) − Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context.

FIRSTDATE (<dates>) − 返回当前上下文中指定日期列中的第一个日期。

FIRSTDATE (<dates>) − Returns the first date in the current context for the specified column of dates.

LASTDATE (<dates>) − 返回当前上下文中指定日期列中的最后一个日期。

LASTDATE (<dates>) − Returns the last date in the current context for the specified column of dates.

通过使用 DAX 函数，可以创建以下用于计算指定日期范围内西区销售总和的 DAX 公式 −

You can create the following DAX formulas for calculating the sum of sales in the West region over a specified date range, by using the DAX functions −

  1. DAX Formula to calculate the sales for the 15 days prior to July 17, 2016.

CALCULATE (
   SUM (WestSales[SalesAmount]), DATESINPERIOD (DateTime[DateKey], DATE(2016,17,14), -15, day)
)

  1. DAX Formula to create a calculated field that calculates the first quarter 2016 sales.

= CALCULATE (
   SUM (WestSales[SalesAmount]),DATESBETWEEN (DateTime[DateKey], DATE (2016,1,1), DATE (2016,3,31))
)

  1. DAX Formula to create a calculated field that obtains the first date when a sale was made in the West region for the current context.

= FIRSTDATE (WestSales [SaleDateKey])

  1. DAX Formula to create a calculated field that obtains the last date when a sale was made in the West region for the current context.

= LASTDATE (WestSales [SaleDateKey])

  1. DAX Formula to calculate the dates that are one year before the dates in the current context.

= DATEADD (DateTime[DateKey],-1,year)

Excel DAX - Ranking and Comparing Values

如果你只想显示一列或数据透视表中前 n 个项目，则有以下两个选项 −

If you want to show only the top n number of items in a column or PivotTable, you have the following two options −

  1. You can select n number of top values in the PivotTable.

  2. You can create a DAX formula that dynamically ranks values and then uses the ranking values in a Slicer.

Applying a Filter to Show only the Top Few Items

若要选择 n 个排名前的值在数据透视表中显示，请执行以下操作 −

To select n number of top values for display in the PivotTable, do the following −

  1. Click the down arrow in the row labels heading in the PivotTable.

  2. Click the Value Filters in the dropdown list and then click Top 10.

applying a filter

出现“前 10 筛选器 (<column name>)”对话框。

Top 10 Filter (<column name>) dialog box appears.

  1. Under Show, select the following in the boxes from left to right. Top18 (The number of top values that you want to display. The default is 10.)Items.In the by box, select Medal Count.

medal count

  1. Click OK. The top 18 values will be displayed in the PivotTable.

Advantages and Disadvantages of Applying Filter

Advantages

  1. It is simple and easy to use.

  2. Suitable for tables with large number of rows.

Disadvantages

  1. The filter is solely for display purposes.

  2. If the data underlying the PivotTable changes, you must manually refresh the PivotTable to see the changes.

Creating a DAX Formula That Dynamically Ranks Values

可以使用包含已排序值的 DAX 公式创建计算列。然后，可以在 resulting 计算列上使用切片器来选择要显示的值。

You can create a calculated column using a DAX formula that contains the ranked values. You can then use a slicer on the resulting calculated column to select the values to be displayed.

可以根据行中某个给定值的较大值通过计算同个表格中值大于该值的行数来获得排序值。此方法返回以下内容

You can obtain a rank value for a given value in a row by counting the number of rows in the same table having a value larger than the one that is being compared. This method returns the following −

  1. A zero value for the highest value in the table.

  2. Equal values will have the same rank value. If n number of values are equal, the next value after the equal values will have a nonconsecutive rank value adding up the number n.

例如，如果你有一个带有销售数据的“Sales”表格，你可以创建一个计算列，按照销售金额值的排序如下创建

For example, if you have a table ‘Sales’ with sales data, you can create a calculated column with the ranks of the Sales Amount values as follows −

= COUNTROWS (FILTER (Sales,
   EARLIER (Sales [Sales Amount]) < Sales [Sales Amount])
) + 1

接下来，你可以在新的计算列上插入切片器并按排序选择性地显示值。

Next, you can insert a Slicer on the new calculated column and selectively display the values by ranks.

Advantages and Disadvantages of Dynamic Ranks

Advantages

  1. The ranking is done in the table and not on a PivotTable. Hence, can be used in any number of PivotTables.

  2. DAX formulas are calculated dynamically. Hence, you can always be sure that the ranking is correct even if the underlying data has changed.

  3. Since the DAX formula is used in a calculated column, you can use the ranking in a Slicer.

  4. Suitable for tables with large number of rows.

Disadvantages

因为 DAX 计算对于计算来说过于昂贵，所以此方法可能不适合行数较多的表格。

Since the DAX calculations are computationally expensive, this method might not be suitable for tables with large number of rows.