Introducing DAX

Contents

Handling errors in DAX expressions

Now that you have seen some of the basics of the syntax, it is time to learn how to handle invalid calculations gracefully. A DAX expression might contain invalid calculations because the data it references is not valid for the formula. For example, the formula might contain a division by zero or reference a column value that is not a number while being used in an arithmetic operation such as multiplication. It is good to learn how these errors are handled by default and how to intercept these conditions for special handling.

Before discussing how to handle errors, though, we describe the different kinds of errors that might appear during a DAX formula evaluation. They are

  • Conversion errors

  • Arithmetic operations errors

  • Empty or missing values

Conversion errors

The first kind of error is the conversion error. As we showed previously in this chapter, DAX automatically converts values between strings and numbers whenever the operator requires it. All these examples are valid DAX expressions:

"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"

These formulas are always correct because they operate with constant values. However, what about the following formula if VatCode is a string?

Sales[VatCode] + 100

Because the first operand of this sum is a column that is of Text data type, you as a developer must be confident that DAX can convert all the values in that column into numbers. If DAX fails in converting some of the content to suit the operator needs, a conversion error will occur. Here are some typical situations:

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

If you want to avoid these errors, it is important to add error detection logic in DAX expressions to intercept error conditions and return a result that makes sense. One can obtain the same result by intercepting the error after it has happened or by checking the operands for the error situation beforehand. Nevertheless, checking for the error situation proactively is better than letting the error happen and then catching it.

Arithmetic operations errors

The second category of errors is arithmetic operations, such as the division by zero or the square root of a negative number. These are not conversion-related errors: DAX raises them whenever we try to call a function or use an operator with invalid values.

The division by zero requires special handling because its behavior is not intuitive (except, maybe, for mathematicians). When one divides a number by zero, DAX returns the special value Infinity. In the special cases of 0 divided by 0 or Infinity divided by Infinity, DAX returns the special NaN (not a number) value.

Because this is unusual behavior, it is summarized in Table 2-3.

Table 2-3 Special Result Values for Division by Zero

Expression

Result

10 / 0

Infinity

7 / 0

Infinity

0 / 0

NaN

(10 / 0) / (7 / 0)

NaN

It is important to note that Infinity and NaN are not errors but special values in DAX. In fact, if one divides a number by Infinity, the expression does not generate an error. Instead, it returns 0:

9954 / ( 7 / 0 ) = 0

Apart from this special situation, DAX can return arithmetic errors when calling a function with an incorrect parameter, such as the square root of a negative number:

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

If DAX detects errors like this, it blocks any further computation of the expression and raises an error. One can use the ISERROR function to check if an expression leads to an error. We show this scenario later in this chapter.

Keep in mind that special values like NaN are displayed in the user interface of several tools such as Power BI as regular values. They can, however, be treated as errors when shown by other client tools such as an Excel pivot table. Finally, these special values are detected as errors by the error detection functions.

Empty or missing values

The third category that we examine is not a specific error condition but rather the presence of empty values. Empty values might result in unexpected results or calculation errors when combined with other elements in a calculation.

DAX handles missing values, blank values, or empty cells in the same way, using the value BLANK. BLANK is not a real value but instead is a special way to identify these conditions. We can obtain the value BLANK in a DAX expression by calling the BLANK function, which is different from an empty string. For example, the following expression always returns a blank value, which can be displayed as either an empty string or as “(blank)” in different client tools:

= BLANK ()

On its own, this expression is useless, but the BLANK function itself becomes useful every time there is the need to return an empty value. For example, one might want to display an empty result instead of 0. The following expression calculates the total discount for a sale transaction, leaving the blank value if the discount is 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, by itself, is not an error; it is just an empty value. Therefore, an expression containing a BLANK might return a value or a blank, depending on the calculation required. For example, the following expression returns BLANK whenever Sales[Amount] is BLANK:

= 10 * Sales[Amount]

In other words, the result of an arithmetic product is BLANK whenever one or both terms are BLANK. This creates a challenge when it is necessary to check for a blank value. Because of the implicit conversions, it is impossible to distinguish whether an expression is 0 (or empty string) or BLANK using an equal operator. Indeed, the following logical conditions are always true:

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

Therefore, if the columns Sales[DiscountPerc] or Sales[Clerk] are blank, the following conditions return TRUE even if the test is against 0 and empty string, respectively:

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

In such cases, one can use the ISBLANK function to check whether a value is BLANK or not:

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

The propagation of BLANK in a DAX expression happens in several other arithmetic and logical operations, as shown in the following examples:

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

However, the propagation of BLANK in the result of an expression does not happen for all formulas. Some calculations do not propagate BLANK. Instead, they return a value depending on the other terms of the formula. Examples of these are addition, subtraction, division by BLANK, and a logical operation including a BLANK. The following expressions show some of these conditions along with their results:

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

EMPTY VALUES IN EXCEL AND SQL

Excel has a different way of handling empty values. In Excel, all empty values are considered 0 whenever they are used in a sum or in a multiplication, but they might return an error if they are part of a division or of a logical expression.

In SQL, null values are propagated in an expression differently from what happens with BLANK in DAX. As you can see in the previous examples, the presence of a BLANK in a DAX expression does not always result in a BLANK result, whereas the presence of NULL in SQL often evaluates to NULL for the entire expression. This difference is relevant whenever you use DirectQuery on top of a relational database because some calculations are executed in SQL and others are executed in DAX. The different semantics of BLANK in the two engines might result in unexpected behaviors.

Understanding the behavior of empty or missing values in a DAX expression and using BLANK to return an empty cell in a calculation are important skills to control the results of a DAX expression. One can often use BLANK as a result when detecting incorrect values or other errors, as we demonstrate in the next section.

Intercepting errors

Now that we have detailed the various kinds of errors that can occur, we still need to show you the techniques to intercept errors and correct them or, at least, produce an error message containing meaningful information. The presence of errors in a DAX expression frequently depends on the value of columns used in the expression itself. Therefore, one might want to control the presence of these error conditions and return an error message. The standard technique is to check whether an expression returns an error and, if so, replace the error with a specific message or a default value. There are a few DAX functions for this task.

The first of them is the IFERROR function, which is similar to the IF function, but instead of evaluating a Boolean condition, it checks whether an expression returns an error. Two typical uses of the IFERROR function are as follows:

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

In the first expression, if either Sales[Quantity] or Sales[Price] is a string that cannot be converted into a number, the returned expression is an empty value. Otherwise, the product of Quantity and Price is returned.

In the second expression, the result is an empty cell every time the Test[Omega] column contains a negative number.

Using IFERROR this way corresponds to a more general pattern that requires using ISERROR and IF:

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

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

In these cases, IFERROR is a better option. One can use IFERROR whenever the result is the same expression tested for an error; there is no need to duplicate the expression in two places, and the code is safer and more readable. However, a developer should use IF when they want to return the result of a different expression.

Besides, one can avoid raising the error altogether by testing parameters before using them. For example, one can detect whether the argument for SQRT is positive, returning BLANK for negative values:

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

Considering that the third argument of an IF statement defaults to BLANK, one can also write the same expression more concisely:

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

A frequent scenario is to test against empty values. ISBLANK detects empty values, returning TRUE if its argument is BLANK. This capability is important especially when a value being unavailable does not imply that it is 0. The following example calculates the cost of shipping for a sale transaction, using a default shipping cost for the product if the transaction itself does not specify a weight:

= 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
)

If we simply multiply product weight by shipping price, we get an empty cost for all the sales transactions without weight data because of the propagation of BLANK in multiplications.

When using variables, errors must be checked at the time of variable definition rather than where we use them. In fact, the first formula in the following code returns zero, the second formula always throws an error, and the last one produces different results depending on the version of the product using DAX (the latest version throws an error also):

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
)

The error happens when WrongValue is evaluated. Thus, the engine will never execute the IFERROR function in the second example, whereas the outcome of the third example depends on product versions. If you need to check for errors, take some extra precautions when using variables.

AVOID USING ERROR-HANDLING FUNCTIONS

Although we will cover optimizations later in the book, you need to be aware that error-handling functions might create severe performance issues in your code. It is not that they are slow in and of themselves. The problem is that the DAX engine cannot use optimized paths in its code when errors happen. In most cases, checking operands for possible errors is more efficient than using the error-handling engine. For example, instead of writing this:

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

It is much better to write this:

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

This second expression does not need to detect the error and is faster than the previous expression. This, of course, is a general rule. For a detailed explanation, see Chapter 19, “Optimizing DAX.”

Another reason to avoid IFERROR is that it cannot intercept errors happening at a deeper level of execution. For example, the following code intercepts any error happening in the conversion of the Table[Amount] column considering a blank value in case Amount does not contain a number. As discussed previously, this execution is expensive because it is evaluated for every row in Table.

SUMX (
    Table,
    IFERROR ( VALUE ( Table[Amount] ), BLANK () )
)

Be mindful that, due to optimizations in the DAX engine, the following code does not intercept the same errors intercepted by the preceding example. If Table[Amount] contains a string that is not a number in just one row, the entire expression generates an error that is not intercepted by IFERROR.

IFERROR (
    SUMX (
        Table,
        VALUE ( Table[Amount] )
    ),
    BLANK ()
)

ISERROR has the same behavior as IFERROR. Be sure to use them carefully and only to intercept errors raised directly by the expression evaluated within IFERROR/ISERROR and not in nested calculations.

Generating errors

Sometimes, an error is just an error, and the formula should not return a default value in case of an error. Indeed, returning a default value would end up producing an actual result that would be incorrect. For example, a configuration table that contains inconsistent data should produce an invalid report rather than numbers that are unreliable, and yet it might be considered correct.

Moreover, instead of a generic error, one might want to produce an error message that is more meaningful to the users. Such a message would help users find where the problem is.

Consider a scenario that requires the computation of the square root of the absolute temperature measured in Kelvin, to approximately adjust the speed of sound in a complex scientific calculation. Obviously, we do not expect that temperature to be a negative number. If that happens due to a problem in the measurement, we need to raise an error and stop the calculation.

In that case, this code is dangerous because it hides the problem:

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

Instead, to protect the calculations, one should write the formula like this:

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

This chapter is from the book