MDX dimension usage in CASE statment

Question

I want to check the number of the month and according to that divide by a certain number. The problem is that this code always defaults to the else statement. Can anyone spot the issue? Thanks.

CREATE MEMBER CURRENTCUBE.[Measures].[SBBD]
 AS case [Date].[Calendar Month Number Of Year]
         when 2 then [Measures].[SB]/28 
         when 4 then [Measures].[SB]/30 
         when 6 then [Measures].[SB]/30 
         when 9 then [Measures].[SB]/30 
         when 11 then [Measures].[SB]/30 
         else [Measures].[SB]/31
    end, 
FORMAT_STRING = "Standard", 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'LB';

Answer 1

Try just comparing to the member's value in the condition of the CASE statement. Currently you are comparing a member [Date].[Calendar Month Number Of Year] which is not seen as a numeric expression in mdx, and comparing it to a numeric expression (2, 4,...).

Maybe the following works better:

CREATE MEMBER CURRENTCUBE.[Measures].[SBBD]
 AS case [Date].[Calendar Month Number Of Year].CURRENTMEMBER.MEMBER_VALUE
         when 2 then [Measures].[SB]/28 
         when 4 then [Measures].[SB]/30 
         when 6 then [Measures].[SB]/30 
         when 9 then [Measures].[SB]/30 
         when 11 then [Measures].[SB]/30 
         else [Measures].[SB]/31
    end, 
FORMAT_STRING = "Standard", 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'LB';

I'd be more tempted to initially create a measure [Measures].[NumOfMonth] and then feed that into the case (chances are splitting in two like this will be quicker):

CREATE MEMBER CURRENTCUBE.[Measures].[NumOfMonth]
 AS [Date].[Calendar Month Number Of Year].CURRENTMEMBER.MEMBER_VALUE, 
FORMAT_STRING = "Standard", 
VISIBLE = 0 ,  ASSOCIATED_MEASURE_GROUP = 'LB';

CREATE MEMBER CURRENTCUBE.[Measures].[SBBD]
 AS case [Measures].[NumOfMonth]
         when 2 then [Measures].[SB]/28 
         when 4 then [Measures].[SB]/30 
         when 6 then [Measures].[SB]/30 
         when 9 then [Measures].[SB]/30 
         when 11 then [Measures].[SB]/30 
         else [Measures].[SB]/31
    end, 
FORMAT_STRING = "Standard", 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'LB';

---

OTHER SIDE OF THE COIN

Rather than worrying about values you could compare members. The condition of the case should use the currentmember function, and the numbers replaced by members:

CREATE MEMBER CURRENTCUBE.[Measures].[SBBD]
 AS CASE [Date].[Calendar Month Number Of Year].CURRENTMEMBER
         WHEN [Date].[Calendar Month Number Of Year].[2] then [Measures].[SB]/28 
         WHEN [Date].[Calendar Month Number Of Year].[4] THEN [Measures].[SB]/30 
         WHEN [Date].[Calendar Month Number Of Year].[6] THEN [Measures].[SB]/30 
         WHEN [Date].[Calendar Month Number Of Year].[9] THEN [Measures].[SB]/30 
         WHEN [Date].[Calendar Month Number Of Year].[11] THEN [Measures].[SB]/30 
         ELSE[Measures].[SB]/31
    END, 
FORMAT_STRING = "Standard", 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'LB';