DAX COALESCE function
Returns the first argument that isn't blank.
COALESCE is the tidy way to replace blank with something else, usually zero, so rows with no data still show.
Syntax
COALESCE(<expression>, <expression>[, <expression>]…)| Argument | Required | Description |
|---|---|---|
| expression | Required | Two or more values; the first non-blank one is returned. |
Returns: The first non-blank value, or blank if all are blank.
Examples
Calculated on the free practice data (table financials), with [Total Sales] = SUM(financials[Sales]) and [Total Profit] = SUM(financials[Profit]).
1. Sales with zero instead of blank
Sales or Zero = COALESCE([Total Sales], 0)| Month Year | Sales or Zero |
|---|---|
| Jun 2013 | 0 |
| Jul 2013 | 0 |
| Aug 2013 | 0 |
| Sep 2013 | 4,484,000.03 |
| Oct 2013 | 9,295,611.10 |
There are no sales before September 2013, so those months show 0 instead of disappearing.
Good to know
- Don't add zeros everywhere: blank rows are hidden on purpose, and a zero for every combination makes visuals long and slow.
Related functions
Official reference: COALESCE function on Microsoft Learn