HTML CSS JAVASCRIPT PYTHON PYTHON2 JAVA C C++ C# SQL Excel Linux AI BOOTSTRAP PHP 教程库 参考手册 技术文章 测验 练习 HOWTO FAQ
❮ 上一节 下一节 ❯

Excel DAX - 公式

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

DAX 公式 与 Excel 公式非常相似。 要创建 DAX 公式，您可以键入等号，后跟函数名称或表达式以及任何必需的值或参数。

DAX 函数与 DAX 公式

DAX 公式可以包含 DAX 函数并利用它们的用法。 这就是 DAX 公式往往在重要方面不同于 DAX 函数的地方。

  • DAX 函数始终引用完整的列或表。 如果您只想使用表或列中的特定值，您可以向公式添加过滤器。

  • 如果您想逐行自定义计算，Power Pivot 提供的函数可让您使用当前行值或相关值来执行因上下文而异的计算。

  • DAX 包含一种函数，它返回一个表作为结果，而不是单个值。 这些函数可用于为其他函数提供输入，从而计算整个表或列的值。

  • 一些 DAX 函数提供时间智能，让您可以使用有意义的日期范围创建计算并比较平行时期的结果。

了解 DAX 公式语法

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

  • 每个公式必须以等号开头。

  • 在等号右侧，您可以键入或选择一个函数名称，或者键入一个表达式。 表达式可以包含由 DAX 运算符连接的表名和列名。

以下是一些有效的 DAX 公式 −

  • [column_Cost] + [column_Tax]
  • = Today ()

了解智能感知功能

DAX 提供了 IntelliSense(智能感知) 功能，可让您快速正确地编写 DAX 公式。 使用此功能，您无需完整键入表、列和函数名称，而是在编写 DAX 公式时从下拉列表中选择相关名称。

  • 开始键入函数名称的前几个字母。 AutoComplete 显示可用函数列表，名称以这些字母开头。

  • 将指针放在任何函数名称上。 将显示 IntelliSense 工具提示，让您使用该功能。

  • 单击函数名称。 函数名称显示在公式栏中并显示语法，这将指导您选择参数。

  • 键入所需表名的第一个字母。 自动完成显示可用表和列的列表，名称以该字母开头。

  • 按 TAB 键或单击名称以将自动完成列表中的项目添加到公式中。

  • 单击Fx 按钮以显示可用功能列表。 要从下拉列表中选择一个函数，请使用箭头键突出显示该项目，然后单击"确定"将该函数添加到公式中。

  • 通过从可能的表和列的下拉列表中选择参数或键入所需的值来为函数提供参数。

强烈推荐使用这个方便的智能感知功能。

在哪里使用 DAX 公式?

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

  • 您可以在计算列中使用 DAX 公式，方法是添加一个列，然后在公式栏中键入一个表达式。 您在 PowerPivot 窗口中创建这些公式。

  • 您可以在计算字段中使用 DAX 公式。 您创建这些公式 −

    • 在"计算字段"对话框的 Excel 窗口中，或

    • 在表格计算区域的 Power Pivot 窗口中。

同一公式的行为可能会有所不同，具体取决于该公式是用在计算列还是计算字段中。

  • 在计算列中，公式始终应用于整个表中列中的每一行。 根据行上下文，该值可能会发生变化。

  • 然而，在计算领域中，结果的计算在很大程度上取决于上下文。 也就是说，数据透视表的设计以及行标题和列标题的选择会影响计算中使用的值。

了解 DAX 中上下文的概念对于编写 DAX 公式很重要。 这在您开始 DAX 之旅时可能有点困难，但一旦您掌握了它，您就可以编写复杂和动态数据分析所需的有效 DAX 公式。 有关详细信息，请参阅章节 - DAX 上下文。

创建 DAX 公式

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

要创建 DAX 公式，请使用以下步骤 −

  • 键入等号。

  • 在等号右侧，键入以下内容 −

    • 键入函数或表名称的首字母，然后从下拉列表中选择完整名称。

    • 如果您选择了函数名称，请键入括号"("。

    • 如果您选择了表格名称，请键入括号"["。 输入列名称的第一个字母，然后从下拉列表中选择完整的名称。

    • 列名以"]"结束，函数名以")"结束。

    • 在表达式之间键入 DAX 运算符或键入","来分隔函数参数。

    • 重复步骤 1 - 5 直到 DAX 公式完成。

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

SUM ([East_Sales[Amount])

正如在 DAX 语法一章中所讨论的那样，推荐的做法是在每次引用任何列名时都使用表名和列名。 这被称为 – "完全合格的名称"。

DAX 公式可能会有所不同，具体取决于它是针对计算字段还是计算列。 有关详细信息，请参阅以下部分。

为计算列创建 DAX 公式

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

  • 单击要在其中添加计算列的表的选项卡。
  • 单击功能区上的"设计"选项卡。
  • 点击添加。
  • 在公式栏中为计算列键入 DAX 公式。
= DIVIDE (East_Sales[Amount], East_Sales[Units])

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

  • 将一行中 Amount 列中的值除以同一行中 Units 列中的值。

  • 将结果放在同一行中新添加的列中。

  • 迭代地重复步骤 1 和 2，直到完成表中的所有行。

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

  • 如您所见，计算列也需要计算和存储空间。 因此，仅在必要时才使用计算列。 尽可能使用计算字段。

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

为计算字段创建 DAX 公式

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

  • 要在 Excel 窗口中为计算字段创建 DAX 公式，请使用"计算字段"对话框。

  • 要在 Power Pivot 窗口中为计算字段创建 DAX 公式，请单击相关表计算区域中的一个单元格。 使用 CalculatedFieldName:= 开始 DAX 公式。

例如，东部总销售额:=SUM ([East_Sales[Amount])

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

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

使用公式栏创建 DAX 公式

Power Pivot 窗口还有一个类似于 Excel 窗口公式栏的公式栏。 公式栏使用自动完成功能使创建和编辑公式变得更加容易，从而最大限度地减少语法错误。

  • 要输入表名，请开始输入表名。 公式自动完成提供了一个下拉列表，其中包含以这些字母开头的有效表格名称。 您可以从一个字母开始，然后根据需要键入更多字母以缩小列表范围。

  • 要输入列名，您可以从所选表的列名列表中选择它。 在表名右侧键入括号"["，然后从所选表的列列表中选择列。

使用自动完成的提示

以下是使用自动完成的一些提示 −

  • 您可以在 DAX 公式中嵌套函数和公式。 在这种情况下，您可以在具有嵌套函数的现有公式中间使用公式自动完成。 插入点之前的文本用于显示下拉列表中的值，插入点之后的所有文本保持不变。

  • 您为常量创建的定义名称不会显示在自动完成下拉列表中，但您仍然可以键入它们。

  • 函数的右括号不会自动添加。 你需要自己来完成。

  • 您必须确保每个函数在句法上都是正确的。

了解插入函数特性

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

  • Power Pivot 窗口中的"插入函数"按钮位于公式栏的左侧。

  • Excel 窗口中的"插入函数"按钮位于公式右侧的"计算字段"对话框中。

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

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

了解插入函数

在 DAX 公式中使用插入函数

假设您要创建以下计算字段 −

Medal Count: = COUNTA (]Medal])

您可以使用以下步骤使用插入函数对话框 −

  • 单击结果表的计算区域。
  • 在公式栏中输入以下内容 −
Medal Count: =
  • 单击"插入函数"按钮 (fx)。

出现插入函数对话框。

  • 在选择类别框中选择统计，如下面的屏幕截图所示。

  • 在选择函数框中选择 COUNTA，如下面的屏幕截图所示。

使用插入函数

如您所见，显示了所选的 DAX 函数语法和函数描述。 这使您能够确保它是您要插入的函数。

  • 单击"确定"。 Medal Count:=COUNTA( 出现在公式栏中，并且还会出现一个显示函数语法的工具提示。

  • 输入 [. 这意味着您将要键入一个列名。 当前表中所有列的名称和计算字段的名称将显示在下拉列表中。 您可以使用 IntelliSense 来完成公式。

  • 键入 M。下拉列表中显示的名称将仅限于以"M"开头的名称。

  • 点击 Medal。

点击 Medal

  • 双击 Medal。 Medal Count: = COUNTA([Medal] 将显示在公式栏中。关闭括号。

  • 按回车键。 大功告成。 您也可以使用相同的过程来创建计算列。 您也可以按照相同的步骤使用"插入函数"功能在 Excel 窗口的"计算字段"对话框中插入函数。

  • 单击公式右侧的插入函数 (fx) 按钮。

出现插入函数对话框。 其余步骤同上。

在 DAX 公式中使用多个函数

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

如果一个DAX公式有很多嵌套函数，它有以下缺点 −

  • 创建公式会非常困难。
  • 如果公式有误，调试起来会非常困难。
  • 公式计算速度不会很快。

在这种情况下，您可以将公式拆分为更小的易于管理的公式，然后逐步构建大公式。

使用标准聚合创建 DAX 公式

执行数据分析时，您将对聚合数据执行计算。 您可以在 DAX 公式中使用多种 DAX 聚合函数，例如 SUM、COUNT、MIN、MAX、DISTINCTCOUNT 等。

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

  • 单击 Power Pivot 窗口中的"结果"选项卡。 将显示结果表。
  • 单击 Medal 列。 整列 - Medal 将被选中。
  • 单击功能区上的主页选项卡。
  • 单击"计算"组中"自动求和"旁边的向下箭头。
使用标准聚合创建 DAX 公式
  • 在下拉列表中点击 COUNT。
点击次数

如您所见，计算字段 Count of Medal 出现在列 – Medal 下方的计算区域中。 DAX 公式也出现在公式栏中 −

Count of Medal: = COUNTA([Medal])

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

注意事项——要使用自动求和功能，您需要单击功能区上自动求和旁边的向下箭头。 如果你点击自动求和本身，你会得到 −

Sum of Medal: = SUM([Medal])

并标记错误，因为 Medal 不是数字数据列，列中的文本无法转换为数字。

错误

有关 DAX 错误的详细信息，请参阅DAX 错误参考章节。

DAX 公式和关系模型

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

当您在两个表之间创建关系时，您应该确保用作键的两个列具有匹配的值，至少对于大多数行，如果不是完全匹配的话。 在 Power Pivot 数据模型中，键列中可能有不匹配的值，但仍会创建关系，因为 Power Pivot 不强制执行参照完整性（有关详细信息，请参阅下一节）。 但是，键列中存在空白或不匹配的值可能会影响 DAX 公式的结果和数据透视表的外观。

参照完整性

建立参照完整性涉及建立一组规则，以在您输入或删除数据时保留表之间已定义的关系。 如果您不专门确保这一点，因为 Power Pivot 不强制执行，您可能无法使用在进行数据更改之前创建的 DAX 公式获得正确的结果。

如果你强制引用完整性，可以防止以下陷阱 −

  • 在主表中没有关联行（即在键列中具有匹配值）时向相关表添加行。

  • 更改主表中的数据会导致相关表中出现孤立行（即键列中的数据值在主表键列中没有匹配值的行）。

  • 当相关表的行中存在匹配的数据值时，从主表中删除行。

❮ 上一节 下一节 ❯