本部分提供一些示例链接,这些示例演示了以下方案中 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 时间智能函数包括可帮助从数据中检索日期或日期范围的函数。 然后,可以使用这些日期或日期范围计算类似时间段的值。 时间智能函数还包括使用标准日期间隔的函数,用于比较月、年或季度的值。 您还可以创建一个公式,用于比较指定时间段的第一个和最后一个日期的值。
有关所有时间智能函数的列表,请参阅Time Intelligence Functions (DAX) 。 有关如何在分析中有效地使用日期和时间 Power Pivot ,请参阅 Power Pivot 中的日期。
计算累计销售额
以下主题包含如何计算结余额和未结余额的示例。 这些示例允许创建不同时间间隔(如天、月、季度或年)的运行余额。
比较一段时间的值
以下主题包含如何比较不同时间段中的总和的示例。 DAX 支持的默认时间段是月、季度和年。
计算自定义日期范围中的值
请参阅以下主题,了解如何检索自定义日期范围的示例,例如开始促销后的前 15 天。
如果使用时间智能函数检索自定义日期集,可以使用该日期集作为执行计算的函数的输入,以跨时间段创建自定义聚合。 请参阅以下主题,了解如何执行此操作的示例:
-
注意: 如果不需要指定自定义日期范围,但使用标准会计单位(如月、季度或年),我们建议使用为此设计的时间智能函数(如 TOTALQTD、TOTALMTD、TOTALQTD 等)执行计算。
方案:排名和比较值
若要只显示列或数据透视表中的前 n 个项,有几个选项:
-
可以使用 2010 Excel中的功能创建 Top 筛选器。 还可以在数据透视表中选择一些顶部或底部值。 本部分的第一部分介绍如何筛选数据透视表中的前 10 个项目。 有关详细信息,请参阅 Excel 文档。
-
您可以创建一个动态对值进行排名的公式,然后按排名值进行筛选,或者使用排名值作为切片器。 本部分的第二部分介绍如何创建此公式,然后在切片器中使用该排名。
每种方法都有优缺点。
-
"Excel顶部"筛选器易于使用,但筛选器仅用于显示目的。 如果数据透视表的基础数据发生更改,则必须手动刷新数据透视表以查看更改。 如果需要动态处理排名,可以使用 DAX 创建一个公式,用于将值与列中的其他值进行比较。
-
DAX 公式功能更强大;此外,通过将排名值添加到切片器,只需单击切片器即可更改显示的顶部值数。 但是,计算成本较高,此方法可能不适合包含许多行的表。
仅显示数据透视表中的前 10 个项目
显示数据透视表中的顶部或底部值
|
使用公式动态排序项目
以下主题包含如何使用 DAX 创建存储在计算列中的排名的示例。 由于 DAX 公式是动态计算的,因此即使基础数据已更改,也始终可以确信排名正确。 此外,由于公式在计算列中使用,因此可以在切片器中使用该排名,然后选择前 5、前 10 甚至前 100 个值。