Microsoft SQL Server 2012 Analysis Services: DAX Basics

Handling Errors in DAX Expressions

Now that you have seen some basic formulas, you learn how to handle invalid calculations gracefully if they happen. A DAX expression might contain invalid calculations because the data it references is not valid for the formula. For example, you might have a division by zero or a column value that is not a number but is used in an arithmetic operation, such as multiplication. You must learn how these errors are handled by default and how to intercept these conditions if you want some special handling.

Before you learn how to handle errors, the following list describes the different kinds of errors that might appear during a DAX formula evaluation. They are:

• Conversion errors

• Arithmetical operations errors

• Empty or missing values

The following sections explain them in more detail.

Conversion Errors

The first kind of error is the conversion error. As you have seen before in this chapter, DAX values are automatically converted between strings and numbers whenever the operator requires it. To review the concept with examples, all these 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. What about the following expression?

SalesOrders[VatCode] + 100

Because the first operator of this sum is obtained by a column (which, in this case, is a text column), you must be sure that all the values in that column are numbers to determine whether they will be converted and the expression will be evaluated correctly. If some of the content cannot be converted to suit the operator needs, you will incur a conversion error. Here are typical situations.

"1 + 1" + 0 = Cannot convert value '1+1' of type string to type real

DATEVALUE("25/14/2010")  = Type mismatch

To avoid these errors, you must write more complex DAX expressions that contain error detection logic to intercept error conditions and always return a meaningful result.

Arithmetical Operation Errors

The second category of errors is arithmetical operations, such as division by zero or the square root of a negative number. These kinds of errors are not related to conversion; they are raised whenever you try to call a function or use an operator with invalid values.

Division by zero, in DAX, requires special handling because it behaves in a way that is not very intuitive (except for mathematicians). When you divide a number by zero, DAX usually returns the special value Infinity. Moreover, in the very special cases of 0 divided by 0 or Infinity divided by Infinity, DAX returns the special NaN (not a number) value. These results are summarized in Table 4-2.

Table 4-2 Special Result Values for Division by Zero

Note that Infinity and NaN are not errors but special values in DAX. In fact, if you divide a number by Infinity, the expression does not generate an error but returns 0.

9954 / (7 / 0) = 0

Apart from this special situation, arithmetical errors might be returned when calling a DAX function with a wrong 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. You can use the special ISERROR function to check whether an expression leads to an error, something that you use later in this chapter. Finally, even if special values such as NaN are displayed correctly in the SSDT window, they show as errors in an Excel PivotTable, and they will be detected as errors by the error detection functions.

Empty or Missing Values

The third category of errors is not a specific error condition but the presence of empty values, which might result in unexpected results or calculation errors.

DAX handles missing values, blank values, or empty cells by a special value called BLANK. BLANK is not a real value but a special way to identify these conditions. It is the equivalent of NULL in SSAS Multidimensional. The value BLANK can be obtained 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.

= BLANK()

On its own, this expression is useless, but the BLANK function itself becomes useful every time you want to return or check for an empty value. For example, you might want to display an empty cell instead of 0, as in the following expression, which calculates the total discount for a sale transaction, leaving the cell blank if the discount is 0.

= IF( Sales[DiscountPerc] = 0, BLANK(), Sales[DiscountPerc] * Sales[Amount] )

If a DAX expression contains a blank, it is not considered an error—it is considered an empty value. So an expression containing a blank might return a value or a blank, depending on the calculation required. For example, the following expression

= 10 * Sales[Amount]

returns BLANK whenever Sales[Amount] is BLANK. In other words, the result of an arithmetic product is BLANK whenever one or both terms are BLANK. This propagation of BLANK in a DAX expression happens in several other arithmetical and logical operations, as you can see in the following examples.

BLANK() + BLANK()     = BLANK()
10 * BLANK()          = BLANK()
BLANK() / 3           = BLANK()
BLANK() / BLANK()     = BLANK()
BLANK() || BLANK()    = FALSE
BLANK() && BLANK()    = FALSE

However, the propagation of BLANK in the result of an expression does not happen for all formulas. Some calculations do not propagate BLANK but return a value depending on the other terms of the formula. Examples of these are addition, subtraction, division by BLANK, and a logical operation between a blank and a valid value. In the following expressions, you can see some examples of these conditions along with their results.

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

Understanding the behavior of empty or missing values in a DAX expression and using BLANK() to return an empty cell in a calculated column or in a measure are important skills to control the results of a DAX expression. You can often use BLANK() as a result when you detect wrong values or other errors, as you learn in the next section.

Intercepting Errors

Now that you have seen the various kinds of errors that can occur, you can learn a technique to intercept errors and correct them or, at least, show an error message with some meaningful information. The presence of errors in a DAX expression frequently depends on the value contained in tables and columns referenced in the expression itself, so you 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 message or a default value. A few DAX functions have been designed for this.

The first of them is the IFERROR function, which is very similar to the IF function, but instead of evaluating a TRUE/FALSE condition, it checks whether an expression returns an error. You can see two typical uses of the IFERROR function here.

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

In the first expression, if either Sales[Quantity] or Sales[Price] are strings that cannot be converted into a number, the returned expression is BLANK; otherwise the product of Quantity and Price is returned.

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

When you use IFERROR this way, you follow a more general pattern that requires the use of ISERROR and IF. The following expressions are functionally equivalent to the previous ones, but the usage of IFERROR in the previous ones makes them shorter and easier to understand.

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

You should use IFERROR whenever the expression that has to be returned is the same as that tested for an error; you do not have to duplicate the expression, and the resulting formula is more readable and safer in case of future changes. You should use IF, however, when you want to return the result of a different expression when there is an error.

For example, the ISNUMBER function can detect whether a string (the price in the first line) can be converted to a number and, if it can, calculate the total amount; otherwise, a BLANK can be returned.

= IF( ISNUMBER( Sales[Price] ), Sales[Quantity] * Sales[Price], BLANK() )
= IF( Test[Omega] >= 0, SQRT( Test[Omega] ), BLANK() )

The second example detects whether the argument for SQRT is valid, calculating the square root only for positive numbers and returning BLANK for negative ones.

A particular case is the test against an empty value, which is called BLANK in DAX. The ISBLANK function detects an empty value condition, returning TRUE if the argument is BLANK. This is especially important when a missing value has a meaning different from a value set to 0. In the following example, you calculate the cost of shipping for a sales transaction by using a default shipping cost for the product if the weight is not specified in the sales transaction itself.

= IF( ISBLANK( Sales[Weight] ),
      RELATED( Product[DefaultShippingCost] ),
      Sales[Weight] * Sales[ShippingPrice] )

If you had just multiplied product weight and shipping price, you would have an empty cost for all the sales transactions with missing weight data.