Downloads
DAXLogicalBeginner

COALESCE

Returns the first value in the list that is not blank.

Syntax
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

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

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.

Related functions

All DAX functions · View as Markdown · DAX fundamentals