The COALESCE function receives two or more expressions as arguments and returns the result of the first one that does not return a BLANK.
COALESCE(
expression,
expression
[, expression]…
)
- expression: Expressions to evaluate.
The COALESCE function returns a scalar.
Expressions are evaluated from left to right, returning the first expression whose evaluation does not return a BLANK. If all expressions return a BLANK, the function will also return this value.
The expressions to be evaluated can return data of different types.
In this example, the BLANK function and several scalars are added as arguments, returning the first argument found that does not return a BLANK ("A"):
resultado = COALESCE(BLANK(), "A", BLANK(), "B")
In this second example, we start from the following data table:
...table that we take to a visual object type "table" (which allows the selection of a value by clicking on it, which causes the selection of the entire row) and we define the following measure:
Selección =
COALESCE(
SELECTEDVALUE(data[Código]),
SELECTEDVALUE(data[Cantidad]),
SELECTEDVALUE(data[Precio])
)
If we bring this expression to the canvas in card format and do not make any selection in the table, the result is the following:
The measure returns a BLANK as there are no selected values in any of the three fields. But if we select one of the "A" values from the Código column:
...the expression returns this value (first argument of the COALESCE function).
If we now select one of the values in the Cantidad column for which the Código column does not include a value (for example, the value 2 in the second row), the result is the following:
In this case the function has evaluated the first argument (which returns the value selected in the Código column) obtaining a BLANK, so that it has evaluated the second argument (selected value in the Cantidad column) obtaining a value of 2, not null, and therefore being returned as a result of the measure.
Finally, if we select the value 4.00 from the first row (record for which there is no value neither in the Código column nor in Cantidad), the function returns the value 4.00:
This function can be used to convert possible null values to, for example, a zero. Suppose the following measure that adds up the sales made:
Ventas = SUM(Sales[Amount])
If the context reduces the sales table to the point where there are no values to add, the previous measure will return a BLANK, which may not be what you want:
We can, however, "protect" the function so that it always returns a numeric value with the COALESCE function:
Ventas = COALESCE(SUM(Sales[Amount]), 0)
