Handling blank values using COALESCE Function

Everyone would have faced a situation when a measure returns a blank output or a column has blank value for a particular row. We can handle these values with the help of a DAX function called  COALESCE.

COALESCE is a logical function that returns the first non-empty value from a list of expressions. If all expressions are empty, BLANK is returned. The COALESCE function is a very flexible function that can be used in many situations. It can be used for:

• Return the value of a measure if it is not blank, or a default value if it is blank.
• Return the value of a column if it is not blank, or the average value of the column if it is blank.
• Return the value of a calculated field if it is not blank, or the value of another calculated field if it is blank.

The syntax for the COALESCE function is as follows:

COALESCE(expression1, expression2, expression3, …)

Where:

expression1 is the first expression to be evaluated.

expression2 is the second expression to be evaluated, and so on.

Scenario 1 :

COALESCE(blank(),100,20)

Function will start scanning from left. The first expression is blank so it will move to second expression.

Since it is non blank then output would be 100.

Scenario 2:

COALESCE(Total Sales, Total Quantity,0)

It will check whether Total sales is blank or not, it its blank then it will move to second expression. If total quantity is non blank then it will be the output else it will show 0.

Scenario 3:

COALESCE(Blank(),Blank(),Blank())

If all the expression holds a blank value then it will also return blank as output.

Implementation

Lets understand its implementation in Power BI with below data sets ?

We don’t have sales data for two rows in the table (Logistics/hyderabad & Manufacturing/Delhi). We have created a view to show Total sales and sales by city and category in a table as follows:

If you will see carefully, table has blank for both the categories as there are no sales data. If we select any one of them in the slicers it will show as follows:

Table has no data and card visual showing Blank. We can cover this scenario using COALESCE function with the help of a measure. Create a measure as follows:

This measure will check blank value from left to right and will return first non-blank value. Let’s add this measure to the table and add a new card as follows:

Select Delhi/manufacturing from the slicers and see the difference between a COALESCE measure and a normal sales measure:

It is showing 0 instead of Blank(). Likewise we can write more expressions to cover different scenarios or to show any particular values in such conditions.

Limitations:

The COALESCE function has a few limitations that you should be aware of:

• The COALESCE function can only return a single value. If you need to return multiple values, you will need to use a different function, such as the SWITCH function.
• The COALESCE function cannot be used to handle errors. If an expression in the COALESCE function evaluates to an error, the COALESCE function will return BLANK.

Conclusion

The COALESCE function is a powerful tool that can be used to deal with blank values ​​in Power BI. By understanding how the COALESCE function works, you can generate accurate and informative reports, even if your data contains blank values.