The COALESCE function in DAX

A common requirement in authoring DAX code is to provide a default value for the result of a calculation that might otherwise be blank. The classic approach to solving this requirement is by using an IF statement. For example, the following code returns the value of either the Sales[Net Price] column or the Sales[Unit Price] column, whichever is first to not be blank:

IF ( 
    ISBLANK ( Sales[Net Price] ), 
    Sales[Unit Price], 
    Sales[Net Price] 
)

When the expressions considered are measures instead of column references, it is a best practice to save the result of the first measure in a variable. The variable improves the performance by ensuring that the measure is evaluated only once:

VAR MyResult = [MyMeasure]
VAR DefaultValue = [Default Measure]
VAR Result = 
    IF ( 
        ISBLANK ( MyResult ), 
        DefaultValue, 
        MyResult 
    )
RETURN
    Result

The alternative to this expression requires two references to the same MyMeasure measure, which could result in a double evaluation of the same expression to obtain the same result. The following expression evaluates MyMeasure a first time to test whether its value is blank or not, and then a second time to determine the result of the IF function if MyMeasure returns a non-blank value:

IF ( 
    ISBLANK ( [MyMeasure] ), 
    [Default Measure], 
    [MyMeasure]
)

By using the COALESCE function the code is more readable. COALESCE implements the same logic as in the previous examples, evaluating its arguments and returning the first argument that is not a blank:

COALESCE ( [MyMeasure], [DefaultMeasure] )

COALESCE accepts multiple arguments and returns the first argument that is not blank. If the first argument is blank, COALESCE returns the value provided by the expression in the second argument, and so on. If all the arguments are blank, COALESCE returns blank as well.

For example, consider a measure showing the Price of a product, using the average of Sales[Net Price] or using the Product[Unit Price] column if there are no sales transactions. Without COALESCE, the measure looks like this:

Price =
VAR AvgPrice =
    AVERAGE ( Sales[Net Price] )
VAR UnitPrice =
    SELECTEDVALUE ( 'Product'[Unit Price] )
VAR Result =
    IF ( 
        ISBLANK ( AvgPrice ), 
        UnitPrice, 
        AvgPrice 
    )
RETURN
    Result

By using COALESCE, the code becomes much simpler:

Price =
VAR AvgPrice =
    AVERAGE ( Sales[Net Price] )
VAR UnitPrice =
    SELECTEDVALUE ( 'Product'[Unit Price] )
VAR Result =
    COALESCE ( AvgPrice, UnitPrice )
RETURN
    Result

Performance-wise, COALESCE implements an IF condition. For this reason, you should not expect the latter code based on COALESCE to be faster than the former example based on an IF . Nevertheless, using COALESCE improves readability and this is already a great reason to start using it whenever needed. Moreover, the behavior of COALESCE may be further optimized in future versions of the DAX engine, so that the COALESCE function also ends up improving performance.