Power Pivot 中的 DAX 方案

重要:  本文是由机器翻译的,请参阅免责声明。请在 此处 中查找本文的英文版本以便参考。

本节提供了一些指向示例的链接,这些示例阐释了如何在以下应用场景下使用 DAX 公式。

  • 执行复杂计算

  • 处理文本和日期

  • 条件值和错误测试

  • 使用时间智能

  • 对值进行排名和比较

本文内容

入门

示例数据

其他资源

应用场景:执行复杂计算

创建针对数据透视表的自定义计算

将筛选器应用于公式

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

使用来自外部循环的值

应用场景:处理文本和日期

通过串联创建键列

基于从文本日期提取的日期部分编译日期

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

使用公式更改数据类型

应用场景:条件值和错误测试

基于条件创建值

测试公式内是否有错误

应用场景:使用时间智能

计算累计销售额

比较一段时间内的值

针对自定义日期范围计算值

应用场景:对值进行排名和比较

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

通过使用公式动态对项进行排序

入门

示例数据

如果您不熟悉 DAX 公式,您可能想要通过查看 Power Pivot 示例数据中的示例来开始。有关详细信息,请参阅获取针对 DAX 和数据模型教程的示例数据

其他资源

您可能希望访问DAX 资源中心 Wiki ,您可以找到各种 DAX 包括博客、 示例、 白皮书和视频行业前导专业人员和 Microsoft 提供的有关信息。

应用场景:执行复杂计算

DAX 公式可以执行涉及自定义聚合、筛选和使用条件值的复杂计算。本节提供关于如何初步了解自定义计算的示例。

创建针对数据透视表的自定义计算

CALCULATE 和 CALCULATETABLE 是用于定义计算字段的强大而又灵活的函数。通过这些函数,您可以更改将在其中执行计算的上下文。您还可以自定义要执行的聚合或数学运算的类型。有关示例,请参阅下列主题。

将筛选器应用于公式

在 DAX 函数采用表作为参数的大多数地方,您通常可以通过使用 FILTER 函数来代替表名称,或通过指定筛选表达式作为函数参数之一,改为传入筛选的表。下面的主题提供一些示例,说明如何创建筛选器以及筛选器是如何影响公式结果的。有关详细信息,请参阅在 DAX 公式中筛选数据

FILTER 函数允许您通过使用表达式来指定筛选条件,而其他函数是为筛选出空白值而专门设计的。

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

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

  • 某年度中当前产品的销售额占总销售额的比率是多少?

  • 对于所有运营年份,与其他部分相比,此部分占总利润的比率是多少?

在数据透视表中使用的公式可能会受数据透视表的上下文中,但您可以选择性地更改通过添加或删除筛选器的上下文。所有主题中的示例显示如何执行此操作。若要查找销售额的比率针对特定分销商销售上方的所有经销商,您可以创建计算当前上下文除以所有上下文的值的值的度量值。

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 执行的隐式数据类型转换来操作输出数据类型。有关类型转换的详细信息,请参阅获取针对 DAX 和数据模型教程的示例数据

  • 若要将日期或数字字符串转换为数字,请与 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 中的功能创建一个 Top 筛选器。您还可以选择数据透视表中探顶值或探底值的数目。本节的第一部分描述如何在数据透视表中筛选前 10 项。有关详细信息,请参阅 Excel 文档。

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

每种方法都各有优缺点。

  • Excel Top 筛选器易于使用,但该筛选器仅用于显示目的。如果基于数据透视表的数据发生更改,则您必须手动刷新数据透视表以便看到这些更改。如果您需要动态使用排名,则可以使用 DAX 创建一个公式以便对同一列内的各个值进行相互比较。

  • DAX 公式更强大;此外,通过向切片器添加排名值,您只需单击切片器即可更改显示的探顶值的数目。但是,在执行计算时会占用大量系统资源,并且此方法可能不适合于具有许多行的表。

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

在数据透视表中显示探顶值或探底值

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

  2. 依次选择“值筛选器”>“前 10 位”

  3. 前 10 个筛选 < 列名称 >对话框中,选择的列的排位和值的数目,如下所示 ︰

    1. 选择“最大”可以看到具有最高值的单元,选择“最小”可以看到具有最低值的单元。

    2. 键入要看到的探顶值或探底值的数目。默认值为 10。

    3. 选择希望值如何显示:

名称

说明

选择此选项可以筛选数据透视表,以便只按其值显示探顶项或探底项的列表。

百分比

选择此选项可以筛选数据透视表,以便只显示合计达到指定百分比的项。

总和

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

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

  2. 单击“确定”

通过使用公式动态对项进行排序

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

注意: 机器翻译免责声明:本文是由无人工介入的计算机系统翻译的。Microsoft 提供机器翻译是为了帮助非英语国家/地区用户方便阅读有关 Microsoft 产品、服务和技术的内容。由于机器翻译的原因,本文可能包含词汇、语法或文法方面的错误。

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

此信息是否有帮助?

谢谢您的反馈!

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

×