Default Values with the COALESCE Function

Often, when creating measures in DAX, it’s necessary to handle default values. In the case of non-existent values in the dataset (blank), it’s a good practice to introduce default values. An example might be presenting sales values broken down by products and years. If it turns out that the sales of a product in a particular year didn’t occur at all, the measure summing up the sales would return a blank value.

Certainly, a solution could involve using the combination of IF and ISBLANK functions, but it would require writing longer code. The COALESCE function comes to the rescue. The COALESCE function evaluates a measure, and if it encounters a blank value, it selects the measure with the default value. Using the IF/ISBLANK combination, our measure would look like:

Sales=
IF(
ISBLANK([Total Sales]),[Default Value],[Total Sales] )

Applying the COALESCE function, our measure looks like this:

Sales=
COALESCE(
[Total Sales],
[Default Value]
)

Using this function makes our code more readable, and the logic of the previous solution is preserved. Comparing the speed of execution, both functions perform similarly fast. However, if code readability is important, it’s advisable to use this function as a good practice in data visualization. Moreover, this function may be optimized in the future with new versions of the DAX engine, so it’s worthwhile to establish good habits now.

Interesting article? Share further!

Leave A Comment