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
Numeric expression example
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] )
Date expression example
This MDX formula uses the CoalesceEmpty() function to return the first non-empty value from a list of expressions. It uses dynamic date as a parameter("today," "yesterday," etc.).
CoalesceEmpty( [Measures].[Issue resolution date], [Measures].[Issue due date], DateParse("today") )