Power Pivot 中的 DAX 方案

本节提供演示在以下方案中使用 DAX 公式的示例的链接。

• 执行复杂计算
• 使用文本和日期
• 条件值和错误测试
• 使用时间智能
• 排名和比较值

本文内容

• 入门

• 方案：执行复杂计算

  • 为数据透视表创建自定义计算
  • 对公式应用筛选器
  • 有选择地删除筛选器以创建动态比率
  • 使用外部循环中的值

• 方案：使用文本和日期

  • 通过串联创建键列
  • 根据从文本日期中提取的日期部分Compose日期
  • 定义自定义日期或数字格式
  • 使用公式更改数据类型

• 方案：条件值和测试错误

  • 基于条件创建值
  • 测试公式中的错误

• 方案：使用时间智能

  • 计算累计销售额
  • 比较一段时间内的值
  • 计算自定义日期范围内的值

• 方案：排名和比较值

  • 仅显示数据透视表中的前 10 个项目
  • 使用公式动态订购项目

入门

访问 DAX 资源汇 Wiki，可在其中找到有关 DAX 的各类信息，包括行业领先专业人士和 Microsoft 提供的博客、示例、白皮书和视频。

方案：执行复杂计算

DAX 公式可以执行复杂的计算，这些计算涉及自定义聚合、筛选和条件值的使用。 本部分提供如何开始使用自定义计算的示例。

为数据透视表创建自定义计算

CALCULATE 和 CALCULATETABLE 是功能强大且灵活的函数，可用于定义计算字段。 这些函数允许您更改执行计算的上下文。 还可以自定义要执行的聚合或数学运算的类型。 有关示例，请参阅以下主题。

• 计算
• CALCULATETABLE

对公式应用筛选器

在大多数 DAX 函数将表用作参数的位置，通常可以通过使用 FILTER 函数而不是表名称，或通过指定筛选器表达式作为函数参数之一来传入筛选表。 以下主题提供了有关如何创建筛选器以及筛选器如何影响公式结果的示例。 有关详细信息，请参阅 在 DAX 公式中筛选数据。

FILTER 函数允许使用表达式指定筛选条件，而其他函数则专门用于筛选出空白值。

• 筛选器
• ALLNOBLANKROW
• FIRSTNONBLANK
• LASTNONBLANK

有选择地删除筛选器以创建动态比率

通过在公式中创建动态过滤器，可以轻松回答以下问题：

• 当前产品的销售额对当年总销售额的贡献有多大？
• 与其他部门相比，该部门对所有运营年度的总利润的贡献有多大？

在数据透视表中使用的公式可能受数据透视表上下文的影响，但可以通过添加或删除筛选器来选择性地更改上下文。 ALL 主题中的示例演示如何执行此操作。 要查找特定经销商的销售额与所有经销商的销售额之比，请创建一个度量值，以计算当前上下文的值除以 ALL 上下文的值。

ALLEXCEPT 主题提供了有关如何选择性地清除公式筛选器的示例。 这两个示例都介绍了结果如何根据数据透视表的设计而变化。

• ALL
• ALLEXCEPT

有关如何计算比率和百分比的其他示例，请参阅以下主题：

• ISBLANK
• ISERROR

使用外部循环中的值

除了在计算中使用当前上下文中的值外，DAX 还可以在创建一组相关计算时使用上一个循环中的值。 以下主题将提供有关如何构建引用外部循环中值的公式的演练。 EARLIER 函数支持最多两个级别的嵌套循环。

• EARLIER 函数

若要了解有关行上下文和相关表以及如何在公式中使用此概念的详细信息，请参阅 DAX 公式中的上下文。

方案：使用文本和日期

本部分提供指向 DAX 参考主题的链接，其中包含涉及处理文本、提取和撰写日期和时间值或者基于条件创建值的常见方案示例。

通过串联创建键列

Power Pivot 不允许使用组合键;因此，如果数据源中有复合键，则可能需要将它们合并到单个键列中。 以下主题提供如何基于组合键创建计算列的一个示例。

• CONCATENATE 函数

根据从文本日期中提取的日期部分Compose日期

Power Pivot 使用 SQL Server 日期/时间数据类型来处理日期;因此，如果外部数据包含的日期格式不同（例如，如果日期采用 Power Pivot 数据引擎无法识别的区域日期格式编写，或者数据使用整数代理键），则可能需要使用 DAX 公式提取日期部分，然后将这些部分组合为有效的日期/时间表示形式。

例如，如果有一列日期已表示为整数，然后又导入为文本字符串，则可以使用以下公式将该字符串转换为日期/时间值：

=DATE (RIGHT ([Value1]，4) ，LEFT ([Value1]，2) ，MID ([Value1]，2) )

Value1	结果
01032009	1/3/2009
12132008	12/13/2008
06252007	6/25/2007

以下主题提供有关用于提取和撰写日期的函数的详细信息。

• DATEVALUE 函数
• TIMEVALUE 函数
• DATE 函数

定义自定义日期或数字格式

如果数据包含的日期或数字未以某一种标准 Windows 文本格式表示，则可以定义自定义格式，以确保正确处理这些值。 将值转换为字符串或从字符串转换时使用这些格式。 以下主题还提供了可用于处理日期和数字的预定义格式的详细列表。

• FORMAT 函数的预定义数字格式
• FORMAT 函数的自定义数字格式
• FORMAT 函数的预定义日期和时间格式
• FORMAT 函数的自定义日期和时间格式

使用公式更改数据类型

在 Power Pivot 中，输出的数据类型由源列决定，不能明确指定结果的数据类型，因为最佳数据类型由 Power Pivot 决定。 但是，可以使用 Power Pivot 执行的隐式数据类型转换来操作输出数据类型。 

• 要将日期或数字字符串转换为数字，请乘以 1.0。 例如，以下公式计算当前日期减去 3 天，然后输出相应的整数值。
  = (TODAY () -3) *1.0
• 要将日期、数字或货币值转换为字符串，请将该值与空字符串连接起来。 例如，以下公式以字符串形式返回今天的日期。
  =“”& TODAY ()

以下函数还可用于确保返回特定数据类型：

将实数转换为整数

• ROUND 函数
• CEILING 函数
• FLOOR 函数
  将实数、整数或日期转换为字符串
• FIXED 函数
• FORMAT 函数
  将字符串转换为实数或日期
• VALUE 函数
• DATEVALUE 函数
• TIMEVALUE 函数

方案：条件值和测试错误

与 Excel 一样，DAX 也具有函数，可用于测试数据中的值并根据条件返回不同的值。 例如，可以创建一个计算列，根据年销售额将经销商标记为 “首选” 或 “值 ”。 测试值的函数还可用于检查值的范围或类型，以防止意外的数据错误中断计算。

基于条件创建值

您可以使用嵌套的 IF 条件来测试值并有条件地生成新值。 以下主题包含条件处理和条件值的一些简单示例：

• IF
• OR 函数
• CONCATENATE 函数

测试公式中的错误

与 Excel 不同的是，计算列的一行不能包含有效值，而在另一行中保留无效值。 也就是说，如果 Power Pivot 列的任何部分存在错误，则整列都将标记为错误，因此必须始终更正导致无效值的公式错误。

例如，如果创建除以零的公式，则可能会得到无穷大结果或错误值。 如果函数期望数值时遇到空白值，某些公式也会失败。 在开发数据模型时，最好允许出现错误，以便您可以单击该消息并解决问题。 但是，在发布工作簿时，应合并错误处理，以防止意外值导致计算失败。

若要避免在计算列中返回错误，请结合使用 logic 函数和 information 函数来测试错误并始终返回有效值。 以下主题提供了有关如何在 DAX 中执行此操作的一些简单示例：

• ISBLANK 函数
• IFERROR 函数

方案：使用时间智能

DAX 时间智能函数包括有助于从数据中检索日期或日期范围的函数。 然后，可以使用这些日期或日期范围计算相似时期的值。 时间智能函数还包括适用于标准日期间隔的函数，以允许您比较月、年或季度的值。 还可以创建一个公式来比较指定时间段的第一个和最后一个日期的值。

有关所有时间智能函数的列表，请参阅时间 智能函数 (DAX) 。 有关如何在 PowerPivot 分析中有效使用日期和时间的提示，请参阅 Power Pivot 中的日期。

计算累计销售额

以下主题包含如何计算期末余额和期初余额的示例。 这些示例允许您跨不同间隔（例如天、月、季度或年）创建运行余额。

• CLOSINGBALANCEMONTH 函数， CLOSINGBALANCEQUARTER 函数， CLOSINGBALANCEYEAR 函数
• OPENINGBALANCEMONTH 函数， OPENINGBALANCEQUARTER 函数， OPENINGBALANCEYEAR 函数

比较一段时间内的值

以下主题包含有关如何比较不同时间段的总和的示例。 DAX 支持的默认时间段为月、季和年。

• PREVIOUSMONTH 函数、PREVIOUSQUARTER、PREVIOUSYEAR 函数
• TOTALMTD 函数、 TOTALQTD 函数、 TOTALYTD 函数
• PARALLELPERIOD 函数

计算自定义日期范围内的值

有关如何检索自定义日期范围（例如促销开始后的前 15 天）的示例，请参阅以下主题。

• DATESINPERIOD 函数
• DATESBETWEEN 函数
• DATEADD 函数
• FIRSTDATE 函数
• LASTDATE 函数

如果使用时间智能函数检索自定义日期集，则可以使用该日期集作为执行计算的函数的输入，以创建跨时间段的自定义聚合。 有关如何执行此操作的示例，请参阅以下主题：

• PARALLELPERIOD 函数

  注意

  如果您不需要指定自定义日期范围，但使用的是标准会计单位（如月、季度或年），我们建议您使用为此目的设计的时间智能函数（如 TOTALQTD、TOTALMTD、TOTALQTD 等）执行计算。

方案：排名和比较值

若要仅显示列或数据透视表中前 n 个项目，可以使用多个选项：

• 可使用 Excel 中的功能创建热门筛选器。 还可以在数据透视表中选择多个上限或底部值。 本节的第一部分介绍如何筛选数据透视表中的前 10 个项目。 有关详细信息，请参阅 Excel 文档。
• 可以创建对值进行动态排名的公式，然后按排名值进行筛选，或将排名值用作切片器。 本部分的第二部分介绍如何创建此公式，然后在切片器中使用该排名。

每种方法都有优点和缺点。

• Excel 顶部筛选器易于使用，但该筛选器仅用于显示目的。 如果数据透视表的基础数据发生更改，则必须手动刷新数据透视表才能查看更改。 如果需要动态处理排名，可以使用 DAX 创建一个公式，将值与列中的其他值进行比较。
• DAX 公式更强大;此外，通过将排名值添加到切片器，您只需单击切片器即可更改显示的顶级值的数量。 但是，计算成本高昂，此方法可能不适合具有许多行的表。

仅显示数据透视表中的前 10 个项目

显示数据透视表中的上限或下限值 在数据透视表中，单击 “行标签 ”标题中的向下箭头。 选择值筛选器前 10。> 在 “前 10 个筛选器 <列名”> 对话框中，选择要对其进行排名的列和值数，如下所示： 选择 “顶部 ”可查看具有最高值的单元格，选择“ 底部 ”可查看具有最低值的单元格。 键入要查看的上限或底部值的数量。 默认值为 10。 选择希望值显示方式： 名称描述项目 选择此选项可筛选数据透视表，以仅按值显示排名靠前或靠后的项列表。百分比选择此选项可筛选数据透视表，以仅显示加起来达到指定百分比的项目。总和 选择此选项可显示顶部或底部项的值之和。 选择包含要排名的值的列。 单击“确定”。

使用公式动态订购项目

以下主题包含一个示例，介绍如何使用 DAX 创建存储在计算列中的排名。 由于 DAX 公式是动态计算的，因此即使基础数据已更改，您也始终可以确保排名正确无误。 此外，由于该公式用于计算列，因此可以使用切片器中的排名，然后选择前 5 个、前 10 个甚至前 100 个值。

• EARLIER 函数。