Microsoft Office Tutorials and References

In Depth Information

You might be wondering whether ISNUMBER can be used with a text column just to check

whether a conversion to a number is possible. Unfortunately, you cannot use this approach;

if you want to test whether a text value can be converted to a number, you need to try the

conversion and handle the error if it fails. To get a TRUE result from the ISERROR function,

for example, we tried to add a zero to the Price, to force the conversion from a Text value to

a number. The conversion fails for the N/A price value, so you can see that ISERROR is TRUE

for that row only.

Mathematical Functions

The set of mathematical functions available in DAX is very similar to the same set in Excel, with

the same syntax and behavior. You can see the complete list of these functions and their syntax

in Table A-4 of the Appendix. The mathematical functions of common use are ABS, EXP, FACT,

LN, LOG, LOG10, MOD, PI, POWER, QUOTIENT, SIGN, and SQRT. Random functions are RAND

and RANDBETWEEN. Finally, there are several functions to round numbers that deserve an

example; in fact, you might use several approaches to get the same result. Consider these

calculated columns, along with their results in Figure 3-23:

FLOOR = FLOOR( Tests[Value], 0.01 )

TRUNC = TRUNC( Tests[Value], 2 )

ROUNDDOWN = ROUNDDOWN( Tests[Value], 2 )

MROUND = MROUND( Tests[Value], 0.01 )

ROUND = ROUND( Tests[Value], 2 )

CEILING = CEILING( Tests[Value], 0.01 )

ROUNDUP = ROUNDUP( Tests[Value], 2 )

INT = INT( Tests[Value] )

FIguRE 3-23
Summary of different rounding functions.

As you can see, FLOOR, TRUNC, and ROUNDDOWN are very similar, except in the way you can

specify the number of digits to round on. In the opposite direction, CEILING and ROUNDUP

are very similar in their results. You can see a few differences in the way the rounding is done

(see row B, where 1.265 number is rounded in two different ways on the second decimal digit)

between MROUND and ROUND function. Finally, it is important to note that FLOOR and

MROUND functions do not operate on negative numbers, whereas other functions do.