DAX筛选公式与联合表问题求助：调整筛选逻辑后结果异常

原公式与调整需求 #

原筛选公式：

L_RM = FILTER(L_BRT,(L_BRT[Due Date]>= DATE(YEAR(TODAY())-1,1,1)) && 
(L_BRT[Type] IN {"HR Training", "Issue-Remediation", "PDMS", "Policy Deviation","SoD User Account Access", "SoD Service Account Management", "SoD Entitlement Verification","Action Plan"})
&& (L_BRT[Mandatory/Required Training] IN {"Mandatory", BLANK()}))

调整需求：

• HR Training类型仅保留OfficialITDueDateRM = "Y"的项
• 添加2024年1月1日起生效的新类型：
  • Network Pentest Finding
  • LCM Model - EOL
  • LCM Model - Missing Attribute(s)
  • Security Baseline-Attestation
  • Security Baseline-Baseline Amendment
  • Security Baseline-Baseline Deviation
  • Security Baseline-Existing Baseline
  • MCT Exceptions
  • Compliance
• 同时包含2024年1月1日起的Required培训

尝试的方案与问题 #

方案1：单FILTER公式（结果不正确） #

L_RM = FILTER(L_BRT,(    (L_BRT[Due Date] >= DATE(YEAR(TODAY()) - 1, 1, 1)     && ( (L_BRT[Type] = "HR Training" && L_BRT[OfficialITDueDateRM] = "Y")    || ( L_BRT[Type] IN { "Issue Remediation", "PDMS", "Policy Deviation", "SoD User Account Access", "SoD Service Account Management", "SoD Entitlement Verification", "Action Plan" }    && L_BRT[Mandatory/Required Training] = "Mandatory"))    || (( L_BRT[Mandatory/Required Training] = "Required" && L_BRT[Due Date] >= DATE(2024, 1, 1) )    || ( L_BRT[Due Date] >= DATE(2024, 1, 1) && L_BRT[Type] IN {"Compliance", "Network Pentest Finding", "MCT Exceptions", "LCM Model - EOL", "LCM Model - Missing Attribute(s)", "Security Baseline-Attestation", "Security Baseline-Baseline Amendment", "Security Baseline- Baseline Deviation", "Security Baseline Existing Baseline" }     )))))

方案2：UNION+DISTINCT（单表正确，联合后出现错误值） #

L_RM1 = FILTER(L_BRT,(L_BRT[Due Date]>= DATE(YEAR(TODAY())-1,1,1)) && ( (L_BRT[Type] = "HR Training" && L_BRT[OfficialITDueDateRM] = "Y")    ||(L_BRT[Type] IN {"Issue-Remediation", "PDMS", "Policy Deviation","SoD User Account Access", "SoD Service Account Management", "SoD Entitlement Verification","Action Plan"}) && (L_BRT[Mandatory/Required Training] IN {"Mandatory", BLANK()})))

L_RM2 = FILTER(L_BRT,(L_BRT[Due Date]>=  DATE(2024, 1, 1) && (L_BRT[Type] IN {"Network Pentest Finding", "MCT Exceptions", "LCM Model - EOL", "LCM Model - Missing Attribute(s)", "Security Baseline-Attestation", "Security Baseline-Baseline Amendment", "Security Baseline- Baseline Deviation", "Security Baseline Existing Baseline", "Compliance"}) && (L_BRT[Mandatory/Required Training] IN {"Required", BLANK()})))

L_RM = DISTINCT (UNION (L_RM1, L_RM2))

问题分析与修复方案 #

方案2错误原因 #

UNION要求两个表的列名、列数、数据类型完全一致，若L_RM1和L_RM2因筛选逻辑导致列数据类型隐性变化，或列顺序不一致，会出现错误值。此外原L_RM2的类型列表存在拼写错误：

• Security Baseline- Baseline Deviation多了空格，应为Security Baseline-Baseline Deviation
• Security Baseline Existing Baseline少了连字符，应为Security Baseline-Existing Baseline

修复后的方案2 #

先修正类型拼写，确保表结构一致：

L_RM1 = 
FILTER(
    L_BRT,
    L_BRT[Due Date] >= DATE(YEAR(TODAY()) - 1, 1, 1) &&
    (
        (L_BRT[Type] = "HR Training" && L_BRT[OfficialITDueDateRM] = "Y") ||
        (
            L_BRT[Type] IN {
                "Issue-Remediation", 
                "PDMS", 
                "Policy Deviation",
                "SoD User Account Access", 
                "SoD Service Account Management", 
                "SoD Entitlement Verification",
                "Action Plan"
            } &&
            L_BRT[Mandatory/Required Training] IN {"Mandatory", BLANK()}
        )
    )
)

L_RM2 = 
FILTER(
    L_BRT,
    L_BRT[Due Date] >= DATE(2024, 1, 1) &&
    (
        (
            L_BRT[Type] IN {
                "Network Pentest Finding", 
                "MCT Exceptions", 
                "LCM Model - EOL", 
                "LCM Model - Missing Attribute(s)", 
                "Security Baseline-Attestation", 
                "Security Baseline-Baseline Amendment", 
                "Security Baseline-Baseline Deviation", 
                "Security Baseline-Existing Baseline", 
                "Compliance"
            } &&
            L_BRT[Mandatory/Required Training] IN {"Required", BLANK()}
        ) ||
        (L_BRT[Mandatory/Required Training] = "Required")
    )
)

L_RM = DISTINCT(UNION(L_RM1, L_RM2))

更简洁的单FILTER方案（推荐） #

拆分逻辑为三个互斥条件块，避免括号层级混乱：

L_RM = 
FILTER(
    L_BRT,
    -- 条件1：原有类型（HR需符合条件），时间范围近1年
    (
        L_BRT[Due Date] >= DATE(YEAR(TODAY()) - 1, 1, 1) &&
        (
            (L_BRT[Type] = "HR Training" && L_BRT[OfficialITDueDateRM] = "Y") ||
            (
                L_BRT[Type] IN {
                    "Issue-Remediation", 
                    "PDMS", 
                    "Policy Deviation",
                    "SoD User Account Access", 
                    "SoD Service Account Management", 
                    "SoD Entitlement Verification",
                    "Action Plan"
                } &&
                L_BRT[Mandatory/Required Training] IN {"Mandatory", BLANK()}
            )
        )
    ) ||
    -- 条件2：2024起的新类型，支持Required/Blank
    (
        L_BRT[Due Date] >= DATE(2024, 1, 1) &&
        L_BRT[Type] IN {
            "Network Pentest Finding", 
            "MCT Exceptions", 
            "LCM Model - EOL", 
            "LCM Model - Missing Attribute(s)", 
            "Security Baseline-Attestation", 
            "Security Baseline-Baseline Amendment", 
            "Security Baseline-Baseline Deviation", 
            "Security Baseline-Existing Baseline", 
            "Compliance"
        } &&
        L_BRT[Mandatory/Required Training] IN {"Required", BLANK()}
    ) ||
    -- 条件3：2024起的Required培训
    (
        L_BRT[Due Date] >= DATE(2024, 1, 1) &&
        L_BRT[Mandatory/Required Training] = "Required"
    )
)

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