基于DAX计算多条件客户重复来电率的Power BI技术求助

问题分析 #

你当前的度量值仅覆盖当日成功接通的重复来电场景，且聚合到周/月维度时，因直接对周期内客户去重而非累加每日结果，导致数值偏小。需同时解决未接通弃电的重复判定和周期聚合逻辑两个核心问题。

前置准备 #

1. 确保数据中有标记来电状态的字段（如Call Status，值为"成功接通"/"未接通"），以及唯一标识来电的字段（如Call ID）。
2. 建议创建独立的Date日期表并与Repeated Contact[Start date]建立关系，这是Power BI时间智能和周期聚合的最佳实践。

步骤1：计算首次成功来电日期（计算列） #

先给每个客户标记首次成功接通的日期，用于未接通弃电的重复判定：

First Successful Date = 
CALCULATE(
    MIN('Repeated Contact'[Start date]),
    ALLEXCEPT('Repeated Contact', 'Repeated Contact'[Customer]),
    'Repeated Contact'[Call Status] = "成功接通"
)

注：如果客户从未成功接通，该字段会返回空值，可根据业务需求决定是否纳入统计。

步骤2：日级重复客户数度量值 #

2.1 成功接通的重复客户数 #

仅统计当日成功接通且来电次数>1的客户：

Repeated Successful Customers (Daily) = 
VAR CurrentDate = MAX('Repeated Contact'[Start date])
-- 获取当日成功接通的所有客户
VAR DailySuccessfulCustomers = CALCULATETABLE(
    VALUES('Repeated Contact'[Customer]),
    'Repeated Contact'[Start date] = CurrentDate,
    'Repeated Contact'[Call Status] = "成功接通"
)
-- 筛选出当日来电次数>1的客户
VAR RepeatedCustomers = FILTER(
    DailySuccessfulCustomers,
    CALCULATE(
        COUNT('Repeated Contact'[Call ID]),
        ALLEXCEPT('Repeated Contact', 'Repeated Contact'[Customer], 'Repeated Contact'[Start date]),
        'Repeated Contact'[Call Status] = "成功接通"
    ) > 1
)
RETURN COUNTROWS(RepeatedCustomers)

2.2 未接通弃电的重复客户数 #

统计弃电当日至首次成功来电前，来电次数>1的客户：

Repeated Abandoned Customers (Daily) = 
VAR CurrentDate = MAX('Repeated Contact'[Start date])
-- 获取当日未接通的所有客户
VAR DailyAbandonedCustomers = CALCULATETABLE(
    VALUES('Repeated Contact'[Customer]),
    'Repeated Contact'[Start date] = CurrentDate,
    'Repeated Contact'[Call Status] = "未接通"
)
-- 筛选出有首次成功来电记录的客户（排除从未成功接通的情况）
VAR CustomersWithSuccess = FILTER(
    DailyAbandonedCustomers,
    NOT ISBLANK('Repeated Contact'[First Successful Date])
)
-- 判断当前日期是否在[弃电日, 首次成功日-1]区间内，且该客户在此区间内来电次数>1
VAR RepeatedAbandoned = FILTER(
    CustomersWithSuccess,
    CurrentDate < 'Repeated Contact'[First Successful Date] &&
    CALCULATE(
        COUNT('Repeated Contact'[Call ID]),
        ALLEXCEPT('Repeated Contact', 'Repeated Contact'[Customer]),
        'Repeated Contact'[Start date] >= CurrentDate,
        'Repeated Contact'[Start date] < 'Repeated Contact'[First Successful Date]
    ) > 1
)
RETURN COUNTROWS(RepeatedAbandoned)

2.3 日级总重复客户数 #

合并两个场景的结果：

Total Repeated Customers (Daily) = 
[Repeated Successful Customers (Daily)] + [Repeated Abandoned Customers (Daily)]

步骤3：周/月维度聚合度量值 #

通过迭代每日结果实现累加，避免直接周期去重的问题：

Total Repeated Customers (Period) = 
SUMX(
    VALUES(Date[Date]), -- 迭代当前筛选范围内的每个日期
    CALCULATE([Total Repeated Customers (Daily)])
)

若无独立日期表，可替换为VALUES('Repeated Contact'[Start date])，但推荐使用日期表保证时间筛选的准确性。

步骤4：重复来电率计算 #

4.1 日级总来电客户数 #

Total Customers (Daily) = 
CALCULATE(
    DISTINCTCOUNT('Repeated Contact'[Customer]),
    ALLEXCEPT(
        'Repeated Contact',
        'Repeated Contact'[Start date],
        'Repeated Contact'[Division Group],
        'Repeated Contact'[Division],
        'Repeated Contact'[Queue_Dep],
        'Repeated Contact'[Queue_Type],
        'Repeated Contact'[Name]
    )
)

4.2 周期总来电客户数 #

Total Customers (Period) = 
SUMX(
    VALUES(Date[Date]),
    CALCULATE([Total Customers (Daily)])
)

4.3 重复来电率 #

Repeat Call Rate = 
DIVIDE(
    [Total Repeated Customers (Period)],
    [Total Customers (Period)],
    0 -- 处理分母为0的情况，返回0
)

筛选器适配 #

所有度量值均保留了Division Group、Queue_Dep等维度的上下文，当你在报表中选择国家、队列、时间范围等筛选条件时，计算结果会自动同步更新。

内容的提问来源于stack exchange，提问作者Peter Novak