Microsoft Office Tutorials and References
In Depth Information
If PowerPivot detects errors like this, it blocks any further computation of the DAX expression
and raises an error. You can use the special DAX ISERROR function to check if an expression leads
to an error, something that we are going to use later in this chapter. Finally, even if special values
as NaN are displayed in such a way in the PowerPivot window, they are displayed as errors when
shown in an Excel PivotTable and will be detected as errors by the error detection functions.
Empty or Missing Values
The third category that we examine is not a specific error condition, but the presence of empty
values, which might result in unexpected results or calculation errors. You need to understand
how these special values are treated in PowerPivot.
DAX handles missing values, blank values, or empty cells in the same way, using the value
BLANK, which is not a real value but a special way to identify these conditions. The value BLANK
can be obtained in a DAX expression by calling the BLANK function, which is different from
an empty string. For example, the following expression always returns a blank value, which is
displayed as an empty cell in the PowerPivot window:
= BLANK()
The expression above is useless, but the BLANK function itself becomes useful every time you
want to return an empty value. For example, you might want to display an empty cell instead
of 0, as in the following expression that calculates the total discount for a sale transaction,
leaving the cell blank if the discount is 0:
= IF( Sales[DiscountPerc] = 0, BLANK(), Sales[DiscountPerc] * Sales[Amount] )
If a DAX expression contains a BLANK, it is not considered an error but an empty value. So an
expression containing a BLANK might return a value or a blank, depending on the calculation
required. For example, the following expression
= 10 * Sales[Amount]
returns BLANK whenever Sales[Amount] is BLANK. In other words, the result of an arithmetic
product is BLANK whenever one or both terms are BLANK. This propagation of BLANK in a
DAX expression happens in several other arithmetical and logical operations, as you can see in
the following examples:
BLANK() + BLANK() = BLANK()
10 * BLANK() = BLANK()
BLANK() / 3 = BLANK()
BLANK() / BLANK() = BLANK()
BLANK() OR BLANK() = BLANK()
BLANK() AND BLANK() = BLANK()
However, the propagation of BLANK in the result of an expression does not happen for all
formulas. Some calculations do not propagate BLANK but return a value depending on the
Search JabSto ::




Custom Search