CoalesceEmpty

Converges an empty cell value to a number or string and returns the specified value instead of empty. All parameters must have the same data type (string or numeric).

Syntax

For string type values:

CoalesceEmpty(String1_Expression [, String2_Expression...])

For numeric type values:

CoalesceEmpty(Numeric1_Expression [, Numeric2_Expression...])

Arguments

String1_Expression	A valid string expression. Usually, an MDX expression of cell coordinates that returns a string.
String2_Expression .. StringN_Expression	A valid string expression that will be substituted for a NULL returned by the first non-NULL string expression (the substitute value order is important).
Numeric1_Expression	A valid numeric expression. Usually, an MDX expression of cell coordinates that returns a number.
Numeric2_Expression .. StringN_Expression	A valid numeric expression that will be substituted for a NULL returned by the first non-NULL numeric expression (the substitute value order is important).

Examples

String expression example

When some string members need to be compared, CoalesceEmpty() can be used to avoid comparing with null cells, which can result in a null pointer exception error. For instance, you can convert an empty value (i.e. value is not present) to an empty string '' which is suitable for comparing two values with the MATCHES function:

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

Numeric expression examples

The formula will give you 0 for Story Points resolved if no story points are resolved.

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

See the example report Story points progress % in our Demo account. The report uses a calculated measure Story Points resolved. This calculated measure uses the 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).

You can give another measure as a parameter if the measure that is used as the first parameter is empty. For instance, to calculate completed story points, we suggest using "Sprint Story Points completed" measure, which is calculated only at the sprint completion (it is empty during the ongoing sprint, even if the issue has reached completion status); therefore, for ongoing sprints you may want to show resolved story points instead and even add the 3rd option to show closed story points if both preceding measures have no value:

CoalesceEmpty(
  [Measures].[Sprint Story Points completed],
  [Measures].[Story Points resolved],
  [Measures].[Story Points closed]
)

Workaround for dates

The CoalesceEmpty() function does not support the date format directly. However, the date can be interpreted as a numeric or string value to use in this function.

For instance, you can refer to issue properties holding date values. The code below would show the resolution date. If the resolution date is not set, it returns the issue closing date. If both are empty, it falls back to the issue's due date.

CoalesceEmpty(
  [Measures].[Issue resolution date],
  [Measures].[Issue closed date],
  [Measures].[Issue due date]
)

For more complex use cases, for example, if you want to use dynamic dates as parameters ("today", "yesterday," etc.), you can force to transform each date parameter as a numeric for the function CoalesceEmpty() to work. Finally, transform the result returned by CoalesceEmpty() back to a date value.

TimestampToDate(
  CoalesceEmpty(
    DateToTimestamp([Measures].[Issue resolution date]),
    DateToTimestamp([Measures].[Issue due date]),
    DateToTimestamp(DateParse("today"))
  )
)