Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
304 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
The first formula works—only if there’s no region, it looks a little strange (for example,
UK-). Unlike SQL Server (unless the CONCAT_NULL_YIELDS_NULL database option
is set to OFF), PowerPivot treats nulls as blanks, so adding a null region to a country
does not return null. The second formula produces a more pleasing result and is the
one shown in the preceding illustration. If the region is blank (probably because it’s
originally a null value in the source data), then the hyphen (-) is not appended to the
country. The second example has a nested CONCATENATE(). The position of the
nested CONCATENATE() is different from that in the previous set of examples, where it
appears immediately after the outer CONCATENATE(). Both varieties work.
The second example here is a lengthy formula. As such, part of it may scroll out of
view as you edit. There are two tricks that are quite handy in these situations. One,
you can increase the height of the formula bar (for calculated columns) or the Formula
text box (for measures) by dragging. Two, you can force your formula to word-wrap by
pressing alt-enter.
EXACT()
EXACT() compares two text values and returns either TRUE or FALSE. The first
formula is an example. The second example shows an alternative approach (using
an equality test) that produces the same result. More useful is the third formula, or
its equivalent in the fourth formula. The examples are calculated columns in the
Northwind Orders table.
Syntax
=EXACT('Orders'[CustomerID],"QUICK")
=IF('Orders'[CustomerID] = "QUICK", TRUE(), FALSE())
=IF(EXACT('Orders'[CustomerID],"QUICK"), "QUICK-Stop", "Others")
=IF('Orders'[CustomerID] = "QUICK", "QUICK-Stop", "Others")
Chapter 10: Text Functions 305
Result
Analysis
The result is from the third formula. This enables you to slice to see values or measures
associated with the customer “QUICK-Stop” and to compare with those from all
other customers. A total of 830 orders from a maximum of 91 customers have been
summarized into 830 orders from a maximum of two customers/customer groups.
Instead of 91 choices in a slicer, you have only 2. EXACT() is case-sensitive as in the
third example. The fourth example is not case-sensitive.
FIND()
FIND() and its related function, SEARCH(), are used to see if one text value occurs
within another text value. The difference between the two functions is due to case
sensitivity. The examples are calculated columns on any table.
Syntax
=FIND("e","bottles",1)
=FIND("E","bottles",1)
Result
306 Practical PowerPivot & DAX Formulas for Excel 2010
Analysis
FIND(), unlike SEARCH(), is case-sensitive. Unfortunately, it only accepts literal text
values for its two parameters and can’t work directly against table columns. The number
returned is the start position of the text in the first parameter. The final parameter is the
start position for the search. The result is from the first example. The second example
returns an error, as the string is not found.
FIXED()
This time, we have a text function used in a measure rather than in a calculated column.
FIXED() is handy for formatting numbers with a specified number of decimal places
and with or without a thousands separator. The example is a calculated measure in a
pivot table, although FIXED() can also be used in a calculated column.
Syntax
=FIXED(SUM('Order Details'[Sales Amount]),2,0)
Result
Chapter 10: Text Functions 307
Analysis
The second parameter to the formula is the number of decimal places. The third
parameter is for stipulating thousands separators (either 0 or 1). Counterintuitively, a
value of 1 will suppress thousands separators. Although FIXED() is a text function,
it accepts a number as the first parameter. It’s a text function, as it returns a text
value. However, it can still serve as an additive measure in the value section of a pivot
table because PowerPivot will automatically convert it back into a number (unless
it’s passed as a parameter to a function expecting a numeric parameter), but keeping
the formatting you specify. Unlike with a pure number, PowerPivot will left-align the
result—you’ll have to manually right-align it (right-click, Format Cells | Alignment |
Text Alignment | Horizontal | Right (Indent)). You can achieve the same format result
with the FORMAT() function, which is far more versatile. FORMAT() is covered next.
FORMAT()
There are five examples of the FORMAT() function here. Unlike FIXED(), it can
format date/time values as well as numeric values. Here the FORMAT() function is part
of a measure rather than a calculated column. The examples are calculated measures in
a pivot table.
Syntax
=FORMAT('Order Details'[Sum of Sales Amount],"Currency")
=FORMAT(SUM('Order Details'[Sales Amount]),"Currency")
=FORMAT(SUM('Order Details'[Sales Amount]),"$#,###.00")
=FORMAT(SUM('Order Details'[Sales Amount]),"£#,###.00")
=FORMAT(SUM('Order Details'[Sales Amount]),"#,###.00 ")
308 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
The result is from the last example. The first two formulas set the currency symbol
implicitly; Currency is a predefined format, based on your Windows regional settings.
The currency symbol is explicitly nominated in the third, fourth, and fifth examples.
These last three are custom formats—a custom format gives you a lot of control and
you are referred to your DAX help for a full list of all the possibilities for formatting
numbers and dates.
The second parameter is the format string. In our case, there is only one entry in the
format string. It will apply to positive and negative numbers and zero. If you have two
entries in the format string (separated by semicolons [;]), the first applies to positive
and zero values and the second to negative values. With three entries, the first is for
positive numbers, the second for negative, and the third for zeros.
Chapter 10: Text Functions 309
LEFT()
LEFT() is pretty straightforward and probably self-explanatory if you have used Excel,
Visual Basic for Applications (VBA), or SQL before. Unlike the Excel version, it
can operate against columns (as shown here) as well as against literal text or numeric
values—as a reminder, the DAX functions don’t operate on worksheet cells. The
example is a calculated column in the Customers table.
Syntax
=LEFT('Customers'[Country],3) & "-" & 'Customers'[CustomerID]
Result
Analysis
The formula is creating a new identifier for each customer. This new identifier is the
old primary key prefixed by the first three letters of the country and a hyphen. If you
add this to your pivot table rows, you can right-click to sort on country and old primary
key. I’ve been a little lazy here—the concatenation is done with ampersands rather than
a nested CONCATENATE().
LEN()
Again, the LEN() function is very similar to the old Excel equivalent, except it can
accept a column as a parameter. The example is a Products table calculated column.
Syntax
=LEN('Products'[QuantityPerUnit])
310 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
The returned length of the QuantityPerUnit column includes any embedded spaces.
Each character counts as 1, even if your data is in 2-byte Unicode.
LOWER()
The LOWER() function simply converts a string or a column containing a text value
into lowercase. The example is a Products table calculated column.
Syntax
=LOWER('Products'[ProductName])
Result
Chapter 10: Text Functions 311
Analysis
There’s nothing very sophisticated here. Often, the text functions are used in
combination to provide meaningful manipulation of data. Any good book on Excel
functions and formulas will contain lots of interesting examples—but please remember
that the DAX versions of the Excel functions can usually accept columns as input
parameters.
MID()
The MID() function strips out a substring from a text column or text value (it also
works on numeric columns and values). The example is a Products table calculated
column.
Syntax
=MID('Products'[ProductName],2,3)
Result
Analysis
The result is a three-character string starting at the second character of the
ProductName column.
312 Practical PowerPivot & DAX Formulas for Excel 2010
REPLACE()
The REPLACE() function is potentially more useful! I don’t think you’ll be too surprised
if the REPLACE() function “replaces” some existing text. There is also a SUBSTITUTE()
function that replaces text, but it does so in a different way. SUBSTITUTE() is covered
shortly. The example is a Customers table calculated column.
Syntax
=REPLACE('Customers'[CustomerID],4,2,"XXX")
Result
Analysis
Here’s an example of how to create a new identifier for each customer. The formula
replaces two characters starting from position 4 with the characters XXX.
REPT()
The REPT() function repeats a nominated string a number of times. The examples are
Customers table calculated columns.
Syntax
='Customers'[CustomerID] & REPT(".",3)
=LEFT('Customers'[CustomerID],3) & REPT("X",3)
Chapter 10: Text Functions 313
Result
Analysis
The first example adds three trailing dots to the CustomerID column, the result shown.
The second one has the same result as the previous example formula for REPLACE().
RIGHT()
The RIGHT() function returns a specified number of characters from the end of a text
column or literal string value (trailing spaces count as characters). The example is a
Customers table calculated column.
Syntax
=RIGHT('Customers'[CustomerID],3) & LEFT('Customers'[CustomerID],2)
Result