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

Luis Lima 61 Reputation points
2021-01-07T12:01:47.42+00:00

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

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,291 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Lukas Yu -MSFT 5,821 Reputation points
    2021-01-08T08:01:51.723+00:00

    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


  2. Alexei Stoyanovsky 3,416 Reputation points
    2021-01-11T09:05:04.72+00:00

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.