DAX度量值问题：基于双表状态计算营销活动时长

问题背景 #

现有两张表：

• Table1：存储唯一国家名称及状态（取值为Running或Completed）
• Table2：存储含重复项的国家名称、创建日期（Created）和修改日期（Modified）

需求：编写DAX度量值，实现以下逻辑：

• 当Table1中国家状态为Running时，计算今日与Table2中该国家最小创建日期的天数差
• 当状态为Completed时，计算Table2中该国家最大修改日期与最小创建日期的天数差

示例数据 #

Table1

Table2

今日固定日期：10.01.2024

预期结果 #

• 选中Spain时：10.01.2024 - 01.01.2024 = 9天，输出「活动进行中 9 天」
• 选中UK时：04.01.2024 - 01.01.2024 = 3天，输出「活动已完成 3 天」

用户原DAX代码 #

CampaignDuration = 

var running_days = -DATEDIFF(TODAY(),MIN('Table2'[Created]),WEEK)

var closed_days = -DATEDIFF(MAX('COI_Merged_All'[Modified]),MIN('Table2'[Created]),WEEK)

VAR Campaign_Status = MAX('Table1'[status])

var result = 
IF(Campaign_Status = "Completed", 
"completed in" & " " & closed_days & " " & "weeks", 
"ongoing" & " " & running_days & " " & "weeks")

RETURN

result

问题分析 #

1. 表名错误：COI_Merged_All应为Table2，导致引用无效
2. 单位错误：需求计算天数，原代码用WEEK作为差值单位
3. 上下文逻辑问题：未明确筛选当前国家对应的日期，可能出现跨国家的日期计算
4. 文本输出不符合中文需求，且大小写匹配存在潜在风险
5. DATEDIFF参数顺序反向，需用负号修正，可读性差

修正后的DAX度量值 #

CampaignDuration = 
VAR CurrentCountry = SELECTEDVALUE('Table1'[Country])
VAR MinCreated = CALCULATE(MIN('Table2'[Created]), 'Table2'[Country] = CurrentCountry)
VAR MaxModified = CALCULATE(MAX('Table2'[Modified]), 'Table2'[Country] = CurrentCountry)
VAR CampaignStatus = SELECTEDVALUE('Table1'[Status])
-- 示例用固定日期，实际使用可替换为 TODAY()
VAR TodayDate = DATE(2024, 1, 10)

VAR DurationDays = 
    SWITCH(
        TRUE(),
        CampaignStatus = "Running", DATEDIFF(MinCreated, TodayDate, DAY),
        CampaignStatus = "Completed", DATEDIFF(MinCreated, MaxModified, DAY),
        BLANK()
    )

VAR ResultText = 
    SWITCH(
        TRUE(),
        CampaignStatus = "Running", "活动进行中 " & DurationDays & " 天",
        CampaignStatus = "Completed", "活动已完成 " & DurationDays & " 天",
        "状态未知"
    )

RETURN ResultText

代码说明 #

1. 使用SELECTEDVALUE精准获取当前上下文的国家和状态，避免歧义
2. 用CALCULATE配合筛选条件，确保只计算当前国家对应的日期数据
3. 替换WEEK为DAY，匹配需求的天数计算
4. 调整DATEDIFF参数顺序，无需负号，逻辑更直观
5. 用SWITCH替代嵌套IF，提升代码可读性
6. 预留动态日期入口，实际场景可将TodayDate替换为TODAY()
7. 输出中文文本，完全匹配预期格式

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