Microsoft Office Tutorials and References
In Depth Information
PowerPivot has a powerful type handling system so that you do not have to worry much about
data types: when you write a DAX expression, the resulting type is based on the type of the
terms used in the expression. You need to be aware of this in case the type returned from a
DAX expression is not the expected one: then you must investigate the data type of the terms
used in the expression itself. For example, if one of the terms of a sum is a date, the result is a
date too while, if the same operator is used with integers, the result is an integer. This is known
as operator overloading and you can see an example of its behavior in Figure 3-3, where the
DatePlusOneWeek column is calculated by adding 7 to the value in the Date column. The result
is, as we said, a date.
FIguRE 3-3 Adding an integer to a date results in a date increased by the corresponding number of days.
In addition to operator overloading, PowerPivot automatically converts strings into numbers
and numbers into strings whenever it is required by the operator. For example, if you use the
& operator, which concatenates strings, PowerPivot automatically converts its arguments into
strings. If you look at the formula
= 5 & 4
it returns a “54” string result. On the other hand, the formula
= "5" + "4"
returns an integer result with the value of 9.
As you have seen, the resulting value depends on the operator and not on the source columns,
which are converted following the requirements of the operator. But even if this behavior is
convenient, later in this chapter you see what types of errors might happen during these