Microsoft Office Tutorials and References

In Depth Information

Cross References

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.

Conversion Errors

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

DAX expressions:

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