IF
Returns one value when a test is true and another value when it is false.
IF(<logical_test>, <value_if_true> [, <value_if_false>])Returns: The value chosen by the test (a scalar). If the false value is left out and the test is false, the result is BLANK.
What it does
IF tests a condition and returns one of two results. It is the basic building block of conditional logic in DAX: flag an invoice as overdue, label a variance as favorable or unfavorable, or choose a measure based on a threshold. The test is evaluated in the current context, so the answer can change in every cell of a matrix.
Syntax and arguments
- logical_test: an expression that is true or false, such as
[Gross Margin %] < 0.2. - value_if_true: the result when the test is true.
- value_if_false (optional): the result when the test is false. If it is left out and the test is false, the result is BLANK, not zero.
Example
The CFO wants each product category flagged when its gross margin falls below the company target of 20 percent.
Margin Status =
IF(
[Gross Margin %] < 0.2,
"Below target",
"On target"
)
The label is a text value, so it can go in a table or a tooltip. For a number for the chart, use a separate measure that returns 1 or 0, which sums and filters more easily.
Example: a second, different use
Favorable or unfavorable budget variance, where a positive variance means spending came in under budget:
Variance Label =
IF(
[Actual Cost] <= [Budget Cost],
"Favorable",
"Unfavorable"
)
The test compares two measures, so the label flips on the matrix cell that is over budget. Use this in a conditional format rule to color the cell.
Things to watch
- A missing false branch returns BLANK. Write the false branch when you need a value in every case.
- Nested IF statements become hard to read after two or three levels. Use SWITCH for a list of conditions.
- IF returns a value in each branch only as needed, but the test must be true or false. To act on a missing value, test it with ISBLANK, because a blank is treated as zero in a comparison.
- In a measure, the test sees the filter context of the cell. In a calculated column, it sees the row. Know which one you are writing.
- IF returns a value, it does not change the data. For a lookup that returns a value, consider LOOKUPVALUE or RELATED.
In business terms
IF drives the flags and labels in management reports: over budget or under, late or on time, reconciled or open, material or immaterial. Controllers use it to turn a number into a decision, such as whether an account needs a review note at close.