Microsoft Office Tutorials and References
In Depth Information
Almost all of the text functions available in DAX are similar to those available in Excel, with only
a few exceptions, which are noted in the Appendix. Table A-5 of the Appendix contains a
complete description of the text functions available in DAX: they are CONCATENATE, EXACT, FIND,
FIXED, FORMAT, LEFT, LEN, LOWER, MID, REPLACE, REPT, RIGHT, SEARCH, SUBSTITUTE, TRIM,
UPPER and VALUE. These functions are useful for manipulating text and extracting data from
strings that contain multiple values. For example, in Figure 3-24, you can see an example of the
extraction of first and last name from a string containing these values separated by commas,
with the title in the middle that we want to remove.
We start calculating the position of the two commas and then we use these numbers to
extract the right part of the text. The SimpleConversion column implements a formula that
might return wrong values if there are fewer than two commas in the string (and it raises
an error if there are no commas at all), whereas the FirstLastName column implements a
more complex expression that does not fail in case of missing commas.
Comma1 = IFERROR( FIND( ",", People[Name] ), BLANK() )
Comma2 = IFERROR( FIND( ",", People[Name], People[Comma1] + 1 ), BLANK() )
SimpleConversion = MID( People[Name], People[Comma2] + 1, LEN(People[Name]) )
& " " & LEFT( People[Name], People[Comma1]-1 )
FirstLastName = TRIM( MID( People[Name], IF( ISNUMBER( People[Comma2] ),
People[Comma1] ) + 1, LEN(People[Name]) ) )
& IF( ISNUMBER( People[Comma1] ),
" " & LEFT( People[Name], People[Comma1] - 1 ),
FIguRE 3-24 Extracting first and last names using text functions.
As you can see, the FirstLastName column is defined by a long DAX expression, but you must
use it to avoid possible errors that would propagate to the whole column if even a single value
generated an error.
Date and Time Functions
Almost in every type of data analysis, handling time and date is an important part of the job.
PowerPivot has a large number of functions that operate on date and time. Some of them
correspond to similar functions in Excel and make simple transformations to and from a datetime
data type, like the ones described in Table A-6 of the Appendix. These are DATE, DATEVALUE,
DAY, EDATE, EOMONTH, HOUR, MINUTE, MONTH, NOW, SECOND, TIME, TIMEVALUE, TODAY,