Excel DAX – 公式
Excel DAX – 公式
DAX 是一种用于在 Power 数据透视表中创建自定义计算的公式语言。您可以使用旨在处理关系数据并在 DAX 公式中执行动态聚合的 DAX 函数。
DAX 公式与 Excel 公式非常相似。要创建 DAX 公式，请键入等号，后跟函数名称或表达式以及任何必需的值或参数。
DAX 函数与 DAX 公式
DAX 公式可以包含 DAX 函数并利用它们的用法。这就是 DAX 公式在重要方面与 DAX 函数不同的地方。
-
DAX 函数始终引用完整的列或表。如果您只想使用表或列中的特定值，您可以向公式添加过滤器。
-
如果要逐行自定义计算，Power Pivot 提供的函数可让您使用当前行值或相关值来执行因上下文而异的计算。
-
DAX 包含一种函数，它返回一个表作为结果，而不是一个单一的值。这些函数可用于为其他函数提供输入，从而计算整个表或列的值。
-
某些 DAX 函数提供时间智能，可让您使用有意义的日期范围创建计算，并比较并行期间的结果。
了解 DAX 公式语法
每个 DAX 公式都具有以下语法 –
-
每个公式必须以等号开头。
-
在等号的右侧，您可以键入或选择函数名称，或者键入表达式。该表达式可以包含由 DAX 运算符连接的表名和列名。
以下是一些有效的 DAX 公式 –
- [column_Cost] + [column_Tax]
- = 今天 ()
了解 IntelliSense 功能
DAX 提供了 IntelliSense 功能，可让您快速正确地编写 DAX 公式。使用此功能，您无需完全键入表、列和函数名称，而是在编写 DAX 公式时从下拉列表中选择相关名称。
-
开始键入函数名称的前几个字母。AutoComplete 显示可用函数的列表，名称以这些字母开头。
-
将指针放在任何函数名称上。将显示 IntelliSense 工具提示，让您可以使用该功能。
-
单击函数名称。函数名称出现在公式栏中并显示语法，它将指导您选择参数。
-
键入所需的表名的第一个字母。AutoComplete 显示名称以该字母开头的可用表和列的列表。
-
按 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 是表的名称。金额是表中的一列。
SUM ([East_Sales[Amount])
正如在 DAX 语法一章中已经讨论过的，建议在每次引用任何列名时使用表名和列名。这被称为 – “完全限定名称”。
DAX 公式可以根据它是用于计算字段还是计算列而有所不同。有关详细信息，请参阅以下部分。
为计算列创建 DAX 公式
您可以在 Power Pivot 窗口中为计算列创建 DAX 公式。
- 单击要在其中添加计算列的表的选项卡。
- 单击功能区上的设计选项卡。
- 单击添加。
- 在公式栏中键入计算列的 DAX 公式。
= DIVIDE (East_Sales[Amount], East_Sales[Units])
此 DAX 公式对 East_Sales 表中的每一行执行以下操作 –
-
将行的金额列中的值除以同一行中单位列中的值。
-
将结果放在同一行中新添加的列中。
-
迭代地重复步骤 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 窗口公式栏的公式栏。公式栏使创建和编辑公式变得更加容易，使用自动完成功能以最大程度地减少语法错误。
-
要输入表格名称，请开始键入表格名称。公式自动完成提供了一个下拉列表，其中包含以这些字母开头的有效表名。如果需要，您可以从一个字母开始，然后键入更多字母以缩小列表的范围。
-
要输入列名，您可以从所选表的列名列表中选择它。在表名右侧键入方括号“[”，然后从所选表的列列表中选择该列。
使用自动完成的提示
以下是使用 AutoComplete 的一些提示 –
-
您可以在 DAX 公式中嵌套函数和公式。在这种情况下，您可以在具有嵌套函数的现有公式中间使用公式自动完成。插入点之前的文本用于显示下拉列表中的值，插入点之后的所有文本保持不变。
-
您为常量创建的定义名称不会显示在 AutoComplete 下拉列表中，但您仍然可以键入它们。
-
函数的右括号不会自动添加。你需要自己做。
-
您必须确保每个函数在语法上都是正确的。
了解插入功能特征
您可以在 Power Pivot 窗口和 Excel 窗口中找到标记为fx的“插入函数”按钮。
-
Power Pivot 窗口中的“插入功能”按钮位于公式栏的左侧。
-
Excel 窗口中的“插入函数”按钮位于“公式”右侧的“计算字段”对话框中。
当您单击fx按钮时，会出现插入函数对话框。“插入函数”对话框是查找与 DAX 公式相关的 DAX 函数的最简单方法。
“插入函数”对话框可帮助您按类别选择函数并提供每个函数的简短说明。
在 DAX 公式中使用插入函数
假设您要创建以下计算字段 –
Medal Count: = COUNTA (]Medal])
您可以使用以下步骤使用插入函数对话框 –
- 单击结果表的计算区域。
- 在公式栏中键入以下内容 –
Medal Count: =
- 单击插入函数按钮 ( fx )。
出现插入函数对话框。
-
在“选择类别”框中选择“统计”，如下面的屏幕截图所示。
-
在 Select a function 框中选择 COUNTA，如下面的屏幕截图所示。
如您所见，将显示选定的 DAX 函数语法和函数描述。这使您能够确保它是您要插入的函数。
-
单击确定。Medal Count:=COUNTA( 出现在公式栏中，并且还会出现显示函数语法的工具提示。
-
类型 [。这意味着您将要键入列名。当前表中所有列和计算字段的名称将显示在下拉列表中。您可以使用 IntelliSense 来完成公式。
-
键入 M。下拉列表中显示的名称将仅限于以“M”开头的名称。
-
点击勋章。
-
双击勋章。Medal Count: = COUNTA([Medal] 将显示在公式栏中。关闭括号。
-
按 Enter。你完成了。您也可以使用相同的过程来创建计算列。您还可以按照相同的步骤使用插入函数功能在 Excel 窗口的计算字段对话框中插入函数。
-
单击公式右侧的插入函数 ( fx ) 按钮。
出现插入函数对话框。其余步骤与上述相同。
在 DAX 公式中使用多个函数
DAX 公式最多可以包含 64 个嵌套函数。但是，DAX 公式不太可能包含如此多的嵌套函数。
如果 DAX 公式具有许多嵌套函数，则它具有以下缺点 –
- 该公式将很难创建。
- 如果公式有错误，将很难调试。
- 公式评估不会很快。
在这种情况下，您可以将公式拆分为较小的可管理公式并逐步构建大公式。
使用标准聚合创建 DAX 公式
执行数据分析时，您将对聚合数据执行计算。您可以在 DAX 公式中使用多种 DAX 聚合函数，例如 SUM、COUNT、MIN、MAX、DISTINCTCOUNT 等。
您可以使用 Power Pivot 窗口中的 AutoSum 功能使用标准聚合自动创建公式。
- 单击 Power Pivot 窗口中的结果选项卡。将显示结果表。
- 单击奖牌列。将选择整个列 – 奖牌。
- 单击功能区上的“主页”选项卡。
- 单击计算组中 AutoSum 旁边的向下箭头。
- 单击下拉列表中的 COUNT。
如您所见，计算的字段Count of Medal 出现在列- Medal 下方的计算区域中。DAX 公式也出现在公式栏中 –
Count of Medal: = COUNTA([Medal])
AutoSum 功能已经为您完成了工作 – 创建了用于数据聚合的计算字段。此外，AutoSum 采用了 DAX 函数 COUNT 的适当变体，即 COUNTA（DAX 具有 COUNT、COUNTA、COUNTAX 函数）。
注意事项 – 要使用 AutoSum 功能，您需要单击功能区上 AutoSum 旁边的向下箭头。如果您点击 AutoSum 本身，您将获得 –
Sum of Medal: = SUM([Medal])
错误被标记为 Medal 不是数字数据列，并且该列中的文本无法转换为数字。
有关 DAX 错误的详细信息，请参阅“ DAX 错误参考”一章。
DAX 公式和关系模型
如您所知，在 Power Pivot 的数据模型中，您可以处理多个数据表并通过定义关系连接这些表。这将使您能够创建有趣的 DAX 公式，这些公式使用相关表之间的列的相关性进行计算。
当您在两个表之间创建关系时，您需要确保用作键的两列具有匹配的值，至少对于大多数行（如果不是完全匹配）。在 Power Pivot 数据模型中，键列中可能存在不匹配的值并仍然创建关系，因为 Power Pivot 不强制执行参照完整性（有关详细信息，请参阅下一节）。但是，键列中存在空白或不匹配的值可能会影响 DAX 公式的结果和数据透视表的外观。
参照完整性
建立参照完整性涉及构建一组规则，以在您输入或删除数据时保留表之间定义的关系。如果您没有专门确保这一点，因为 Power Pivot 不会强制执行它，您可能无法使用在数据更改之前创建的 DAX 公式获得正确的结果。
如果您强制执行参照完整性，则可以防止以下陷阱 –
-
当主表中没有关联行（即键列中有匹配值）时，将行添加到相关表中。
-
更改主表中的数据会导致相关表中的孤立行（即，键列中的数据值在主表键列中没有匹配值的行）。
-
当相关表的行中有匹配的数据值时，从主表中删除行。
