2.4 表达式中的错误处理

2020-02-25 本文已影响0人 jweishan

第2章 DAX简介

2.1 理解DAX计算
2.2了解计算列和度量值
2.3 变量入门
2.4 表达式中的错误处理
2.5 格式化DAX代码
2.6 聚合函数和迭代函数
2.7 使用常见的DAX函数

表达式中的错误处理

既然已经了解了语法的一些基础知识，是时候学习如何优雅地处理无效计算了。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是字符串，那么下面的公式呢？
Sales[VatCode] + 100

因为此求和公式的第一个操作数是Text数据类型的列，所以作为开发人员，必须确信DAX可以将该列中的所有值转换为数字。如果DAX无法将某些内容转换为适合运算符需要的内容，则会发生转换错误。以下是一些典型情况：

"1 + 1" + 0 = Cannot convert value '1 + 1' of type Text to type Number
DATEVALUE ("25/14/2010") = Type mismatch

如果要避免这些错误，在DAX表达式中添加错误检测逻辑以拦截错误条件并返回有意义的结果非常重要。可以在发生错误后拦截错误或事先检查操作数以了解错误情况来获得相同的结果。但是，主动检查错误情况比让错误发生然后捕获要好。

算术运算错误

第二类错误是算术运算错误，例如被零除或负数求平方根。这些不是与转换相关的错误：使用无效数值调用函数及运算符时，DAX都会引发算术运算错误。

被零除需要特殊处理，因为它的行为不直观（数学家可能除外）。当一个数字除以零时，DAX返回特殊值Infinity（无穷大）。在0除以0或Infinity除以Infinity的特殊情况下，DAX返回特殊的NaN（不是数字）值。这些异常行为在表2-3中进行了总结。

表 2-3 被零除的特殊结果

表达式	结果
10/0	Infinity
7/0	Infinity
0/0	NaN
(10/0)/(7/0)	NaN

重要的是要注意，Infinity和NaN并不是错误，而是DAX中的特殊值。实际上，如果将数字除以Infinity，则表达式不会产生错误。而是返回0：
9954 / ( 7 / 0 ) = 0

除了这种特殊情况外，当使用不正确的参数（例如负数的平方根）调用函数时，DAX可能返回算术错误：

SQRT ( -1 ) = An argument of function 'SQRT' has the wrong data type or the result is too
large or too small

如果DAX检测到这样的错误，它将阻止对该表达式进一步运算，并引发错误。可以使用ISERROR函数检查表达式是否导致错误。我们将在本章后述这种情况。

请记住，象NaN这样的特殊值会在Power BI等工具的用户界面中显示为正常值。但是，其他客户端工具如Excel pivot table（Excel数据透视表）显示这些值时，可能视为错误。最终，用错误检测函数检测这些特殊值均为错误。

空值或缺失值

第三类不是特定的错误情况，而是存在空值。当空值与计算中的其他元素结合使用时，可能会导致意外结果或计算错误。

DAX以相同的方式处理缺失值、空值或空单元格，即使用值BLANK（空值）。BLANK不是真正的值，而是识别这些情况的一种特殊方法。我们可以通过调用BLANK函数来获得DAX表达式中的值BLANK，BLANK不同于空字符串。例如，以下表达式始终返回一个空值，该空值可以在不同的客户端工具中显示为空字符串或"（blank）"：
= BLANK ()

单独使用此表达式没什么用处，但需要返回空值时，BLANK函数就会会变得有用，例如，可能要显示一个空结果而不是0。以下表达式计算销售交易的总折扣，如果折扣为0，则保留空白值：

=IF (
    Sales[DiscountPerc] = 0,              -- Check if there is a discount
    BLANK (),                             -- Return a blank if no discount is present
    Sales[DiscountPerc] * Sales[Amount]   -- Compute the discount otherwise
)

空值本身并不是一个错误；它只是一个空值。因此，包含BLANK的表达式可能会返回值或空值，具体取决于计算式。例如，下面的表达式当Sales[Amount]是 BLANK时返回 BLANK：

= 10 * Sales[Amount]

换句话说，只要一项或两项为BLANK，则算术积的结果为BLANK。这在需要检查空值时会带来挑战。由于存在隐式转换，因此无法使用等于运算符（=）区分表达式是0（或空字符串）还是BLANK。实际上，以下逻辑条件始终是正确的：

BLANK () = 0     -- Always returns TRUE
BLANK () = ""    -- Always returns TRUE

因此，如果Sales [DiscountPerc]或Sales [Clerk]列为空，即使分别针对0和空字符串测试，以下条件也会返回TRUE：

Sales[DiscountPerc] = 0  -- Returns TRUE if DiscountPerc is either BLANK or 0
Sales[Clerk] = ""        -- Returns TRUE if Clerk is either BLANK or ""

在这种情况下，可以使用ISBLANK函数检查值是否为BLANK：

ISBLANK ( Sales[DiscountPerc] )  -- Returns TRUE only if DiscountPerc is BLANK
ISBLANK ( Sales[Clerk] )         -- Returns TRUE only if Clerk is BLANK

DAX表达式中BLANK的传递还发生在其他几种算术和逻辑运算中，如以下示例所示：

BLANK () + BLANK () = BLANK ()
10 * BLANK () = BLANK ()
BLANK () / 3 = BLANK ()
BLANK () / BLANK () = BLANK ()

但是，并不是公式都会在表达式结果中传递BLANK。某些计算不会传递BLANK，而是根据公式的其他条件返回一个值。这些示例包括加减法，BLANK除法以及包括BLANK的逻辑运算。以下表达式显示了其中一些条件及其结果：

BLANK () − 10 = −10
18 + BLANK () = 18
4 / BLANK () = Infinity
0 / BLANK () = NaN
BLANK () || BLANK () = FALSE
BLANK () && BLANK () = FALSE
( BLANK () = BLANK () ) = TRUE
( BLANK () = TRUE ) = FALSE
( BLANK () = FALSE ) = TRUE
( BLANK () = 0 ) = TRUE
( BLANK () = "" ) = TRUE
ISBLANK ( BLANK() ) = TRUE
FALSE || BLANK () = FALSE
FALSE && BLANK () = FALSE
TRUE || BLANK () = TRUE
TRUE && BLANK () = FALSE

Excel和SQL中的空值
Excel具有处理空值的另一种方法。在Excel中，将所有空值用于求和或乘法时都将被视为0，但是如果它们是除法或逻辑表达式的一部分，则它们可能会返回错误。
在SQL中，空值在表达式中的传递方式与DAX中BLANK发生的方式不同。从前面的示例中可以看到，DAX表达式中存在BLANK并不总是导致BLANK结果，而SQL 中NULL的存在对于整个表达式通常为NULL。每当在关系数据库上使用DirectQuery时，这种区别就很重要，因为某些计算在SQL中执行，而其他计算在DAX中执行。两个引擎中BLANK的语义不同可能会导致意外行为。

了解DAX表达式中空值或缺失值的行为以及在计算中使用BLANK返回空单元格是控制DAX表达式结果的重要技能。正如我们在下一节中演示的那样，当检测到错误的值或其他错误时，通常可以使用BLANK作为结果。

拦截错误

既然已经详细介绍了可能发生的各种错误，接下来还需要向您介绍拦截错误并纠正错误的技术，至少出现错误时返回有意义错误消息。DAX表达式中是否存在错误，通常取决于表达式本身中使用的列值。因此，要控制产生错误的条件并返回错误消息。标准技术是检查表达式是否返回错误，如果是，则将错误替换为特定的消息或默认值。有几个DAX函数具备此功能。
第一个是IFERROR函数，它与IF函数类似，但是它不评估布尔条件，而是检查表达式是否返回错误。IFERROR函数的两种典型用法如下：

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

在第一个表达式中，如果Sales [Quantity]或Sales [Price]是无法转换为数字的字符串，则表达式返回空值。否则，返回数量和价格的乘积。
在第二个表达式中，当TestΩ列包含负数时，结果返回一个空单元格。
还有一个更通用的使用IFERROR的模式，该模式需要同时使用ISERROR和IF：

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

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

对于这些情况，IFERROR是更好的选择。只要返回结果的表达式与错误测试的表达式相同，就可以使用IFERROR；无需在两个地方重复该表达式，并且代码更安全，更易读。但是，开发人员要返回其他表达式的结果时应使用IF。
此外，完全可以通过在使用参数之前测试参数来减少错误的出现。例如，可以检测SQRT的参数是否为正，并为负值返回BLANK：

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

考虑到IF语句的第三个参数默认为BLANK，也可以编写简洁的等效表达式：

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

一种常见的情况是空值测试。如果其参数为BLANK，ISBLANK检测到空值，则返回TRUE。此函数特别重要，尤其是当不可用的值并不表示其为0时。以下示例说明，如果交易本身未指定重量，使用交易的默认运费来计算销售交易的运费：

= IF (
   ISBLANK ( Sales[Weight] ),             -- If the weight is missing
   Sales[DefaultShippingCost],            -- then return the default cost
   Sales[Weight] * Sales[ShippingPrice]   -- otherwise multiply weight by shipping price
)

如果我们简单地将产品重量乘以运输价格，那么由于BLANK的乘积传递，所有没有重量数据的销售交易的运费为空。

使用变量时，必须在定义变量而不是使用变量时检查错误。实际上，以下代码中的第一个公式返回零，第二个公式始终出现错误，最后一个公式根据使用的DAX版本产生不同的结果（最新版本也出现错误）：

IFERROR ( SQRT ( -1 ), 0 )                -- This returns 0

VAR WrongValue = SQRT ( -1 )              -- Error happens here, so the result is
RETURN                                    -- always an error
    IFERROR ( WrongValue, 0 )             -- This line is never executed

IFERROR (                                 -- Different results depending on versions
    VAR WrongValue = SQRT ( -1 )          -- IFERROR throws an error in 2017 versions
    RETURN                                -- IFERROR returns 0 in versions until 2016
        WrongValue,
    0
)

错误产生在评估WrongValue的时候，因此，在第二个示例中，引擎将永远不会执行IFERROR函数，而第三个示例的结果取决于产品版本。如果需要检查错误，请在使用变量时采取一些额外的预防措施。

避免使用错误处理函数
尽管我们将在本书的后面部分会介绍优化，但是您需要意识到错误处理函数可能会在代码中造成严重的性能问题。并不是说这些函数本身很慢。问题在于，发生错误时，DAX引擎无法在其代码中使用优化的路径。在大多数情况下，检查操作数是否存在错误比使用错误处理引擎更有效。例如，不要这样写：
IFERROR (
  SQRT ( Test[Omega] ),
  BLANK ()
)
最好这样写：
IF (
  Test[Omega] >= 0,
  SQRT ( Test[Omega] ),
  BLANK ()
)
第二个表达式不需要检测错误，并且比前一个表达式快。当然，这是一般规则。有关详细说明，请参见第19章”优化DAX”。
避免使用IFERROR的另一个原因是它无法拦截更深层次的错误。例如，以下代码拦截了Table表的Amount列不包含数字的情况下转换为空值时发生的任何错误，如前所述，此执行代价很高，因为需要对Table中的每一行进行评估。
SUMX (
   Table,
   IFERROR ( VALUE ( Table[Amount] ), BLANK () )
)
)
请注意，由于DAX引擎中的优化，以下代码不会截获与前面示例相同的错误。如果Table [Amount]仅在一行中包含不是数字的字符串，则整个表达式将生成IFERROR不会拦截的错误。
IFERROR (
   SUMX (
       Table,
      VALUE ( Table[Amount] )
   ),
   BLANK ()
)
ISERROR 具有与 IFERROR 相同的行为。确保谨慎使用它们，并且仅将IFERROR / ISERROR用于拦截表达式直接引发的错误，不要在用在嵌套计算中。

产生错误

有时，错误就错误，在错误出现的情况下，公式不应返回默认值。实际上，返回默认值实际上会导致不正确结果。例如，包含不一致数据的配置表应生成无效的报告，而不是不可靠的数字，生成无效的报告可以认为是正确的。

而且，除了一般错误之外，人们可能希望产生一条对用户更有意义的错误消息。这样的消息将帮助用户找到问题所在。

考虑一个场景，该场景需要计算以开尔文为单位的绝对温度的平方根，以在复杂的科学计算中大致调整声音的速度。显然，我们不希望温度为负数。如果碰巧由于测量问题而发生这种情况，我们需要提出错误并停止计算。在这种情况下，此代码很危险，因为它隐藏了问题：

= IFERROR (
    SQRT ( Test[Temperature] ),
    0
)

相反，为了保护计算，应该这样编写公式：

= IF (
    Test[Temperature] >= 0,
    SQRT ( Test[Temperature] ),
    ERROR ( "The temperature cannot be a negative number. Calculation aborted." )
)