IF
One value when a condition is TRUE, another (or BLANK) when it isn't.
Group: Logical. Compatible with DAX.
Syntax
IF ( <logical_test>, <value_if_true> [, <value_if_false>] )| Parameter | Type | Description |
|---|---|---|
logical_test | Boolean | The condition. |
value_if_true | Value | The result when the condition is TRUE. |
value_if_false | Value optional | The result when it is not; BLANK when left out. |
Returns
Same type as its values. One of the two results.
Remarks
- A blank condition counts as FALSE, and a number as TRUE unless it is 0. Text is an error.
- Only the result that is chosen is evaluated, so the other may hold something that would fail on this row.
- Without value_if_false the result is BLANK, which leaves the row out of a visual.
- When the two results are numbers of different kinds, the answer comes back as the wider one:
IF ( TRUE (), DATE ( 2021, 1, 1 ), 1 )is the number 44197, not a date. A number and a text are left as they are, so one column can hold both. - For more than two outcomes,
SWITCH ( TRUE (), ... )reads better than one IF inside another.
Examples
Big and small lines
EVALUATE
VAR Lines = DATATABLE ( "Liters", INTEGER, { { 40 }, { 250 }, { 1200 }, { 0 } } )
RETURN
ADDCOLUMNS ( Lines, "Size", IF ( [Liters] >= 100, "Big", "Small" ) )| Liters | Size |
|---|---|
| 40 | Small |
| 250 | Big |
| 1200 | Big |
| 0 | Small |
Without the third argument
EVALUATE
VAR Lines = DATATABLE ( "Liters", INTEGER, { { 40 }, { 250 }, { 1200 }, { 0 } } )
RETURN
ADDCOLUMNS ( Lines, "Only big", IF ( [Liters] >= 100, [Liters] ) )| Liters | Only big |
|---|---|
| 40 | (blank) |
| 250 | 250 |
| 1200 | 1200 |
| 0 | (blank) |
A date and a number
One result is a date, the other a number: both come back as numbers.
EVALUATE
ROW ( "Result", IF ( TRUE (), DATE ( 2021, 1, 1 ), 1 ) )| Result |
|---|
| 44197 |