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
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.
Search JabSto ::

Custom Search