Excel DAX - 公式

上一个
下一个

DAX 是一种用于在 Power Pivot 表中创建自定义计算的公式语言。您可以使用专为处理关系数据并在 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 ()

了解IntelliSense功能

DAX 提供了 IntelliSense 功能，使您能够快速准确地编写 DAX 公式。使用此功能，您无需完全键入表、列和函数名称，而是在编写 DAX 公式时从下拉列表中选择相关名称。

  • 开始键入函数名称的前几个字母。自动完成将显示一个包含名称以这些字母开头的可用函数的列表。

  • 将指针放在任何函数名称上。将显示 IntelliSense 工具提示，提供函数的用途。

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

  • 键入您想要的表名的第一个字母。自动完成将显示一个包含名称以该字母开头的可用表和列的列表。

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

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

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

强烈建议使用此方便的 IntelliSense 功能。

在哪里使用DAX公式？

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

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

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

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

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

相同的公式在计算列或计算字段中使用时，其行为可能会有所不同。

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

  • 但是，在计算字段中，结果的计算在很大程度上取决于上下文。也就是说，透视表的结构以及行和列标题的选择会影响计算中使用的值。

了解DAX中的上下文概念以编写DAX公式非常重要。在您开始使用 DAX 的初期，这可能有点困难，但是一旦您掌握了它，您就可以编写有效且满足复杂动态数据分析需求的 DAX 公式。有关详细信息，请参阅章节 - DAX 上下文。

创建DAX公式

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

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

  • 键入等号。

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

    • 键入函数或表名的第一个字母，并从下拉列表中选择完整名称。

    • 如果您选择了函数名称，请键入括号“（”。

    • 如果您选择了表名，请键入方括号“[”。键入列名的第一个字母，并从下拉列表中选择完整名称。

    • 用“]”关闭列名，用“)”关闭函数名。

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

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

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

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公式。

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

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

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

使用公式栏创建DAX公式

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

  • 要输入表名，请开始键入表名。公式自动完成将提供一个包含以这些字母开头的有效表名的下拉列表。您可以从一个字母开始，并键入更多字母以缩小列表范围（如果需要）。

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

使用自动完成的提示

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

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

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

  • 不会自动添加函数的结束括号。您需要自己添加。

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

了解插入函数功能

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

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

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

单击fx按钮时，会显示“插入函数”对话框。“插入函数”对话框是查找与 DAX 公式相关的 DAX 函数的最简单方法。

“插入函数”对话框可帮助您按类别选择函数，并提供每个函数的简短说明。

Understanding Insert Function

在 DAX 公式中使用插入函数

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

Medal Count: = COUNTA (]Medal])

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

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

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

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

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

Using Insert Function

您可以看到，已显示选定的 DAX 函数语法和函数说明。这使您可以确保它是您要插入的函数。

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

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

  • 键入 M。下拉列表中显示的名称将限制为以“M”开头的名称。

  • 单击“Medal”。

Click Medal

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

  • 按 Enter 键。您已完成。您也可以使用相同的过程来创建计算列。您还可以按照相同的步骤在 Excel 窗口的“计算字段”对话框中使用“插入函数”功能插入函数。

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

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

在 DAX 公式中使用多个函数

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

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

  • 创建公式将非常困难。
  • 如果公式存在错误，则调试将非常困难。
  • 公式评估速度不会很快。

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

使用标准聚合创建 DAX 公式

执行数据分析时，您将对聚合数据执行计算。DAX 中有几个聚合函数（如 SUM、COUNT、MIN、MAX、DISTINCTCOUNT 等）可用于 DAX 公式。

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

  • 单击 Power Pivot 窗口中的“结果”选项卡。将显示“结果”表。
  • 单击“Medal”列。将选择整列 -“Medal”。
  • 单击功能区上的“开始”选项卡。
  • 单击“计算”组中“自动求和”旁边的向下箭头。
Creating a DAX Formula Using Standard Aggregations
  • 在下拉列表中单击“COUNT”。
Click Count

您可以看到，计算字段“Medal 的计数”出现在列“Medal”下方的计算区域中。DAX 公式也显示在公式栏中：

Count of Medal: = COUNTA([Medal])

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

注意 - 要使用“自动求和”功能，您需要单击功能区上“自动求和”旁边的向下箭头。如果您改为单击“自动求和”本身，则会得到：

Sum of Medal: = SUM([Medal])

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

Error

您可以参考“DAX 错误参考”一章以了解有关 DAX 错误的详细信息。

DAX 公式和关系模型

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

在两个表之间创建关系时，您需要确保用作键的两个列的值至少在大多数行（如果不是全部）上匹配。在 Power Pivot 数据模型中，键列中可能存在不匹配的值，并且仍然可以创建关系，因为 Power Pivot 不强制实施引用完整性（有关详细信息，请参阅下一部分）。但是，键列中存在空白或不匹配的值可能会影响 DAX 公式的结果和数据透视表的显示。

引用完整性

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

如果实施引用完整性，则可以防止以下问题：

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

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

  • 删除主表中的行时，相关表的行中有匹配的数据值。

打印页面
上一页
下一页
广告