COALESCE
Returns the first value in the list that is not blank.
COALESCE(<value1>, <value2> [, <value3>] ...)Returns: The first non-blank value (a scalar). BLANK when every value is blank.
What it does
COALESCE scans a list of values from left to right and returns the first one that is not blank. It is the standard way to supply a fallback: use the preferred amount, and when it is missing, use the next best one. It is also a clean way to show a zero where a blank would look like a gap.
Syntax and arguments
- value1, value2, ...: two or more values to check, in priority order. They can be columns, measures or constants.
Example
The billing team wants the amount due on each invoice. The approved amount is used when it is filled in, and the original invoice amount is used when it is not.
Amount Due =
COALESCE(
Invoices[ApprovedAmount],
Invoices[InvoiceAmount],
0
)
For each row, the approved amount is used if present. If not, the invoice amount is used. If both are missing, the last value, 0, is returned. Without the final 0, the result would be blank for those rows.
Example: a second, different use
A card that should show 0 when there are no returns, rather than a blank that looks like the report failed:
Returns Shown = COALESCE([Total Returns], 0)
The measure is BLANK when no return lines exist in the context. COALESCE turns that blank into 0 so the card always shows a number.
Things to watch
- COALESCE only checks for blank. A zero is not blank, so a zero in the first position is returned as zero.
- Blank and zero are different things. Choose the default that matches what the business means.
- Use COALESCE inside a total only with care. Replacing a blank with zero makes the blank rows count as zero in averages and counts.
- The order of the list is the priority. Put the most trusted value first.
- COALESCE is different from IFERROR, which reacts to errors, and from IF, which needs an explicit test.
In business terms
COALESCE is used when a figure has a preferred source and a fallback: the actual over the estimate, the paid amount over the billed amount, the current price over the list price. It keeps reports complete without hiding that a fallback was used, if the report says so.