Excel Dax 简明教程

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