从数据到信息
从信息到洞察

DAX表达式中的错误

现在你已经了解了一些基础语法知识,接下来我们将学习如何优雅的处理无效计算。当引用的数据对公式无效时,无效计算就产生了。例如,你可能执行了一个除以零或将非数值型的列值用于算术运算,如乘法运算的操作。你必须了解这些错误在默认情况下是如何处理的,以及如何采取针对性的操作来截获这些错误。

在学习如何处理错误之前,有必要梳理一下 DAX 公式计算中可能出现的各种错误。它们是:

  • 转换错误
  • 算术运算错误
  • 空值或缺失值

转换错误

第一种错误是转换错误。正如你在本章中看到的,只要运算需要,DAX 就会自动将值在字符串和数字之间转换。下面所有的示例都使用有效的 DAX 表达式:

"10" + 32 = 42

"10" & 32 = "1032"

10 & 32 = "1032"

DATE (2010,3,25) = 3/25/2010

DATE (2010,3,25) + 14 = 4/8/2010

DATE (2010,3,25) & 14 = "3/25/201014"

这些公式总是正确的,因为它们是用常数值运算的。但是,如果 VatCode 是一个字符串,那么下面的示例结果是什么呢?

SalesOrders[VatCode] + 100

在本例中,这个求和表达式的第一个运算对象是文本类型,所以你必须确保 DAX 可以将该列中的所有值转换为数字。如果存在部分内容无法被 DAX 转换以满足运算需要,将会导致转换错误。以下是一些典型的情况:

"1 + 1" + 0 = Cannot convert value '1+1' of type string to a number

DATEVALUE ("25/14/2010") = Type mismatch

为了避免此类错误,你需要在 DAX 表达式中添加针对错误的检测逻辑来截获错误条件,以确保始终返回有意义的结果。

算术运算错误

第二类错误来自算术运算,例如除以零或负数的平方根。这些都不是与转换相关的错误:当你试图用无效值去调用函数或执行运算时,DAX 都会提示这些错误。

零做除数的情况需要特殊的处理,因为它的行为不是很直观(也许数学家除外)。当你把一个数字除以 0 时,DAX 通常会返回一个无穷大的特殊值(Infinity)。此外,在非常特殊的情况下,0 除以 0 或无穷大除以无穷大,DAX 返回特殊的 NaN(而不是一个数字)。

考虑到这是一种奇怪的行为,我们将其总结在下表中

零做除数产生的特殊结果

值得注意的是,Infinity 和 NaN 不是错误,而是 DAX 中的特殊值。事实上,如果你把一个数字除以无穷,这个表达式不会产生错误,但会返回 0:

9954 / ( 7/0 ) = 0

除了这种特殊情况,DAX 在调用带有错误参数的函数时会返回计算错误,比如负数的平方根:

SQRT ( -1 ) = 函数 ‘SQRT’ 的参数的数据类型错误或者结果太大或太小

如果 DAX 检测到这样的错误,它就会阻止表达式的进一步计算,并触发错误。你可以使用 ISERROR 函数检查表达式是否导致错误,这个函数在后面会提到。

像 NaN 这样的特殊值可以在 Power Pivot 或 Visual Studio 窗口中显示,但是在某些客户端工具(如 Excel 透视表)中,它们可能会显示为错误。此外,这些特殊值将被错误检测函数检测为错误

空值或缺失值

我们研究的第三类并非特定的错误条件,而是针对计算时存在的空值,当把空值与其他元素结合时,可能会导致意外的结果或计算错误。你需要了解 DAX 是如何处理这些特殊值的。

DAX 用空值(Blank)处理缺失值、空白值或空单元格。空值不是一个真实的值,而是识别这些条件的一种特殊方式。通过调用空值函数可以在 DAX 表达式中获得空值,这与空字符串不同。例如,下面的表达式总是返回一个空白值,它将作为空单元格显示在数据透视表中:

=BLANK()

就其本身而言,这个表达式是无用的,但是每当你想返回一个空值时,空值函数就变得有用了。例如,你可能想要显示一个空单元格而不是 0,如下面的表达式中计算交易的总折扣,如果折扣为 0,则单元格为空:

= IF ( Sales[DiscountPerc] = 0, BLANK (), Sales[DiscountPerc] * Sales[Amount] )

空值本身不是错误类型,它只是显示为空白结果。因此,包含空白的表达式可能返回值或空白,这取决于计算的需要。例如,当 Sales[Amount]为空时,下面的表达式返回空:

= 10 * Sales[Amount]

换句话说,当有一项或两项为空时,乘积的结果为空。在 DAX 表达式中,这种空值的传递也发生在其他一些算术和逻辑运算中,如下面的例子所示:

BLANK () + BLANK () = BLANK ()

10 * BLANK () = BLANK ()

BLANK () / 3 = BLANK ()

BLANK () / BLANK () = BLANK ()

BLANK () || BLANK () = FALSE

BLANK () && BLANK () = FALSE

BLANK () = BLANK () = TRUE

然而,空值的传递并不适用于所有公式。有些计算并不传递空值,而是根据公式的其他项返回值。这些示例包括加法、减法、空值作为除数,以及空值与有效值之间的逻辑操作。在下列表达式中,你可以看到关于这些条件的例子,以及它们的结果:

BLANK () - 10 = -10

18 + BLANK () = 18

4 / BLANK () = 无穷大

0 / BLANK () = NaN

FALSE || BLANK () = FALSE

FALSE && BLANK () = FALSE

TRUE || BLANK () = TRUE

TRUE && BLANK () = FALSE

Excel 和 SQL 中的空值

Excel 使用不同的方法处理空值。在 Excel 中,当在求和或乘法中使用空值时,它们都被认为是 0,但如果它们是除法或逻辑表达式的一部分,则会返回错误。

在 SQL 中,NULL 值以不同于 DAX 中的空值的方式在表达式中传递。正如你在前面的示例中看到的,DAX 表达式中出现的空白并不总是导致空白结果,而 SQL 中出现的 NULL 通常使得整个表达式求值为 NULL。

理解空值或缺失值在 DAX 表达式中的行为,并在计算中使用空值函数返回空单元格,是控制 DAX 表达式结果的重要技能。当你检测到错误的结果或其他错误类型时,可以利用空值函数来处理,你将在下一节中学习到这一点。

拦截错误

现在你已经看到了各种可能发生的错误,接下来我们将学习截获和纠正错误的方法,以及如何显示有用的错误提示消息。DAX 表达式的错误通常取决于表达式本身引用的表和列中的值。因此,你可能希望避免这些错误出现并返回出错消息。标准做法是检查表达式是否返回错误,如果返回,则用一条消息或默认值替换错误,在这方面 DAX 提供了专门的函数。

第一个是 IFERROR 函数,它与 IF 函数非常相似,但它计算的不是布尔条件,而是检查表达式是否返回错误。你可以在下面看到 IFERRROR 函数的两个典型用法:

= IFERROR ( Sales[Quantity] * Sales[Price], BLANK () )

= IFERROR ( SQRT ( Test[Omega] ), BLANK () )

在第一个表达式中,如果 Sales[Quantity]或 Sales[Price]是不能转换为数字的字符串格式,则返回的表达式为空值;否则,返回数量和价格的乘积。

在第二个表达式中,每当 Test[Omega]列包含负数时,结果都是空单元格。

当你以这种方式使用 IFERROR 时,你会遵循一种更一般的模式,即使用 ISERROR 和 IF

= IF (
    ISERROR ( Sales[Quantity] * Sales[Price] ),
    BLANK (),
    Sales[Quantity] * Sales[Price]
)

= IF (
    ISERROR (
        SQRT ( Test[Omega] )
    ),
    BLANK (),
    SQRT ( Test[Omega] )
)

当返回的表达式被检测为错误的情况下,应该使用 IFERROR 函数,它可以避免你在两个位置复制表达式,并且生成的表达式在未来需要修改时具有更好的可读性和安全性。如果检测到错误时你想返回另一个表达式的结果,应该使用 IF。例如,检测 SQRT 函数的参数是否有效,只对正数计算平方根,对负数返回空值:

= IF ( Test[Omega] >= 0, SQRT ( Test[Omega] ), BLANK () )

由于 IF 函数的第三参数默认返回空值,你可以将上面的表达式修改为

= IF ( Test[Omega] >= 0, SQRT ( Test[Omega] ))

有种特殊情况是针对空值的测试。使用 ISBLANK 函数检测条件是否返回空值,如果为空则返回 TRUE。这一点很重要,尤其是当这个缺失值产生的空值与零值有着不同含义的时候。在下面的示例中,我们计算交易的运输成本,如果交易产品本身没有填写重量,则使用产品的默认运输成本:

= IF (
    ISBLANK ( Sales[Weight] ),
    Sales[DefaultShippingCost],
    Sales[Weight] * Sales[ShippingPrice]
)

如果我们直接用产品重量乘以运输价格,对于所有缺失重量数据的销售交易而言,将会产生空成本。

避免使用处理错误的函数

虽然现在还不是讨论 DAX 代码优化的时候,你也需要意识到错误处理函数可能会在代码中造成严重的性能问题。 

此处为隐藏内容 VIP会员和付费用户可见

付费或下载说明

以上隐藏内容查看价格为1G 币,请先
注:加入 VIP 会员可享受全站权益,性价比更高。单独购买的内容长期有效,不受时间限制。

2
说点什么

1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
2 评论数
2 被回复的评论
3 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
成员
alexwyc628

发现一个小BUG:
在算术运算错误一节中,“在非常特殊的情况下0除以0或无穷大除以无穷大,DAX返回特殊的NaN(而不是数字值)。”一句,
正确的翻译应为:
”……DAX会返回一个特殊的值,NaN (not a number,意为“不是一个数字”)。”

原文(位于原书的32页)为”…DAX returns the special NaN (not a number) value.”,
“value”一词位于括号外,说明括号中的内容是用来专门解释“NaN”这个术语的。

整体上无伤大雅,只是在语意上有微妙的不同。

参考链接:https://en.wikipedia.org/wiki/NaN

DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数