Excel Dax 简明教程

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.