MDX Case statement

Question

I am trying to use a MDX calculated measure to do a case statement on a column.

I am using this code:

Case [hist].[title] when "Mr" then "Test1 "Else "Test2"end

when I save it it sets everything to Test2 in the new column even though Mr is in the title field for some of the record. Any ideas where I could be going wrong?

Thanks

whytheq · Answer 1 · 2016-07-29T14:58:37.027

In mdx you can say:

CASE  
 WHEN [hist].[title].CURRENTMEMBER.MEMBER_CAPTION = "Mr"
    THEN "Test1"
 ELSE "Test2"
END

or

CASE  
 WHEN [hist].[title].CURRENTMEMBER 
     IS [hist].[title].[title].&[Mr]
    THEN "Test1"
 ELSE "Test2"
END

'IIF' is better to use as default:

IIF(  
 [hist].[title].CURRENTMEMBER.MEMBER_CAPTION = "Mr"
 ,"Test1"
 ,"Test2"
)

or

IIF(  
 [hist].[title].CURRENTMEMBER 
     IS [hist].[title].[title].&[Mr]
 ,"Test1"
 ,"Test2"
)

note

In terms of performance: - IS operator is preferable to using .MEMBER_CAPTION = "Mr"
- IIF generally performs better than CASE - If you can get away with one of the branches of IIF being NULL then chances are your calculation will run in the faster block mode.

So this would be best:

IIF(  
 [hist].[title].CURRENTMEMBER 
     IS [hist].[title].[title].&[Mr]
 ,"Test1"
 ,NULL
)

Thanks but when I try that I get the same result all records show test 2 — rory83, Jul 29 '16 at 13:56
@rory83 ok - my mistake - you either need to use the `MEMBER_CAPTION` property or better to us the `IS` operator and drag the exact member for "Mr" to the right of the operator - I will guess the full name. — whytheq, Jul 29 '16 at 14:55

MDX Case statement

1 Answers1