Set 0 for specific value MDX query

Question

I've been looking around for the answer but I didn't find anything. Sorry if the answer has been given elsewhere.

Here is my problem :

I have a calculated member which is the number of items (of the current member) divided by the total number of items (sumitem).

with 
member 
    sumitem
as 
    SUM ([FailureReason].[FailureReason].[All],[Measures].[Items])
member
    Impact
as
    [Measures].[Items]/[Measures].[SumItem]

But for a specific member of my dimension FailureReason, the result of Impact has to be 0. So I tried to add this :

member
    ImpactFinal
as
    iif ([FailureReason].CurrentMember = [FailureReason].[FailureReason].&[127], 
        0, 
        Impact
    )   

and I select my data like this :

select 
    {[Measures].[Items],
     ImpactFinal
     } on columns, 
    [FailureReason].members on rows 
from 
    NoOTAR

But instead of getting 0 only for this specific member, every members of this dimension have their ImpactFinal equals to 0. What is strange is if I replace 0 by any other value, the result is good.

Answer 1

Just use

[FailureReason].CurrentMember IS [FailureReason].[FailureReason].&[127]

instead of

[FailureReason].CurrentMember = [FailureReason].[FailureReason].&[127]

and it will work.

Update: Several tips:

1. There is also not necessary to use SUM function, since you can define only tuple, this will be enough for server: ([FailureReason].[FailureReason].[All],[Measures].[Count])

2. It's quite reasonable to check sumitem measure for dividing by zero in ImpactFinal calculation. Because once some filters are applied, this may cause zeroing this measure and errors in reports.

3. If you have an opportunity not only to query, but update cube, SCOPE ([FailureReason].[FailureReason].&[127],[Measures].[Impact]) with THIS = 0 is better than additional member because of performance.

Best of luck!

UPDATE to fix totals:

If total should be w/o FailureReason 127, you can substitute your measures with:

member Impact
as
iif ([FailureReason].[FailureReason].CurrentMember is [FailureReason].[FailureReason].&[127],
        0,
        [Measures].[Items]
    )

member ImpactFinal
as 
iif ([FailureReason].[FailureReason].CurrentMember is [FailureReason].[FailureReason].[All]
    ,[Measures].[Items]-([FailureReason].[FailureReason].&[127],[Measures].[Items])
    ,[Measures].[Impact])/[Measures].[SumItem]

But I have another solution, which is more readable:

member v2_ImpactUncountableFailure
as
iif ([FailureReason].[FailureReason].CurrentMember.Level.Ordinal=0
or 
[FailureReason].[FailureReason].CurrentMember is [FailureReason].[FailureReason].&[127]

,([FailureReason].[FailureReason].&[127],[Measures].[Items])
,null)

member v2_ImpactFinal
as
([Measures].[Items]-[Measures].[v2_ImpactUncountableFailure])
/
([FailureReason].[FailureReason].[All],[Measures].[Items])

Use only this two measures instead of set of measures sumitem,Impact,ImpactFinal. First one will show result on failure-127 and total. Second subtracts it from clean unfiltered measure, so in the end we have clean members, zeroed failure-127 and corrected total.

Please let me know if it isn't work, I've tested on my DB and everything is OK.