How do I retrieve a measure value based on the value of another measure in SSAS MDX?

Question

I have a complex MDX calculated measure question. I have two measures and a Hour-Minute hierarchy. At the hourly level, MeasureA rolls up to the Max value of the underlying minute values. MeasureB should roll up to the corresponding value of the Max from MeasureA. In the example below, 8 is the Max of MeasureA. So MeasureB should roll up to 2 at the hourly level. I am having trouble with the MeasureB calculation. Any ideas how I can do this in an MDX calculated measure?

MeasureA            Measure B
Hour         8            2
  Minute1    2            3
  Minute2    1            2
  Minute3    5            1
  Minute4    6            3
  Minute5    8            2

In addition to the above.... I have two hierarchies "Year-Month-Day" and "Hour-Minute". This works perfectly for the Hour-Minute hierachy. However, when I also want the same logic to roll up from Hour to Day even though they are in separate hierarchies. I have the code below for the working hour/minute keys and the non-working day keys. Do you see anything that I am doing wrong.

Measure A = "Avg Used Resources" and Measure B = "Total Resources"

// Total      Resources Calculation
---Initializing keys
CREATE MEMBER CURRENTCUBE.[Measures].[Avg Used Resources Key Minutes]
 AS null,
VISIBLE = 1,  ASSOCIATED_MEASURE_GROUP = 'NSS Resource Utilization';

CREATE MEMBER CURRENTCUBE.[Measures].[Avg Used Resources Key Hours]
 AS null,
VISIBLE = 1,  ASSOCIATED_MEASURE_GROUP = 'NSS Resource Utilization';

CREATE MEMBER CURRENTCUBE.[Measures].[Avg Used Resources Key Days]
 AS null,
VISIBLE = 1,  ASSOCIATED_MEASURE_GROUP = 'NSS Resource Utilization';
---

-- Minute Key
SCOPE ([Time].[Hour-Minute].[Time Name],[Measures].[Avg Used Resources Key     Minutes]);
THIS = IIF(([Time].[Hour-Minute].CurrentMember,[Measures].[Avg Used Resources]) 
                  = ([Time].[Hour-Minute].CurrentMember.Parent,[Measures].    [Avg Used Resources])
, [Time].[Hour-Minute].CurrentMember.Member_Key, null);
END SCOPE;

-- Hour Key
SCOPE ([Measures].[Avg Used Resources Key Hours]);
THIS = MAX([Time].[Hour-Minute].CurrentMember.Children,[Measures].[Avg Used     Resources Key Minutes]);
END SCOPE;

-- Day Key
SCOPE ([Measures].[Avg Used Resources Key Days]);
THIS = IIF(([Time].[Hour-Minute].CurrentMember,[Measures].[Avg Used Resources]) 
                      = ([Time].[Hour-Minute].CurrentMember.Parent,    [Measures].[Avg Used Resources])
, [Time].[Hour-Minute].CurrentMember.Member_Key, null);
END SCOPE;

-- Hour Value
SCOPE ([Time].[Hour-Minute].[Hour 24],[Measures].[Total Resources]);
THIS = (StrToMember("[Time].[Hour-Minute].[Time Name].&["+CStr([Measures].    [Avg Used Resources Key Hours])+"]")
,[Measures].[Total Resources]);
END SCOPE;

--Date Value
SCOPE ([Time].[Hour-Minute].[ALL],[Measures].[Total Resources]);
THIS = (StrToMember("[Time].[Hour-Minute].[Hour 24].&["+CStr([Measures].[Avg     Used Resources Key Days])+"]")
,[Measures].[Total Resources]);
END SCOPE;

Your question is unanswerable with this info. Please share the cube structure, code behind the current measure and your analysis on this. — SouravA

Alex Peshik Alex Peshik · Accepted Answer · 2015-01-20T17:28:10

I have very stupid solution, but it works! The main idea is to send a key of necessary minute-member to hour-level.

Create additional empty measures to store keys of max minute value:

CREATE MEMBER CURRENTCUBE.[Measures].[Measure A Key Minutes]
 AS null,
VISIBLE = 0;
CREATE MEMBER CURRENTCUBE.[Measures].[Measure A Key Hours]
 AS null,
VISIBLE = 0;

Than add scopes to calculate them:

SCOPE ([Date].[Date].[Minutes],[Measures].[Measure A Key Minutes]);
THIS = IIF(([Date].[Date].CurrentMember,[Measures].[Measure A]) = ([Date].[Date].CurrentMember.Parent,[Measures].[Measure A])
, [Date].[Date].CurrentMember.Member_Key, null);
END SCOPE;
SCOPE ([Measures].[Measure A Key Hours]);
THIS = MAX([Date].[Date].CurrentMember.Children,[Measures].[Measure A Key Minutes]);
END SCOPE;

First scope puts key of a member to the new measure if value of minute level equals hours level. Second one moves this value to hours level. Now we have keys of max minute value on hours level.

And than add last scope to update Measure B:

SCOPE ([Date].[Date].[Hours],[Measures].[Measure B]);
THIS = (StrToMember("[Date].[Date].&["+CStr([Measures].[Measure A Key Hours])+"]")
,[Measures].[Measure B]);
END SCOPE;

It's absolutely slow solution for big dimensions (server will definitely switch to cell-by-cell mode!), it's rigid and strict, but... it works. Just tested.

Any additions how to speed up are welcome. But it's better to use another approach at all.

UPDATE: (reedited, because of bug) Unfortunately there is no easy way to add new levels. But you could do something like this:

Add another two measures for new HOUR-DAY relation.
First will find the highest key from each HOUR member.
Second will move this to DAY level.
Add scope with StrToMember again, but for days.

Unfortunately, this is the one available solution.

So first measure is like "Minute Key on Minutes Level", second is "Minute Key on Hours Level". Than you need to add measures like "Hour Key on Hours level" and than "Hour Key on Days Level", and another scope for days level.

CREATE MEMBER CURRENTCUBE.[Measures].[Measure A Key Hour - Hour Level]
 AS null,
VISIBLE = 0;
CREATE MEMBER CURRENTCUBE.[Measures].[Measure A Key Hour - Day Level]
 AS null,
VISIBLE = 0;

SCOPE ([Date].[Date].[Hour],[Measures].[Measure A Key Hour - Hour Level]);
THIS = IIF(([Date].[Date].CurrentMember,[Measures].[Measure A]) = ([Date].[Date].CurrentMember.Parent,[Measures].[Measure A])
, [Date].[Date].CurrentMember.Member_Key, null);
END SCOPE;

SCOPE ([Date].[Date].[Day],[Measures].[Measure A Key Hour - Day Level]);
THIS = MAX([Date].[Date].CurrentMember.Children,[Measures].[Measure A Key Hour - Hour Level]);
END SCOPE;

SCOPE ([Date].[Date].[Day],[Measures].[Measure B]);
THIS = (StrToMember("[Date].[Date].&["+CStr([Measures].[Measure A Key Hour - Day Level])+"]")
,[Measures].[Measure B]);
END SCOPE;

I believe your first measure have logic as MAX of every hour for days or smth like that.

How do I retrieve a measure value based on the value of another measure in SSAS MDX?

1 Answers