Microsoft Office Tutorials and References

In Depth Information

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 OR BLANK = FALSE

FALSE AND BLANK = FALSE

TRUE OR BLANK = TRUE

TRUE AND BLANK = TRUE

Empty Values in Excel

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 multiplication, but they return an error

if they are part of division or of a logical expression.

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 also 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 are going to learn in the next section.

Intercepting Errors

Now that you have seen the various kinds of errors that can occur, it is time to 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. To perform this operation, you use a few DAX

functions that 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 IFERRROR 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 an empty cell; otherwise the product

of Quantity and Price is returned.