SSAS Calculated measure to show values only for specific dimension values

0
votes

I have a calculated Measure in my SSAS cube, which I want to calculate only for a group of dimension members and for rest it should be zero. In The following image I want MTDOccupancy dimension to work only for Account Groups for Rooms, rest related to food should show zero for this field. enter image description here

following is the simple MDX which I am using for MTDOccupancy measure

([MTDQuantity]/[MTDAvailableRooms])*100
2
sql-serverssasmdx
select * from tablename where AccountGroup like 'ROOMS%' - wiretext
@tinka that is sql not mdx - whytheq
Go for a cube measure not a calculated measure and use the SCOPE operator to set the value as NULL for the required members. It is faster than calculated member approach since it is cached. - SouravA

2 Answers

1
votes

You can change the measure to something like the following:

IIF(
    LEFT([Account Group].[Account Group].currentmember.member_caption,5) = "ROOMS"
  ,([MTDQuantity]/[MTDAvailableRooms])*100
  ,NULL
)

I have guessed this dimension/hierarchy combination [Account Group].[Account Group]. ... - you will need to change this to reflect the names used in your cube.

0
votes

Please do it as a scope statement for performance reasons. The following will get evaluated the first time someone connects to the cube vs the LEFT running in every cell at report time: 

Create member CurrentCube.[Measures].[My Calc] 
as null
,format_string="0.0%";

scope(Filter([Account Group].[Account Group].[Account Group].Members,   LEFT([Account Group].[Account Group].currentmember.member_caption,5)="ROOMS")); 
   [Measures].[My Calc] = iif(MTDAvailableRooms=0,null,([MTDQuantity]/[MTDAvailableRooms])); 
end scope;