MDX: Aggregate function with more than one measures doesn't work

Question

Hi,

I have 5 measures of sum type in my SSAS cube: measureA, measureB, measureC
And I have a calculated member in the query level that is the sum of the 3 measures.

The problem: When I call the aggregate or sum function with a date range, the value of the sum is huge, greather than the real value ( I need the aggregate function because I get the sum of the measures in two different periods, so I cannot slice the cube ).

But when I call the aggregate for each measure, and after all I sum the aggregated members, works fine.

Example: ( Not work )

with member totalMeasures as
measureA + measureB + measureC
with member TotalMeasuresInRange1 as
aggregate( Date.Datekey.Begin1:Date.DateKey.End1, totalMeasures )
with member TotalMeasuresInRange2 as
aggregate( Date.Datekey.Begin2:Date.DateKey.End2, totalMeasures )

Example: ( Work )

with member TotalMeasureA1 as
aggregate( Date.Datekey.Begin1:Date.DateKey.End1, measureA )
with member TotalMeasureB1 as
aggregate( Date.Datekey.Begin1:Date.DateKey.End1, measureB )
with member TotalMeasureC1 as
aggregate( Date.Datekey.Begin1:Date.DateKey.End1, measureC )
with member TotalMeasureA2 as
aggregate( Date.Datekey.Begin2:Date.DateKey.End2, measureA )
with member TotalMeasureB2 as
aggregate( Date.Datekey.Begin2:Date.DateKey.End2, measureB )
with member TotalMeasureC2 as
aggregate( Date.Datekey.Begin2:Date.DateKey.End2, measureC )
member TotalMeasuresInRange1 as
TotalMeasuresA1 + TotalMeasureB1 + TotalMeasureC1
member TotalMeasuresInRange2 as
TotalMeasuresA2 + TotalMeasureB2 + TotalMeasureC2

Why the first example doesn't work? The query is very big in the second example, especially when we think about a case with more measures, 10, 20 measures. I wish something that works more like the first example.

Best Regards,
Luis

Answer 1

Since your base measures are all additive, there's nothing in your example that prevents you from using Sum in place of Aggregate. And, there's nothing to indicate that such sums would be incorrectly inflated. Some of the reasons that said inflation could be happening are that some of the base measures are in fact not additive, or some calculations defined for the cube.
I couldn't relate the "I get the sum of the measures in two different periods, so I cannot slice the cube" explanation to the example given, please elaborate.

Answer 2

Hi,

Aggregate function cannot be used on calculated measures. Not sure why your first query gets a huge value ?

I think you could try create two new member and arrange them like

WITH MEMBER as Date.Date.R1  
aggregate( Date.Datekey.Begin1:Date.DateKey.End1)  
MEMBER as Date.Date.R2  
aggregate( Date.Datekey.Begin2:Date.DateKey.End2)  
member totalMeasures as  
(measureA + measureB + measureC)  
  
SELECT   
totalMeasures on 0,  
{ Date.Date.R1, Date.Date.R2} on 1  
FROM [yourCube]  

Regards,
Lukas

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November