Nonzero

Returns blank if numeric expression has result 0

Syntax

 Nonzero( Numeric_Expression )

Arguments

Numeric_Expression	MDX expression that returns a number.

Returns

Number	Numeric_expression

Examples

Example1

The following example will return the difference between created and resolved issues. Zero results will be replaced by blanks because of the Nonzero function

Nonzero(
  [Measures].[Issues created]
  -
  [Measures].[Issues resolved]
)

Blank rows can be hidden using the "Hide empty" button from the eazyBI toolbar.

Example2

The following example will return the max days between issue resolution date and last date when status updated. In case there is another status to complete the issue both dates won't match, if dates match the Datediffdays would return result 0 but zero results will be replaced by blanks because of the Nonzero function

Nonzero(
Max(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    -- for unresolved issues only
    Not isEmpty([Measures].[Issue resolution date])
    AND
    -- show on time for issue creation date
    DateInPeriod(
      [Measures].[Issue created date],
      [Time].CurrentHierarchyMember)
    ),
    -- calculate days in current status for each relevant issue
    CASE WHEN
    -- check if issue is relevant for the report
    [Measures].[Issues resolved]  > 0
    THEN
    -- calculated days in status
      DateDiffDays(
      [Measures].[Issue resolution date],
      [Measures].[Issue status updated date])  
    END
)
)