# Introducing DAX

- By Alberto Ferrari and Marco Russo
- 7/20/2019

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

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.

### 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." ) )