MDX calculated member cannot be used as a parent of another calculated member

Question

I have a MDX query, which getting measure from period and show it as a next date level:

WITH 
  MEMBER [Creation Date].[2017].[2017/01].[2017/01].[2017/01/11] AS 
    Aggregate
    (
      Filter
      (
        [Creation Date].[Minute].MEMBERS
       ,
          [Creation Date].[Minute].CurrentMember.Name >= '2017/01/11 07:45'
        AND 
          [Creation Date].[Minute].CurrentMember.Name <= '2017/01/11 23:59'
      )
    ) 
SELECT 
  {[Measures].[Sales Count]} ON COLUMNS
 ,{[Creation Date].[2017].[2017/01].[2017/01].[2017/01/11]} ON ROWS
FROM [Sales Star Schema];

and it returns exception: The '[Creation Date].[2017]' calculated member cannot be used as a parent of another calculated member.

2017 year does not contain any measure values in aggregate tables, but if I try query like this:

WITH 
  MEMBER [Creation Date].[2016].[2016/01].[2016/01].[2016/01/11] AS 
    Aggregate
    (
      Filter
      (
        [Creation Date].[Minute].MEMBERS
       ,
          [Creation Date].[Minute].CurrentMember.Name >= '2016/01/11 07:45'
        AND 
          [Creation Date].[Minute].CurrentMember.Name <= '2016/01/11 23:59'
      )
    ) 
SELECT 
  {[Measures].[Sales Count]} ON COLUMNS
 ,{[Creation Date].[2016].[2016/01].[2016/01].[2016/01/11]} ON ROWS
FROM [Sales Star Schema];

this query returns me a correct value, because aggregated views contains value for 2016 year, 2016/01 month, 2016/01/11 day. How i might to change my query structure so that it returns me not an exception in 2017 year's values, but an empty value?

whytheq whytheq · Accepted Answer · 2016-10-04T11:47:50

Calculated members are children of the [All] member of a chosen hierarchy ... however you dress them up this is always the case so better to just explicitly put them there in your scripts to save confusion.

So I assume there is a hierarchy called [Year]:

WITH 
  MEMBER [Creation Date].[Year].[All].[2016/01/11] AS 
    Aggregate
    (
      Filter
      (
        [Creation Date].[Minute].MEMBERS
       ,
          [Creation Date].[Minute].CurrentMember.Name >= '2016/01/11 07:45'
        AND 
          [Creation Date].[Minute].CurrentMember.Name <= '2016/01/11 23:59'
      )
    ) 
  MEMBER [Creation Date].[Year].[All].[2017/01/11] AS 
    Aggregate
    (
      Filter
      (
        [Creation Date].[Minute].MEMBERS
       ,
          [Creation Date].[Minute].CurrentMember.Name >= '2017/01/11 07:45'
        AND 
          [Creation Date].[Minute].CurrentMember.Name <= '2017/01/11 23:59'
      )
    ) 
SELECT 
  {[Measures].[Sales Count]} ON COLUMNS
 ,{
   [Creation Date].[Year].[All].[2016/01/11],
   [Creation Date].[Year].[All].[2017/01/11] 
  } ON ROWS
FROM [Sales Star Schema];

MDX calculated member cannot be used as a parent of another calculated member

1 Answers