Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
MDX Help

已答覆 MDX Help

  • Tuesday, February 19, 2013 1:24 AM
     
     
    Sign In to Vote
    0

    I've the following 4 measures:
    [Measures].[Val]
    [MEASURES].[Cost]
    [MEASURES].[Sales]
    [MEASURES].[Profit]

    The values in these could be: a numeric/decimal value, NULL or Zero.

    I need help to create a new calculated measure which returns the first non zero/non null by considering the above order.

    I used a COALESCEEMPTY - which works as long as one of the measure doesn't have the value = 0. The measure that could have a zero value is: [Measures].[Val]

    GBM

     

All Replies

  • Tuesday, February 19, 2013 3:40 AM
     
     Answered
    Sign In to Vote
    0

    If I understood you correctly, the following probably is what you want:

    IIF ( [Measures].[Val]<>0 and [Measures].[Val] is not null, [Measures].[Val],

    IIF ( [MEASURES].[Cost]<>0 and [MEASURES].[Cost] is not null, [MEASURES].[Cost],

    IIF ( [MEASURES].[Sales]<>0 and [MEASURES].[Sales] is not null, [MEASURES].[Sales],

    IIF ( [MEASURES].[Profit]<>0 and [MEASURES].[Profit] is not null, [MEASURES].[Profit],0))

    note that if all the measures = to 0 or null then the above will return 0, as you didn't mention what to do when all of them = 0 or null



    • Edited by Butmah Tuesday, February 19, 2013 3:41 AM
    • Marked As Answer by GBaksh Tuesday, February 19, 2013 9:41 PM
    •  
     
  • Tuesday, February 19, 2013 3:52 PM
    Answerer
     
     
    Sign In to Vote
    0

    if it is possible and would not change the meaning of your data you may also replace your 0-values by NULL in your relational table/view

    then set NullProcessing-Property of your measures to "Preserve" and you can use COALESCEEMPTY() as you initially intended to

    hth,
    gerhard

    - www.pmOne.com -

     
  • Tuesday, February 19, 2013 9:42 PM
     
     
    Sign In to Vote
    0
    Thanks Gerhard for your suggestion. I already have the NULL values in the underlying fact table, but for some odd reason the SSAS converts those into zeros - any idea why that would be?

    GBM

     
  • Wednesday, February 20, 2013 2:07 PM
    Answerer
     
     
    Sign In to Vote
    0
    yes, as i said, you have to set the NullProcessing-Property of your physical measure to "Preserve"

    - www.pmOne.com -