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
Arithmetic operations errors
Empty or missing values
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
10 / 0
7 / 0
0 / 0
(10 / 0) / (7 / 0)
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.
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.
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." ) )