Microsoft Office Tutorials and References
In Depth Information
In the second expression, the result is an empty cell every time the Test[Omega] column
contains a negative number.
When you use IFERROR this way, you follow a more general pattern that requires the use of
ISERROR and IF:
= IF( ISERROR( Sales[Quantity] * Sales[Price] ), BLANK(), Sales[Quantity] * Sales[Price] )
= IF( ISERROR( SQRT( Test[Omega] ) ), BLANK(), SQRT( Test[Omega] ) )
Of course, you ought to use IFERROR whenever the expression that has to be returned is the
same tested for an error: you do not have to duplicate the expression in two places, and the
resulting formula is more readable and safe in case of future changes. You should use IF, on
the other hand, when you anticipate the existence of an error of an unwanted condition.
For example, the ISNUMBER can be used to detect whether a string (the price in the first line)
can be converted to a number and in that case calculate the total amount; otherwise, an empty
cell can be returned.
= IF( ISNUMBER( Sales[Price] ), Sales[Quantity] * Sales[Price], BLANK() )
= IF( Test[Omega] >= 0, SQRT( Test[Omega] ), BLANK() )
The second example simply detects whether the argument for SQRT is valid or not, calculating
the square root only for positive numbers and returning BLANK for negative ones.
A particular case is the test against an empty value, which is called BLANK in DAX. The ISBLANK
function detects an empty value condition, returning TRUE if the argument is BLANK. This is
important especially when a missing value has a meaning different from a value set to 0. In
the following example, we calculate the cost of shipping for a sales transaction, using a default
shipping cost for the product if the weight is not specified in the sales transaction itself:
= IF( ISBLANK( Sales[Weight] ),
RELATED( Product[DefaultShippingCost] ),
Sales[Weight] * Sales[ShippingPrice] )
If we had just multiplied product weight and shipping price, we would have an empty cost
for all the sales transactions with missing weight data.
Common DAX Functions
Now that you have seen the fundamentals of DAX and how to handle error conditions, let
us take a brief tour through the most commonly used functions and expressions of DAX.
Writing a DAX expression is often similar to writing an Excel expression because many
functions are similar, if not identical. Excel users often find using PowerPivot very intuitive,
thanks to their previous knowledge of Excel. In the remaining part of this chapter, you see
some of the most frequently used DAX functions, which you are likely to use to build your
own PowerPivot data models.