SSAS #27 – How filtered (calculated) measures are aggregated along dimensions?
In an SSAS cube I designed recently, I created a dimension (a junk dimension) where it has either Approved or Declined value for an attribute hierarchy called “Decision”. Most of the queries will just focus on the approved count and amount, without showing the declined amount. This naturally lead me to create two calculated measures, Count Approved, and Amount Approved.
Here is the simple MDX script to filter the count for Approved only.
CREATE MEMBER CURRENTCUBE.[MEASURES].[The Count Approved]
AS ([Measures].[The Count], [The Decision].[Decision].&[Approved]),
FORMAT_STRING = "#,#",
VISIBLE = 1;
To make sure that the calculated/filtered measure behaves as I expected, I did this simple browsing (in BIDS or in SSMS).
1. How the calculated/filtered measure is aggregated along dimensions other than The Decision dimension.
It’s good to see that the measure is summed over the Time dimension as expected. Sum is the default Aggregation Function for the original measure.
2. How the calculated/filtered measure is aggregated along The Decision dimension.
It’s also good to see that the measure is NOT aggregated in any way along the Decision hierarchy. This is also expected. Notice that the Approved measure also stays the same for the different members of the attribute hierarchy. This does look a little odd at first, but this should be also expected.