Conditional calculations

IF and CASE

IF [Revenue] > 1000 THEN 'High'
ELSEIF [Revenue] > 100 THEN 'Medium'
ELSE 'Low'
END

CASE [Region]
  WHEN 'East' THEN 'Domestic'
  WHEN 'West' THEN 'Domestic'
  ELSE 'International'
END

IF handles ranges and complex boolean conditions. CASE matches one field against discrete values — it is cleaner to read and generally performs better for that job.

Conditions evaluate top to bottom, first match wins, so order from most specific to least. Without ELSE, unmatched rows return null.

IIF

IIF([Revenue] > 1000, 'High', 'Low')
IIF([Revenue] > 1000, 'High', 'Low', 'Unknown')   -- fourth argument handles null

Compact for a two-way choice. The optional fourth argument specifies what to return when the test is null, which IF cannot do as concisely.

The aggregation error

IF SUM([Sales]) > 1000 THEN [Region] END      -- fails

IF SUM([Sales]) > 1000
THEN [Region] END

IF SUM([Sales]) > 1000
THEN MIN([Region]) END

Wrapping the row-level field in MIN or ATTR makes both sides aggregate. ATTR returns the value if it is unique within the group and an asterisk if not, which is often the more honest choice.

Booleans are faster

A calculation returning true/false evaluates faster than one returning strings, and boolean filters are cheaper than string filters.

Where you only need a flag, [Revenue] > 1000 on its own is better than an IF returning 'Yes' and 'No'.