Excel Dax 简明教程

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.