SSAS MDX查询中SUM值计算错误，如何排除NULL值？

1. 显式处理NULL值的计算度量值 #

创建持久化的计算度量值时，可通过ISNULL或IIF函数将NULL值转为0，同时配合NON EMPTY过滤无效成员，确保只计算有效数值的总和：

CREATE MEMBER CURRENTCUBE.[Measures].[ValidTotalWaitTime]
AS SUM(
    NON EMPTY [YourDimension].[YourLevel].Members,
    IIF(ISNULL([Measures].[WaitTime]), 0, [Measures].[WaitTime])
),
FORMAT_STRING = "Standard";

如果不需要过滤维度成员，仅将NULL转为0求和，可简化为：

CREATE MEMBER CURRENTCUBE.[Measures].[ValidTotalWaitTime]
AS SUM(
    [YourDimension].[YourLevel].Members,
    IIF(ISNULL([Measures].[WaitTime]), 0, [Measures].[WaitTime])
);

2. 查询层面临时排除NULL值 #

若无需创建持久度量值，可在MDX查询中直接过滤掉WaitTime为NULL的行：

方式一：使用NON EMPTY #

SELECT
    [Measures].[WaitTime] ON 0,
    NON EMPTY [YourDimension].[YourLevel].Members ON 1
FROM [YourCubeName];

方式二：使用FILTER精准控制 #

SELECT
    SUM([Measures].[WaitTime]) ON 0,
    FILTER(
        [YourDimension].[YourLevel].Members,
        NOT ISNULL([Measures].[WaitTime])
    ) ON 1
FROM [YourCubeName];

3. 负数结果的额外排查 #

MDX的SUM函数默认会忽略NULL值，所以总和为负数大概率是数据集本身存在负的WaitTime值。可通过以下查询验证：

SELECT
    [Measures].[WaitTime] ON 0,
    FILTER(
        [YourDimension].[YourLevel].Members,
        [Measures].[WaitTime] < 0
    ) ON 1
FROM [YourCubeName];

如果确实存在负数，需检查数据抽取逻辑是否符合业务规则；若业务上不需要统计负数，可在计算时追加判断：

IIF([Measures].[WaitTime] < 0 OR ISNULL([Measures].[WaitTime]), 0, [Measures].[WaitTime])

内容的提问来源于stack exchange，提问作者munees ms