MDX中Except、Distinct使用问题：客户忠诚度KPI计算结果始终为0求助

首先，先明确你的需求：你要计算的客户忠诚度KPI公式是(X1年客户数 - X2年新客户数)/X1年客户数，这里X1对应上一年、X2对应当前年，本质上这个公式等价于上一年客户中留存到当前年的比例（因为X2年新客户是当前年有但上一年没有的客户，X1年客户数减去新客户数，剩下的就是上一年有且当前年也有的留存客户）。

现在来看你的MDX查询，结果始终为0，大概率是这几个原因导致的：

1. EXCEPT的集合维度不匹配 #

你在EXCEPT里传入的是包含客户、时间成员、度量值的元组集合，而不是单纯的客户成员集合。MDX中EXCEPT是用来比较同维度成员的，元组里的时间成员（上一年vs当前年）完全不同，导致两个集合没有交集，EXCEPT返回的就是第一个集合的全部内容。但如果后续计算时出现了整数除法或者集合计数异常，就会得到0。

正确的做法是先分别提取上一年有交易的客户集合和当前年有交易的客户集合，再基于这两个纯客户集合做计算：

WITH 
-- 上一年有交易的客户（过滤掉无Remise Facture的客户）
SET [LastYear_Clients] AS 
    Nonempty(
        [Dim Client].[PK Client].[PK Client],
        ([Dim Temps 3].[Annee Nom].CurrentMember.PrevMember, [Measures].[Remise Facture])
    )
-- 当前年有交易的客户
SET [CurrentYear_Clients] AS 
    Nonempty(
        [Dim Client].[PK Client].[PK Client],
        ([Dim Temps 3].[Annee Nom].CurrentMember, [Measures].[Remise Facture])
    )
-- 当前年的新客户（今年有但去年没有）
SET [CurrentYear_NewClients] AS 
    EXCEPT([CurrentYear_Clients], [LastYear_Clients])
-- 定义计算度量值
MEMBER [Measures].[LastYear_ClientCount] AS 
    Count([LastYear_Clients])
MEMBER [Measures].[NewClient_Count] AS 
    Count([CurrentYear_NewClients])
MEMBER [Measures].[LoyaltyKPI] AS 
    -- 乘以1.0强制转换为浮点数，避免整数除法导致结果为0
    ([Measures].[LastYear_ClientCount] - [Measures].[NewClient_Count]) * 1.0 / [Measures].[LastYear_ClientCount]
SELECT 
    [Measures].[LoyaltyKPI] ON 0,
    [Dim Temps 3].[Annee Nom].[Annee Nom] ON 1
FROM [YourCubeName]

2. 整数除法导致结果被截断 #

如果你的分子和分母都是整数类型，MDX会执行整数除法，比如(100-30)/100会得到0而不是0.7。上面的公式里我加了*1.0来强制把结果转换成浮点数，解决这个问题。

3. CurrentMember.PrevMember未正确获取上一年 #

如果当前选中的时间成员是维度里的第一年，PrevMember会返回空值，导致[LastYear_Clients]是空集合，此时分子分母都是0，计算结果会是null或者0（取决于MDX引擎的处理）。你可以检查一下查询时的时间成员是否有有效的上一年数据。

4. Nonempty过滤逻辑可能不符合预期 #

Nonempty是基于指定的度量值是否为空来过滤客户，如果[Measures].[Remise Facture]在某些客户的记录中是0而不是null，Nonempty不会过滤掉这些客户。如果你的业务中“有交易”是指Remise Facture>0，那应该改成：

SET [LastYear_Clients] AS 
    Filter(
        [Dim Client].[PK Client].[PK Client],
        ([Dim Temps 3].[Annee Nom].CurrentMember.PrevMember, [Measures].[Remise Facture]) > 0
    )

你可以先测试单独计算[LastYear_ClientCount]和[NewClient_Count]的值，看看是否符合预期，再逐步排查KPI的计算结果。

内容的提问来源于stack exchange，提问作者Mohammed El Mehdi Kherrachi