CoalesceEmpty

Converges an empty cell value to a number or string and returns the specified value instead of empty.

Syntax

 CoalesceEmpty(String_Expression1 [, String_Expression2])
 CoalesceEmpty(Numeric_Expression1 [, Numeric_Expression2])

Arguments

String_Expression1A valid string expression. Usually an MDX expression of cell coordinates that returns a string.
String_Expression2A valid string expression that will be substituted for a NULL returned by the first string expression.
Numeric_Expression1A valid numeric expression. Usually an MDX expression of cell coordinates that returns a number.
Numeric_Expression2A valid numeric expression that will be substituted for a NULL returned by the first numeric expression.

Examples

The formula will give you 0 for Story Points resolved if there is no resolved story points

CoalesceEmpty([Measures].[Story Points resolved],0)/
[Measures].[Story Points created]

See example report Story points progress % in our Demo account. The report uses a calculated measure Story Points resolved%. This calculated measure uses a formula above to calculate how many story points of all created story points are resolved. If there are no resolved story points the formula will give you 0% for resolved points if there are some planned points (Story Points created).


When some string members need to be compared, CoalesceEmpty can be used to avoid comparing with null cells that can result in null pointer exception error.

CoalesceEmpty([Measures].[Issue Account], '') MATCHES ".*Analysis.*"

See also