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.
» 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: Nov 14, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/coalesce-function-dax