Downloads
DAXLogicalBeginner

IF

Returns one value when a test is true and another value when it is false.

Syntax
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

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

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.

Related functions

All DAX functions · View as Markdown · DAX fundamentals