如何修复MDX错误：遇到无法包含计算成员的集合

问题描述 #

需要展示某一度量的上期差异，在Cube计算区域编写了如下脚本：

CREATE MEMBER CURRENTCUBE.[Analysis Period].[Relative Time].[Previous Period]
AS
    null, 
    VISIBLE = 1;      
SCOPE
(
    [Analysis Period].[Relative Time].[Previous Period],
    [Analysis Period].[Period to Date].ALLMEMBERS
);    
SCOPE
(
   DESCENDANTS (
     [Calendar].[Calendar Hierarchy].[All],
     [Calendar].[Calendar Hierarchy].[Year],
     SELF_AND_AFTER
   )
 );   
THIS = 
(
  [Calendar].[Calendar Hierarchy].PREVMEMBER,
  [Analysis Period].[Relative Time].&[1]
);    
END SCOPE;    
END SCOPE;    

CREATE MEMBER CURRENTCUBE.[Analysis Period].[Relative Time].[Variance PrevPd]
AS
(
  [Analysis Period].[Relative Time].&[1] -
  [Analysis Period].[Relative Time].[Previous Period]
), 
VISIBLE = 1;

使用SSMS生成的查询语句如下：

SELECT
    NON EMPTY {[Measures].[Inflow]} ON COLUMNS
FROM
(
  SELECT
  ( {[Analysis Period].[Relative Time].[Variance PrevPd]} ) ON COLUMNS
  FROM
  (
     SELECT
     ( {[Calendar].[Calendar Month].&[202207]} ) ON COLUMNS
     FROM
     (
       SELECT
       ( {[Fact Type].[Transactions or Holdings].&[Transactions]} ) ON COLUMNS
       FROM [Finscape]
     )
   )
)
WHERE
(
  [Fact Type].[Transactions or Holdings].&[Transactions],
  [Calendar].[Calendar Month].&[202207],
  [Analysis Period].[Relative Time].[Variance PrevPd]
)

执行后报错：

A set has been encountered that cannot contain calculated members.

报错原因 #

这个错误和SCOPE语句无关，核心问题是子查询（Subselect）的轴上不能放置计算成员。子查询的作用是筛选Cube的原生维度成员，而Variance PrevPd是自定义的计算成员，不属于原生维度的成员集合，因此不能放到子查询的COLUMNS/ROWS轴中。

修复方案 #

1. 优化Cube计算脚本（可选，简化逻辑） #

可以去掉SCOPE语句，直接在Previous Period的定义中使用动态成员引用，逻辑更清晰：

CREATE MEMBER CURRENTCUBE.[Analysis Period].[Relative Time].[Previous Period]
AS 
    ([Calendar].[Calendar Hierarchy].CurrentMember.PrevMember, [Analysis Period].[Relative Time].&[1]),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Analysis Period].[Relative Time].[Variance PrevPd]
AS
    [Analysis Period].[Relative Time].&[1] - [Analysis Period].[Relative Time].[Previous Period],
VISIBLE = 1;

无需依赖SCOPE的上下文，直接通过CurrentMember.PrevMember获取上期数据，逻辑更直观。

2. 修改查询语句（关键修复） #

把计算成员从子查询中移到外层查询的轴上，同时简化不必要的嵌套子查询：

SELECT
    NON EMPTY {[Measures].[Inflow]} ON COLUMNS,
    NON EMPTY {[Analysis Period].[Relative Time].[Variance PrevPd]} ON ROWS
FROM [Finscape]
WHERE
(
  [Fact Type].[Transactions or Holdings].&[Transactions],
  [Calendar].[Calendar Month].&[202207]
)

如果需要保留原有的嵌套子查询结构，只需把计算成员移到外层：

SELECT
    NON EMPTY {[Measures].[Inflow]} ON COLUMNS,
    NON EMPTY {[Analysis Period].[Relative Time].[Variance PrevPd]} ON ROWS
FROM
(
  SELECT
  ( {[Calendar].[Calendar Month].&[202207]} ) ON COLUMNS
  FROM
  (
    SELECT
    ( {[Fact Type].[Transactions or Holdings].&[Transactions]} ) ON COLUMNS
    FROM [Finscape]
  )
)
WHERE
(
  [Fact Type].[Transactions or Holdings].&[Transactions],
  [Calendar].[Calendar Month].&[202207]
)

说明 #

子查询仅能用于筛选原生维度的成员，计算成员必须放在外层查询的轴（COLUMNS/ROWS）或者切片器（WHERE）中。原查询中WHERE子句重复了子查询的筛选条件，可根据需要保留或移除，不影响结果。

内容的提问来源于stack exchange，提问作者Ian Wells