COALESCE DAX Function in Power BI

In DAX, COALESCE evaluates a series of arguments in the order they are provided and returns the first one that is not blank. If all expressions evaluate to BLANK, BLANK is returned. A "blank" value in DAX, represented by the BLANK() function, signifies the absence of a value—but distinct from zero (0) or an empty string ("").

DAX Syntax COALESCE(value1, value2, ..., valueN)

value1, value2, ..., valueN: A list of arguments (at least one) that can be columns, measures, expressions, or constant values. COALESCE evaluates these arguments sequentially and returns the first one that is not BLANK().

Example: Basic Example with Constants.

DAX

COALESCE Measure1 = COALESCE(BLANK(), 2, 3)

The output of the above dax code is shown below:

Explanation: The first argument is BLANK(), which is blank. The second argument, 2, is not blank, so COALESCE returns 2 and does not evaluate further.

Example: All Arguments Blank.

DAX

COALESCE Measure1 = COALESCE(BLANK(), BLANK(), BLANK())

The output of the above dax code is shown below:

• Explanation: All arguments are blank, so COALESCE returns BLANK().

In Measures We can use COALESCE function with measures for handling a fallback value when blank is returned.

DAX

Coalesce Measure2 = CALCULATE(SUM(Sheet1[10th Marks]), FILTER(Sheet1, Sheet1[Country]= "Bulgaria"))

The output of the above measure is blank as there are no rows in the filter context. Our requirement is in this condition we want to return 0.

DAX

Coalesce Measure2 = COALESCE(CALCULATE(SUM(Sheet1[10th Marks]), FILTER(Sheet1, Sheet1[Country]= "Bulgaria")), 0)

The output of the above dax code is shown below:

COALESCE with SELECTEDVALUE Function We can use the COALESCE function with the SELECTEDVALUE function to show "Not Selected" if no single value is filtered.

DAX

COALESCE Status Measure= COALESCE(SELECTEDVALUE(Sheet1[Country]), "Not Selected")

If no country is selected means all countries are selected. The output of the above dax code is shown below:

Let’s select Canada from the Country slicer we can see its value in the measure as shown below:

To get more information regarding the SELECTEDVALUE function in DAX. Click here.

Previous Next