如何将含多条件内连接的SQL查询转换为PowerBI的DAX代码？

需求概述 #

现有一段基于CDI表的SQL查询，通过CTE筛选不同QuestionID的子表后执行多条件自连接，最终完成聚合统计。已完成DAX中筛选子表的VAR部分，需实现复杂多条件内连接及后续聚合逻辑。

DAX实现方案 #

DAX完全支持复杂多条件内连接，可通过CROSSJOIN生成笛卡尔积后，用FILTER添加所有连接条件，完全复现SQL中ON子句的逻辑。以下是完整的DAX代码：

BingeDrinkingIntFreq = 
-- 筛选并重命名ALC2_2字段，避免连接时字段冲突
VAR ALC2_2 = 
    SELECTCOLUMNS(
        FILTER(
            'chronic_disease_analyses_db   main   CDI', 
            'chronic_disease_analyses_db   main   CDI'[QuestionID] = "ALC2_2" && 
            ('chronic_disease_analyses_db   main   CDI'[StratificationID] = "B_F_ALL" || 'chronic_disease_analyses_db   main   CDI'[StratificationID] = "B_M_ALL")
        ),
        "LogID", [LogID],
        "AlcPrevID", [QuestionID],
        "AlcPrevDataValue", [DataValue],
        "AlcPrevDataValueUnit", [DataValueUnit],
        "AlcPrevDataValueTypeID", [DataValueTypeID],
        "StratificationID", [StratificationID],
        "LocationID", [LocationID],
        "YearStart", [YearStart],
        "YearEnd", [YearEnd],
        "Population", [Population],
        "BingeDrinkingPopulation", [TotalEvents]
    )

-- 筛选并重命名ALC4_0字段
VAR ALC4_0 = 
    SELECTCOLUMNS(
        FILTER(
            'chronic_disease_analyses_db   main   CDI', 
            'chronic_disease_analyses_db   main   CDI'[QuestionID] = "ALC4_0" && 
            ('chronic_disease_analyses_db   main   CDI'[StratificationID] = "B_F_ALL" || 'chronic_disease_analyses_db   main   CDI'[StratificationID] = "B_M_ALL")
        ),
        "AlcIntID", [QuestionID],
        "AlcIntDataValue", [DataValue],
        "AlcIntDataValueUnit", [DataValueUnit],
        "AlcIntDataValueTypeID", [DataValueTypeID],
        "StratificationID", [StratificationID],
        "LocationID", [LocationID],
        "YearStart", [YearStart],
        "YearEnd", [YearEnd]
    )

-- 筛选并重命名ALC3_0字段
VAR ALC3_0 = 
    SELECTCOLUMNS(
        FILTER(
            'chronic_disease_analyses_db   main   CDI', 
            'chronic_disease_analyses_db   main   CDI'[QuestionID] = "ALC3_0" && 
            ('chronic_disease_analyses_db   main   CDI'[StratificationID] = "B_F_ALL" || 'chronic_disease_analyses_db   main   CDI'[StratificationID] = "B_M_ALL")
        ),
        "AlcFreqID", [QuestionID],
        "AlcFreqDataValue", [DataValue],
        "AlcFreqDataValueUnit", [DataValueUnit],
        "AlcFreqDataValueTypeID", [DataValueTypeID],
        "StratificationID", [StratificationID],
        "LocationID", [LocationID],
        "YearStart", [YearStart],
        "YearEnd", [YearEnd]
    )

-- 连接ALC2_2与ALC4_0，匹配SQL中的多条件
VAR ALC2_2_ALC4_0_Join = 
    FILTER(
        CROSSJOIN(ALC2_2, ALC4_0),
        ((ALC2_2[AlcPrevDataValueTypeID] = "AGEADJPREV" && ALC4_0[AlcIntDataValueTypeID] = "AGEADJMEAN") ||
         (ALC2_2[AlcPrevDataValueTypeID] = "CRDPREV" && ALC4_0[AlcIntDataValueTypeID] = "MEAN")) &&
        ALC2_2[StratificationID] = ALC4_0[StratificationID] &&
        ALC2_2[LocationID] = ALC4_0[LocationID] &&
        ALC2_2[YearStart] = ALC4_0[YearStart] &&
        ALC2_2[YearEnd] = ALC4_0[YearEnd]
    )

-- 连接上述结果与ALC3_0，添加对应多条件
VAR Full_Join = 
    FILTER(
        CROSSJOIN(ALC2_2_ALC4_0_Join, ALC3_0),
        ((ALC2_2_ALC4_0_Join[AlcPrevDataValueTypeID] = "AGEADJPREV" && ALC3_0[AlcFreqDataValueTypeID] = "AGEADJMEAN") ||
         (ALC2_2_ALC4_0_Join[AlcPrevDataValueTypeID] = "CRDPREV" && ALC3_0[AlcFreqDataValueTypeID] = "MEAN")) &&
        ALC2_2_ALC4_0_Join[StratificationID] = ALC3_0[StratificationID] &&
        ALC2_2_ALC4_0_Join[LocationID] = ALC3_0[LocationID] &&
        ALC2_2_ALC4_0_Join[YearStart] = ALC3_0[YearStart] &&
        ALC2_2_ALC4_0_Join[YearEnd] = ALC3_0[YearEnd]
    )

-- 执行聚合统计，匹配SQL的GROUP BY逻辑
VAR Aggregated_Result = 
    SUMMARIZE(
        Full_Join,
        -- 分组字段对应SQL的GROUP BY
        ALC2_2_ALC4_0_Join[LocationID],
        ALC2_2_ALC4_0_Join[StratificationID],
        ALC2_2_ALC4_0_Join[YearStart],
        ALC2_2_ALC4_0_Join[YearEnd],
        ALC2_2_ALC4_0_Join[AlcPrevID],
        ALC2_2_ALC4_0_Join[AlcIntID],
        ALC3_0[AlcFreqID],
        -- 聚合计算字段
        "LogID", MAX(ALC2_2_ALC4_0_Join[LogID]),
        "AvgAlcPrevDataValue", AVERAGE(ALC2_2_ALC4_0_Join[AlcPrevDataValue]),
        "AvgAlcIntDataValue", AVERAGE(ALC2_2_ALC4_0_Join[AlcIntDataValue]),
        "AvgAlcFreqDataValue", AVERAGE(ALC3_0[AlcFreqDataValue]),
        "AvgBingeDrinkingPopInt", AVERAGE(ALC2_2_ALC4_0_Join[BingeDrinkingPopulation] * ALC2_2_ALC4_0_Join[AlcIntDataValue]),
        "AvgBingeDrinkingPopFreq", AVERAGE(ALC2_2_ALC4_0_Join[BingeDrinkingPopulation] * ALC3_0[AlcFreqDataValue]),
        "AvgBingeDrinkingPopulation", AVERAGE(ALC2_2_ALC4_0_Join[BingeDrinkingPopulation])
    )

RETURN Aggregated_Result

代码说明 #

1. 字段重命名：用SELECTCOLUMNS给每个子表的字段重命名，避免多表连接时的字段冲突，同时与SQL中别名保持一致。
2. 多条件连接：通过CROSSJOIN生成表的笛卡尔积，再用FILTER添加所有连接条件，包括OR与AND组合的复杂逻辑，完全复现SQL的ON子句。
3. 分步连接：先连接ALC2_2与ALC4_0，再将结果与ALC3_0连接，减少一次性多表交叉连接的性能开销。
4. 聚合统计：用SUMMARIZE实现SQL的GROUP BY和聚合计算，分组字段与聚合函数完全匹配原SQL逻辑。

内容的提问来源于stack exchange，提问作者Mig Rivera Cueva