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.