IFERROR
An expression's value, or another value when it gives an error (an infinite number counts as one).
Group: Logical. Compatible with DAX.
Syntax
IFERROR ( <value>, <value_if_error> )| Parameter | Type | Description |
|---|---|---|
value | Value | The expression to try. |
value_if_error | Value | The result when the expression fails. |
Returns
Same type as its values. The expression's value, or value_if_error when it fails.
Remarks
- Dividing by zero is not an error in OAX: it gives infinity, or NaN for 0 / 0. IFERROR counts both as errors, as Power BI does.
- A blank is not an error:
IFERROR ( BLANK (), 0 )is blank. - For a division,
DIVIDE ( a, b, alternate )says the same more plainly; for blanks,COALESCE. - Comparing values that cannot be compared, a number with a text, stops the query before IFERROR has a chance to catch it.
Examples
What counts as an error
EVALUATE
ROW (
"1 / 0", IFERROR ( 1 / 0, -1 ),
"0 / 0", IFERROR ( 0 / 0, -1 ),
"Text as a number", IFERROR ( VALUE ( "abc" ), -1 ),
"5", IFERROR ( 5, -1 ),
"Blank", IFERROR ( BLANK (), -1 )
)| 1 / 0 | 0 / 0 | Text as a number | 5 | Blank |
|---|---|---|---|---|
| -1 | -1 | -1 | 5 | (blank) |
Codes that may not be numbers
EVALUATE
VAR Codes = DATATABLE ( "Code", STRING, { { "12" }, { "40" }, { "n/a" } } )
RETURN
ADDCOLUMNS ( Codes, "Number", IFERROR ( VALUE ( [Code] ), BLANK () ) )| Code | Number |
|---|---|
| 12 | 12 |
| 40 | 40 |
| n/a | (blank) |