Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
314 Practical PowerPivot & DAX Formulas for Excel 2010
Analysis
Here we are jiggling with the customer identifier.
SEARCH()
The SEARCH() function is the same as FIND() apart from case sensitivity. The
examples are calculated columns on any table.
Syntax
=SEARCH("e","bottles",1)
=SEARCH("E","bottles",1)
Result
Analysis
SEARCH(), just like FIND(), only works on literal values. It’s not possible to search a
column—if you try, you’ll receive an error. The third parameter is the start position for
the search. SEARCH() is not case-sensitive, so both examples work.
Chapter 10: Text Functions 315
SUBSTITUTE()
The SUBSTITUTE() function is slightly different from the REPLACE() function. The
example is a Products table calculated column.
Syntax
=SUBSTITUTE('Products'[QuantityPerUnit],"oz","ounce",1)
Result
Analysis
Instead of a number of characters to replace (as in the REPLACE() function),
you specify a replacement string. Here we are changing “oz” to “ounce.” The third
parameter is the number of the occurrence to replace (or all occurrences if omitted);
SUBSTITUTE() is case-sensitive.
TRIM()
Use TRIM() to remove leading and trailing space characters. The example is a Customers
table calculated column.
Syntax
=TRIM('Customers'[Country])
316 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
Leading (and trailing) spaces can cause problems sorting in your pivot tables, so
it’s often a good idea to employ TRIM(). It does not remove embedded spaces, nor
does it remove the special Unicode non-breaking-space character. The example here
is somewhat artificial, as there are no leading or trailing spaces—it’s for illustration
purposes only.
UPPER()
This function converts a column or string literal into uppercase. The example is a
Customers table calculated column.
Syntax
=UPPER('Customers'[Country])
Result
Chapter 10: Text Functions 317
Analysis
This function might be quite handy for display purposes in your pivot tables and charts.
VALUE()
VALUE() is used to convert text into a number. It accepts both literals and column
references. It also works against dates. The text, of course, must be a valid number—“123”
will work while “x123” will not. The examples are Products table calculated columns.
Syntax
=VALUE(LEFT('Products'[QuantityPerUnit],1))
=VALUE(LEFT('Products'[QuantityPerUnit],2))
Result
Analysis
The second formula will produce an error. That’s because the second character for the
QuantityPerUnit column for the product Queso Cabrales is a space character, which can’t
be cast as a number. It’s quite likely you won’t ever need the VALUE() function. By default,
PowerPivot will attempt to convert text into numbers where appropriate. This is true in
general. However, this is not always the case with measures imported from an SSAS cube.
If a cube measure is used in a DAX function, it may well be converted. If a cube measure
is added to the Values drop-zone in a pivot table, it may not be converted. Implicit data
conversion on cube measures (from text to numeric) may not work as cube numeric
measures can contain non-numeric values (as a result of a cube calculated measure or an
MDX Scope statement in a cube script). The first example can probably be left as
=LEFT('Products'[QuantityPerUnit],1)
This page intentionally left blank
Math & Trig Functions
Chapter 11
320 Practical PowerPivot & DAX Formulas for Excel 2010
T
his is the final chapter dealing with DAX functions and formulas. It deals
with the Math & Trig functions. However, you won’t find any trigonometric
functions as such—expect these to appear in a later version of PowerPivot for
Excel. Some of the functions, for example, EXP() and LN(), are probably for specialized
use only. However, many of the others are going to be very popular—particularly, the
eight functions devoted to rounding numbers. SUM() and SUMX() are Math & Trig
functions, although they have much in common with the Statistical functions as well.
They were both covered in an earlier chapter on aggregate functions (that is, Statistical
functions with SUM() and SUMX()), but are mentioned here again for completeness. In
any case, SUMX() is worth a second look, as it will be very useful in many BI situations.
C
Key concepts Arithmetic manipulation, exponents and logarithms, rounding up
and/or down, summing data
C
Keywords ABS(), CEILING(), EXP(), FACT(), FLOOR(), INT(), ISO.
CEILING(), LN(), LOG(), LOG10(), MOD(), MROUND(), PI(), POWER(),
QUOTIENT(), RAND(), RANDBETWEEN(), ROUND(), ROUNDDOWN(),
ROUNDUP(), SIGN(), SQRT(), SUM(), SUMX(), TRUNC()
ABS()
ABS() simply converts a negative number into a positive one, and leaves positive
numbers unchanged. The example uses the Products table.
Syntax
=ABS(Products[UnitsInStock]-Products[ReorderLevel])
Result
Chapter 11: Math & Trig Functions 321
Analysis
The result of UnitsInStock minus ReorderLevel for the product Chang is negative.
ABS() has converted this to a positive value.
CEILING()
The CEILING() function rounds a number up to the nearest multiple of significance.
The second parameter is the unit of significance. If both parameters are negative,
CEILING() will round down—this distinguishes CEILING() from ISO.CEILING().
The example uses the Products table.
Syntax
=CEILING(Products[UnitPrice],1)
=CEILING(Products[UnitPrice],.1)
Result
Analysis
The result shown is from the second example. If you look at Chef Anton’s Gumbo
Mix, the price has been rounded up to the nearest tenth of a pound (or dollar or euro,
depending upon your Control Panel regional settings)—here it’s been changed from
21.35 to 21.4.
322 Practical PowerPivot & DAX Formulas for Excel 2010
EXP()
EXP() takes just one parameter, which is the power to which to raise the exponent e
(2.71828182845904). The example can be used in any table or as part of a measure.
Syntax
=EXP(2)
Result
Analysis
The result (7.38905609893065) is e squared. This is a useful function if you are engaged
in scientific or mathematical work. EXP() is the inverse of LN().
FACT()
If you want the factorial of a number, then use FACT(). The example can be used in
any table or as part of a measure.
Syntax
=FACT(5)
Chapter 11: Math & Trig Functions 323
Result
Analysis
The answer, 120, is 5 multiplied by 4 multiplied by 3 multiplied by 2 multiplied by 1.
FACT() has lots of applications, including in statistics.
FLOOR()
As you might expect, FLOOR() is the opposite of CEILING(). It rounds numbers
down to the unit of significance, which is the second parameter. The example uses the
Products table.
Syntax
=FLOOR(Products[UnitPrice],.1)