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
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]