Microsoft Office Tutorials and References

In Depth Information

These formulas are always correct because they operate with constant values. But what about

the following one?

SalesOrders[VatCode] + 100

Because the first operator of this sum is obtained by another DAX expression, you need to be

sure that all the values in that column are numbers to determine whether it will be converted

correctly. If the content of a column 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 need to write more complex DAX expressions that contain error

detection logic to intercept error conditions and always return a meaningful result.

Arithmetical Operations

The second category of errors that we want to analyze is that of arithmetical operations, such

as the 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.

The division by zero, in PowerPivot, requires a special handling because it behaves in a way that

is not very intuitive (except for mathematicians). When you divide a number by zero, PowerPivot

usually returns the special value
Infinity
. Moreover, in the
very
special cases of 0 divided by 0 or

Infinity divided by Infinity, PowerPivot returns the special NaN (not a number) value. Because this

is a strange behavior for Excel users to encounter, it is worth summarizing in Table 3-2.

TABLE 3-2
Special result values for division by zero.

Expression

Result

10 / 0

Infinity

7 / 0

Infinity

0 / 0

Infinity

(10 / 0) / (7 / 0)

NaN

It is important to note that Infinity and NaN are not errors but special values in PowerPivot. 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