Search code examples
mdxcase-statement

MDX Case Statement With Multiple Criteria in single "When"


I'm trying to consolidate the following MDX script as much as possible and was wondering if there is a way to merge the four Quarters (Q1,Q2,Q3,Q4) into a single line? Also, if you have any further ideas on how to simplify this, I am all ears!

Case [Period].CurrentMember
  When [Q1] Then [Finance Charge and NSF Revenue] / ([CXO_Average Total A/R]+[Average Total A/R - Normalized Load])*4
  When [Q2] Then [Finance Charge and NSF Revenue] / ([CXO_Average Total A/R]+[Average Total A/R - Normalized Load])*4
  When [Q3] Then [Finance Charge and NSF Revenue] / ([CXO_Average Total A/R]+[Average Total A/R - Normalized Load])*4
  When [Q4] Then [Finance Charge and NSF Revenue] / ([CXO_Average Total A/R]+[Average Total A/R - Normalized Load])*4
  When [Total Year] Then [Finance Charge and NSF Revenue] / ([CXO_Average Total A/R]+[Average Total A/R - Normalized Load])        
Else 
  [Finance Charge and NSF Revenue] / ([CXO_Average Total A/R]+[Average Total A/R - Normalized Load])*12
End

Solution

  • This should work

    Case 
      When [Period].CurrentMember =[Q1] or [Period].CurrentMember =[Q2] or [Period].CurrentMember =[Q3] or [Period].CurrentMember =[Q4]
      Then [Finance Charge and NSF Revenue] / ([CXO_Average Total A/R]+[Average Total A/R - Normalized Load])*4
       When [Period].CurrentMember =[Total Year] Then [Finance Charge and NSF Revenue] / ([CXO_Average Total A/R]+[Average Total A/R - Normalized Load])        
    Else 
      [Finance Charge and NSF Revenue] / ([CXO_Average Total A/R]+[Average Total A/R - Normalized Load])*12
    End
    

    Below is another example based on adventure works which helps.

    with member measures.test as 
    case  
    when 
    [Product].[Subcategory].currentmember.Properties ("Member_Value",TYPED) ="caps" 
    or [Product].[Subcategory].currentmember.Properties ("Member_Value",TYPED) ="chains" 
    or [Product].[Subcategory].currentmember.Properties ("Member_Value",TYPED) ="gloves" 
    then 1 
    when [Product].[Subcategory].currentmember.Properties ("Member_Value",TYPED) ="Fenders" 
    then 5
    else 0 end 
    select {[Measures].[Internet Sales Amount],measures.test} on columns ,
    [Product].[Subcategory].[Subcategory] on rows 
    from 
    [Adventure Works]
    

    enter image description here