US and Worldwide: +1 (866) 660-7555
Results 1 to 2 of 2

Thread: put a default value to measure?

  1. #1

    Default put a default value to measure?

    Hello,
    I have this screenshot that explains itself... When I cross two dimensions, I get lots of empty cells of the measure where no facts are present. It is logical that there is no value, but dashboards and sparklines have problems when they use this MDX query.
    I would like to change the schema definition to have 0 as default value for the measure, even though there are no facts.
    Is it possible to do this from schema or at least from MDX?

    empty_measure_values.png

    Code:
    SELECT
    NON EMPTY {Hierarchize({[Planned End Date.default].[Year].Members})} ON COLUMNS,
    NON EMPTY {Hierarchize({[Status.default].[Status].Members})} ON ROWS
    FROM [Cube]
    Thanks for any suggestions!

  2. #2

    Default

    OK I should have documented myself better before posting...
    in MDX:

    Code:
    WITH
    MEMBER[Measures].[MyCount]
    AS 'IIF([Measures].[Number of Objects]>0, [Measures].[Number of Objects], 0)'
    
    SELECT
    NON EMPTY CrossJoin([Planned End Date.default].[Year].Members, {[Measures].[MyCount]}) ON COLUMNS,
    NON EMPTY {Hierarchize({[Status.default].[Status].Members})} ON ROWS
    FROM [Cube]

    in schema:
    Code:
    <CalculatedMember name="MyCount" formula="IIF([Measures].[Number of Objects] > 0, [Measures].[Number of Objects], 0)" dimension="Measures" visible="true">
    </CalculatedMember>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •