Converges an empty cell value to a number or string and returns the specified value instead of empty.
CoalesceEmpty(String_Expression1 [, String_Expression2]) CoalesceEmpty(Numeric_Expression1 [, Numeric_Expression2])
|String_Expression1||A valid string expression. Usually an MDX expression of cell coordinates that returns a string.|
|String_Expression2||A valid string expression that will be substituted for a NULL returned by the first string expression.|
|Numeric_Expression1||A valid numeric expression. Usually an MDX expression of cell coordinates that returns a number.|
|Numeric_Expression2||A valid numeric expression that will be substituted for a NULL returned by the first numeric expression.|
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.*"