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.
Search JabSto ::

Custom Search