MDX Multi Select Divide by CurrentMember.Properties

Question

Error: "The MDX function CURRENTMEMBER failed because the coordinate for the 'YYYYMM' attribute contains a set."

I have a calculation that works when only a single months data is returned. My Calendar dimension contains a single record per year month (Key: YYYYMM) and includes a property of "Man Hours".

As I understand it this is due to the implementation or lack there of, of multi select support.

This is T-SQL that would returen the expected value.

SELECT CostPerManHour = SUM(MonthCost) / SUM(ManHours)
FROM ( 
    SELECT 
        MonthCost = SUM(C.Cost), 
        Cal.ManHours 
    FROM dbo.Costs C
    JOIN dbo.Calendar Cal ON C.YYYYMM = Cal.YYYYMM
    WHERE Cal.YYYYMM BETWEEN 201503 AND 201705
      AND C.OtherThing = 'Thing1'
    GROUP BY Cal.YYYYMM, Cal.ManHours 
    ) Q
;

MDX:

SELECT [Measures].[Cost Per Man Hour],... ON 0
FROM Metrics
WHERE { 
    { [Calendar].[YYYYMM].&[201503] : [Calendar].[YYYYMM].&[201705] }
  * { [Other Thing].[Other Thing].&[Thing1] }
};

Calculation

CREATE MEMBER CURRENTCUBE.[Measures].[Cost Per Man Hour] 
 AS DIVIDE([Measures].[Cost], [CALENDAR].[YYYYMM].CURRENTMEMBER.PROPERTIES ("Man Hours")), 
VISIBLE = 1,  ASSOCIATED_MEASURE_GROUP = 'Costs';

Danylo Korostil Danylo Korostil · Accepted Answer · 2017-10-10T11:03:51

Try it:

CREATE MEMBER CURRENTCUBE.[Measures].[Cost Per Man Hour] 
 AS 
 DIVIDE(
    [Measures].[Cost],
    SUM(
        existing [Calendar].[YYYYMM].[YYYYMM].Members,
        [CALENDAR].[YYYYMM].CURRENTMEMBER.PROPERTIES ("Man Hours")
    )
 ), 
VISIBLE = 1,  ASSOCIATED_MEASURE_GROUP = 'Costs';

MDX Multi Select Divide by CurrentMember.Properties

1 Answers