Power BI – COALESCE Function to Remove Blanks

Introduction

This post walks through a simple use of the coalesce function to prevent a measure returning a Blank. The example used is a simple measure.

YouTube Version

Measure

The above image shows a card that displays the Total Sales measure and due to slicer selections on the page it returns a blank. The measure is a simple SUMX calculation of Transactions[Qty] * Related Products[Price].

Adding Coalesce

Coalesce function can take multiple parameters. It evaluates the first one, if that returns a value it returns that value, if it returns a blank the function moves onto the next parameter and continues until it finds a non-blank value. If all the calculations return a blank the function returns a blank.

In this example we would like the Blank to be replaced with zero. In other measures you might want it to be another value. So we wrap the whole calculation in a coalesce function.

Total Sales = 
// Return 0 if blank
COALESCE (
    SUMX ( Transactions, Transactions[Qty] * RELATED ( Products[Price] ) ) ,
    0
)

The card now shows a zero value. The Measure was also formatted to be a currency, so we get £0.00.

zero value in card after adding coalesce function

Conclusion

This is just a simple example of how the coalesce function can improve the output from measures. The zero could be a string such as “No Sales” or could be an alternative calculation followed by a zero if both calculations returned a blank.

Power BI Series

Categories:Power BI

Tags:coalesce function

Hat Full of Data