Microsoft Office Tutorials and References
In Depth Information
function: the Price column is of type Text because it contains a N/A value, and the Amount
and CheckedAmount columns are calculated using the following formulas:
Amount = Sales[Quantity] * Sales[Price]
CheckedAmount = IFERROR( Sales[Quantity] * Sales[Price], BLANK() )
FIguRE 3-21 Using IFERROR to avoid conversion errors.
The Amount column returns an error because of the N/A value contained in the Gadget row
because even if a single row generates a calculation error, the error is propagated to the whole
column. The formula used in CheckedAmount column, on the other hand, intercepts the error
and replaces it with a blank value.
Whenever you need to analyze the type of an expression, you can use one of the
information functions that are listed in Table A-3 of the Appendix. All of these functions return a
TRUE/FALSE value and can be used in any logical expression. They are: ISBLANK, ISERROR,
ISLOGICAL, ISNONTEXT, ISNUMBER, and ISTEXT.
It is important to note that when a table column is passed as a parameter, the functions
ISNUMBER, ISTEXT, and ISNONTEXT always returns TRUE or FALSE, depending on the data
type of the column and on the empty condition of each cell. In Figure 3-22, you can see
how the column Price (which is of Text type) affects the result of these calculated columns:
ISBLANK = ISBLANK( Sales[Price] )
ISNUMBER = ISNUMBER( Sales[Price] )
ISTEXT = ISTEXT( Sales[Price] )
ISNONTEXT = ISNONTEXT( Sales[Price] )
ISERROR = ISERROR( Sales[Price] + 0 )
FIguRE 3-22 Results from information functions are based on column type.