Microsoft Office Tutorials and References
In Depth Information
It is obvious that a measure can refer to one or more calculated columns. It might be
less intuitive that the opposite is also true. A calculated column can refer to a measure:
in this way, it forces the calculation of a measure for the context defined by the current
row. This operation transforms and consolidates the result of a measure into a column,
which will not be influenced by user actions. Obviously, only certain operations can
produce meaningful results because usually a measure makes calculations that strongly
depend on the selection made by the user in the pivot table.
Handling Errors in DAX Expressions
Now that you have seen some basic formulas, it is time to learn how to gracefully handle
invalid calculations, in case 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 and is used in an arithmetic
operation, such as multiplication. You need to learn how these errors are handled by
default and how to intercept these conditions in case you want some special handling.
Before you learn how to handle errors, it is worth spending a few words on describing the
different kinds of errors that might appear during a DAX formula evaluation. They are
■ Conversion Error
■ Arithmetical operations
■ Empty or missing values
Let us explain them in more detail.
The first kind of error that we are going to analyze 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
"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"