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