管理约会、计划和预算 - 使用 Microsoft 365 轻松完成

Power Pivot 中的 DAX 方案

本部分提供示例链接,这些示例演示了在下列方案中使用 DAX 公式的情况。

  • 执行复杂计算

  • 处理文本和日期

  • 条件值和测试错误

  • 使用时间智能

  • 排名和比较值

本文内容

入门

方案:执行复杂计算

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

将筛选器应用于公式

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

使用来自外部循环的值

方案:使用文本和日期

通过串联创建键列

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

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

使用公式更改数据类型

方案:条件值和测试错误

基于条件创建值

测试公式中的错误

方案:使用时间智能

计算累计销售额

比较一段时间的值

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

方案:排名和比较值

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

使用公式动态排序项目

入门

请访问 DAX 资源中心 Wiki, 在这里可以找到有关 DAX 的各类信息,包括博客、示例、白皮书以及行业领先专家和 Microsoft 提供的视频。

方案:执行复杂计算

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

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

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

将筛选器应用于公式

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

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

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

通过创建公式中的动态筛选器,可以轻松回答以下问题:

  • 当前产品的销售额对年度总销售额有什么贡献?

  • 与其他部门相比,此部门对于所有运营年的总利润贡献多少?

在数据透视表中使用的公式可能会受数据透视表上下文的影响,但您可以通过添加或删除筛选器有选择地更改上下文。 ALL 主题中的示例演示了如何这样做。 若要查找特定经销商的销售额占所有经销商销售额的比率,请创建一个度量值,用于计算当前上下文的值除以 ALL 上下文的值。

ALLEXCEPT 主题提供了如何有选择地清除公式上的筛选器的示例。 这两个示例将演练结果如何更改,具体取决于数据透视表的设计。

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

使用来自外部循环的值

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

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

方案:使用文本和日期

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

通过串联创建键列

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

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

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

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

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

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

使用公式更改数据类型

在 Power Pivot 中,数据类型的 数据类型 由源列确定,并且不能显式指定结果的 数据类型,因为最佳结果数据类型由 Power Pivot 。 但是,可以使用由 数据类型 执行的隐式 Power Pivot 操作输出数据类型。 

  • 要将日期或数字字符串转换为数字,请乘以 1.0。 例如,以下公式计算当前日期减去 3 天,然后输出相应的整数值。

    = (TODAY () -3) *1.0

  • 要将日期、数字或货币值转换为字符串,请将该值与空字符串连接。 例如,以下公式将今天的日期作为字符串返回。

    =""& TODAY ()

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

将实数转换为整数

方案:条件值和测试错误

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

基于条件创建值

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

测试公式中的错误

与 Excel 不同,在计算列的一行中不能有有效值,在另一行中不能有无效值。 也就是说,如果 Power Pivot 列的任何部分出现错误,则整个列都标有错误,因此必须始终更正导致无效值的公式错误。

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

为了避免在计算列中返回错误,可以使用逻辑函数和信息函数的组合来测试错误并始终返回有效值。 以下主题提供了如何在 DAX 中执行此操作的一些简单示例:

方案:使用时间智能

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

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

计算累计销售额

以下主题包含如何计算收盘和开盘余额的示例。 这些示例允许创建不同时间间隔(如天、月、季度或年)的运行余额。

比较一段时间的值

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

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

请参阅以下主题,了解如何检索自定义日期范围的示例,例如促销开始后的前 15 天。

如果使用时间智能函数检索自定义日期集,可以将该日期集用作执行计算的函数的输入,以跨时间段创建自定义聚合。 请参阅以下主题,了解如何执行下列操作的示例:

  • PARALLELPERIOD 函数

    注意: 如果不需要指定自定义日期范围,但使用标准会计单位(如月份、季度或年),我们建议使用为此设计的时间智能函数(如 TOTALQTD、TOTALMTD、TOTALQTD 等)执行计算。

方案:排名和比较值

若要只显示列或数据透视表中前 n 个项,有几个选项:

  • 您可以使用 Excel 2010 中的功能创建顶部筛选器。 您也可以在数据透视表中选择一些顶部或底部值。 本部分的第一部分介绍如何筛选数据透视表中的前 10 个项目。 有关详细信息,请参阅 Excel 文档。

  • 可以创建一个公式,用于动态对值进行排名,然后按排名值进行筛选,或者将排名值用作切片器。 本部分的第二部分介绍如何创建此公式,然后在切片器中使用该排名。

每种方法都有优缺点。

  • Excel 顶部筛选器易于使用,但该筛选器仅用于显示目的。 如果数据透视表基础的数据发生更改,则必须手动刷新数据透视表以查看更改。 如果需要动态处理排名,可以使用 DAX 创建一个公式,用于将值与列中的其他值进行比较。

  • DAX 公式功能更强大;此外,通过将排名值添加到切片器,只需单击切片器即可更改显示的热门值数。 但是,计算成本较高,此方法可能不适合包含许多行的表。

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

显示数据透视表中的顶部或底部值

  1. 在数据透视表中,单击"行标签"标题 中的向下 箭头。

  2. 选择 10>值筛选器

  3. 在" 排名前 10 <列名称>对话框中,选择要排名的列和值数,如下所示:

    1. 选择"顶部"以查看具有最高值的单元格,或选择"底部"查看具有最低值的单元格。

    2. 键入想要查看的顶部或底部值的数量。 默认值为 10。

    3. 选择值显示方式:

名称

说明

项目

选择此选项可筛选数据透视表,以按值仅显示顶部或底部项目的列表。

百分比

选择此选项可筛选数据透视表,仅显示添加到指定百分比的项。

Sum

选择此选项可显示顶部或底部项的值的总和。

  1. 选择包含要排名的值的列。

  2. 单击“确定”。

使用公式动态排序项目

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

需要更多帮助?

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×