COALESCE DAX Function (Logical)
Returns the first argument that does not evaluate to a blank value. If all arguments evaluate to blank values, BLANK is returned.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| Value1 |
Any value or expression. |
|
| Value2 | Repeatable |
Any value or expression. |
Return values
The first Value argument that is not blank.
Remarks
COALESCE returns the first argument that is not BLANK. If all the arguments are BLANK, COALESCE returns BLANK. An empty string and 0 are not BLANK, so COALESCE returns them.
The arguments can have different data types. The data type of the result can be different from the data type of the returned argument. For example, COALESCE ( DATE ( 2020, 1, 1 ), 1 ) returns the decimal number 43831, not a date.
If a measure uses COALESCE to replace BLANK with 0, the measure is never BLANK. Then a report or a SUMMARIZECOLUMNS query shows all the combinations of the grouping columns, including the combinations without data. This can create many more rows and make the query slower. For example, Product[Color] and Date[Calendar Year] return 48 rows with [Sales Amount] and 112 rows with COALESCE ( [Sales Amount], 0 ).
» 3 related functions
Examples
-- COALESCE returns the first non-blank of its arguments
-- It is commonly used to provide default values to expressions
-- that might result in a blank
EVALUATE
SELECTCOLUMNS (
TOPN ( 10, Store ),
"Store name", Store[Store Name],
"Manager",
COALESCE ( Store[Area Manager], "** Not Assigned **" ),
"Years open",
DATEDIFF (
Store[Open Date],
COALESCE ( Store[Close Date], TODAY () ),
YEAR
)
)
ORDER BY [Manager]
| Store name | Manager | Years open |
|---|---|---|
| Contoso Aurora Store | ** Not Assigned ** | 18 |
| Contoso Bar Harbor Store | Alvarez, Janet | 17 |
| Contoso Renton Store | Bennett, Sydney | 17 |
| Contoso Spokane Store | Hill, Wyatt | 17 |
| Contoso Bayonne Store | Hill, Wyatt | 17 |
| Contoso Seattle No.2 Store | Huang, Eugene | 17 |
| Contoso Redmond Store | Johnson, Elizabeth | 17 |
| Contoso Attleboro Store | Moreno, Jimmy | 17 |
| Contoso Baltimore Store | Ramos, Theresa | 17 |
| Contoso Back River Store | Russell, Jennifer | 17 |
-- COALESCE can have more than two arguments
EVALUATE
VAR A = BLANK ()
VAR B = BLANK ()
VAR C = "Test"
RETURN
{ COALESCE ( A, B, C ) }
| Value |
|---|
| Test |
Related articles
Learn more about COALESCE in the following articles:
-
From SQL to DAX: Implementing NULLIF and COALESCE in DAX
This article describes how to implement a syntax equivalent to the T-SQL function NULLIF and the ANSI SQL function COALESCE, in DAX. » Read more
-
The COALESCE function in DAX
COALESCE is a DAX function introduced in March 2020. This article describes the purpose of COALESCE and how to simplify DAX expressions by removing verbose conditions, and yet obtain the same result. » Read more
-
Preparing a data model for Sankey Charts in Power BI
This article describes how to correctly shape a data model and prepare data to use a Sankey Chart as a funnel, considering events related to a customer (contact, trial, subscription, renewal, and others). » Read more
-
Using EXPAND and COLLAPSE in visual calculations
This article provides examples of visual calculations where the use of EXPAND and COLLAPSE is required to obtain the correct result. » Read more
Related functions
Other related functions are:
Last update: Oct 28, 2026 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://learn.microsoft.com/en-us/dax/coalesce-function-dax