MDX查询已加NON EMPTY仍返回空值，求技术协助

我编写的MDX查询已经使用了NON EMPTY关键字，但结果里依然有空值行。查询能正常返回有效数据，但需要移除所有空值行，求技术帮助。以下是我的MDX代码：

WITH
    MEMBER [Measures].[Total Open PO and Planned] as 
        ([Measures].[Quantity Open - P]) + ([Measures].[Planned Order Quantity])

    MEMBER [Measures].[Open Purchase Orders] as
        ([Measures].[Quantity Open - P])

    MEMBER [Measures].[Forecasted Orders] as 
        ([Measures].[Forecast Quantity])

    MEMBER [Measures].[Planned Orders] as
        [Measures].[Planned Order Quantity]

SELECT 
NON EMPTY
    ({
        [Measures].[Total Open PO and Planned],
        [Measures].[Open Purchase Orders],
        [Measures].[Forecasted Orders],
        [Measures].[Planned Orders]
    }) 
    ON COLUMNS,

NON EMPTY
    {
        [Item Branch].[Business Unit Code].CHILDREN
        *
        [Items].[Item Number 02].CHILDREN
    }
    ON ROWS 

FROM
(SELECT 
    (
        {[Items].[P1 Flag].&[]}

) ON COLUMNS FROM
[Buyer]

)

我原本预期NON EMPTY会过滤掉所有空记录，但并未生效，望得到协助。

核心原因 #

NON EMPTY的过滤逻辑是仅移除当前轴上元组在所有列/行度量中完全为空的记录，但如果存在以下情况，它会失效：

1. 底层度量返回0而非NULL（0不属于空值范畴）
2. 计算度量的空值判断逻辑不明确
3. 交叉连接产生的元组，其空值未被NON EMPTY正确评估

具体解决方法 #

方法1：用FILTER明确过滤全空行

通过IsEmpty函数直接判断行元组的所有度量是否均为空，仅保留至少有一个非空值的行：

WITH
    MEMBER [Measures].[Total Open PO and Planned] as 
        ([Measures].[Quantity Open - P]) + ([Measures].[Planned Order Quantity])

    MEMBER [Measures].[Open Purchase Orders] as
        ([Measures].[Quantity Open - P])

    MEMBER [Measures].[Forecasted Orders] as 
        ([Measures].[Forecast Quantity])

    MEMBER [Measures].[Planned Orders] as
        [Measures].[Planned Order Quantity]

SELECT 
    ({
        [Measures].[Total Open PO and Planned],
        [Measures].[Open Purchase Orders],
        [Measures].[Forecasted Orders],
        [Measures].[Planned Orders]
    }) 
    ON COLUMNS,
    FILTER(
        {[Item Branch].[Business Unit Code].CHILDREN * [Items].[Item Number 02].CHILDREN},
        NOT(
            IsEmpty([Measures].[Total Open PO and Planned]) AND
            IsEmpty([Measures].[Open Purchase Orders]) AND
            IsEmpty([Measures].[Forecasted Orders]) AND
            IsEmpty([Measures].[Planned Orders])
        )
    )
    ON ROWS 

FROM
(SELECT 
    {[Items].[P1 Flag].&[]} ON COLUMNS FROM [Buyer]
)

方法2：将底层0值转换为NULL

如果底层度量返回的是0而非NULL，NON EMPTY不会识别为空。可以修改计算度量，把0替换成NULL：

WITH
    MEMBER [Measures].[Total Open PO and Planned] as 
        IIF(
            IsEmpty([Measures].[Quantity Open - P]) AND IsEmpty([Measures].[Planned Order Quantity]),
            NULL,
            ([Measures].[Quantity Open - P]) + ([Measures].[Planned Order Quantity])
        )

    MEMBER [Measures].[Open Purchase Orders] as
        IIF([Measures].[Quantity Open - P] = 0, NULL, [Measures].[Quantity Open - P])

    MEMBER [Measures].[Forecasted Orders] as 
        IIF([Measures].[Forecast Quantity] = 0, NULL, [Measures].[Forecast Quantity])

    MEMBER [Measures].[Planned Orders] as
        IIF([Measures].[Planned Order Quantity] = 0, NULL, [Measures].[Planned Order Quantity])

SELECT 
NON EMPTY
    ({
        [Measures].[Total Open PO and Planned],
        [Measures].[Open Purchase Orders],
        [Measures].[Forecasted Orders],
        [Measures].[Planned Orders]
    }) 
    ON COLUMNS,

NON EMPTY
    {
        [Item Branch].[Business Unit Code].CHILDREN
        *
        [Items].[Item Number 02].CHILDREN
    }
    ON ROWS 

FROM
(SELECT 
    {[Items].[P1 Flag].&[]} ON COLUMNS FROM [Buyer]
)

方法3：在子查询中提前过滤

如果子查询的筛选条件导致大量空行，可以在子查询层面先过滤无效元组：

WITH
    MEMBER [Measures].[Total Open PO and Planned] as 
        ([Measures].[Quantity Open - P]) + ([Measures].[Planned Order Quantity])

    MEMBER [Measures].[Open Purchase Orders] as
        ([Measures].[Quantity Open - P])

    MEMBER [Measures].[Forecasted Orders] as 
        ([Measures].[Forecast Quantity])

    MEMBER [Measures].[Planned Orders] as
        [Measures].[Planned Order Quantity]

SELECT 
    ({
        [Measures].[Total Open PO and Planned],
        [Measures].[Open Purchase Orders],
        [Measures].[Forecasted Orders],
        [Measures].[Planned Orders]
    }) 
    ON COLUMNS,
    NON EMPTY
    {
        [Item Branch].[Business Unit Code].CHILDREN
        *
        [Items].[Item Number 02].CHILDREN
    }
    ON ROWS 

FROM
(SELECT 
    {[Items].[P1 Flag].&[]} ON COLUMNS 
    FROM [Buyer]
    WHERE NON EMPTY ([Item Branch].[Business Unit Code].CHILDREN * [Items].[Item Number 02].CHILDREN)
)

内容的提问来源于stack exchange，提问作者Tom Johnson