DAX表达式中的错误

显示全部楼层 · 2021-2-21 19:36:37

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

在学习如何处理错误之前，有必要梳理一下 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 代码优化的时候，你也需要意识到错误处理函数可能会在代码中造成严重的性能问题。这并不是说他们本身很慢。问题在于当错误出现时 DAX 引擎不能在其代码中使用经过优化的计算路径。在大多数情况下，检查运算对象比检查错误（使用错误处理引擎）更有效率。例如，不要这样写：