PowerBI DAX计算表实现类For循环批量处理多门店代码

原有DAX计算表代码通过硬编码'shops'[code] = "100"实现特定门店组的二级门店分析，现在需要扩展为遍历独立Codes表的每一条记录，批量执行相同逻辑并汇总所有结果，同时新增列标注对应Codes表中的门店名称。

原硬编码代码 #

ShopsTable = 
VAR PrimaryShops =
    CALCULATETABLE (
        VALUES ( 'shops'[_id] ),
        'shops'[code] = "100",
        'shops'[phase] = "Primary"
    )
VAR PrimaryEmployees =
    CALCULATETABLE (
        DISTINCT ( VALUES ( 'employees_shops'[employee] ) ),
        'employees_shops'[phase] = "Primary" &&
        'employees_shops'[shop] IN PrimaryShops 
    )
VAR SecondaryEmployeeIDs =
    CALCULATETABLE (
        VALUES ( 'employees_shops'[employee] ),
        'employees_shops'[phase] = "Secondary" &&
        'employees_shops'[employee] IN PrimaryShops
    )
VAR SecondaryShops =
    CALCULATETABLE (
        VALUES ( 'employees_shops'[shop] ),
        'employees_shops'[phase] = "Secondary" &&
        'employees_shops'[employee] IN SecondaryEmployeeIDs
    )
VAR SecondaryShopsWithCounts =
    ADDCOLUMNS (
        SUMMARIZE (
            SecondaryShops, 
            'employees_shops'[shop], 
            "name", MAXX ( FILTER ( 'shops', 'shops'[_id] = 'employees_shops'[shop] ), 'shops'[name] ),
        "type",
            IF (
                MAXX ( FILTER ( 'shops', 'shops'[_id] = 'employees_shops'[shop] ), 'shops'[code] ) = "100",
                "SecondaryInGroup",
                "SecondaryOutOfGroup"
            ),
        "_id", MAXX ( FILTER ( 'shops', 'shops'[_id] = 'employees_shops'[shop] ), 'shops'[_id] )
    )
RETURN
    SecondaryShopsWithCounts

注：原代码中'schools'[_id]疑似笔误，已修正为'shops'[_id]

扩展后的批量处理代码 #

利用DAX的GENERATE函数实现类似循环的遍历逻辑，对Codes表中的每一行执行原有分析逻辑，并将结果合并：

ShopsTable_Batch = 
GENERATE(
    // 遍历Codes表的每一条记录
    'Codes',
    VAR CurrentCode = 'Codes'[Code]
    VAR CurrentCodeName = 'Codes'[Name]
    // 原有逻辑替换硬编码为当前遍历的Code
    VAR PrimaryShops =
        CALCULATETABLE (
            VALUES ( 'shops'[_id] ),
            'shops'[code] = CurrentCode,
            'shops'[phase] = "Primary"
        )
    VAR PrimaryEmployees =
        CALCULATETABLE (
            DISTINCT ( VALUES ( 'employees_shops'[employee] ) ),
            'employees_shops'[phase] = "Primary" &&
            'employees_shops'[shop] IN PrimaryShops 
        )
    VAR SecondaryEmployeeIDs =
        CALCULATETABLE (
            VALUES ( 'employees_shops'[employee] ),
            'employees_shops'[phase] = "Secondary" &&
            'employees_shops'[employee] IN PrimaryEmployees
        )
    VAR SecondaryShops =
        CALCULATETABLE (
            VALUES ( 'employees_shops'[shop] ),
            'employees_shops'[phase] = "Secondary" &&
            'employees_shops'[employee] IN SecondaryEmployeeIDs
        )
    VAR SecondaryShopsWithDetails =
        ADDCOLUMNS (
            SUMMARIZE (
                SecondaryShops, 
                'employees_shops'[shop]
            ),
            // 新增Codes表对应的门店名称列
            "SourceCodeName", CurrentCodeName,
            "ShopName", MAXX ( FILTER ( 'shops', 'shops'[_id] = 'employees_shops'[shop] ), 'shops'[name] ),
            "ShopType",
                IF (
                    MAXX ( FILTER ( 'shops', 'shops'[_id] = 'employees_shops'[shop] ), 'shops'[code] ) = CurrentCode,
                    "SecondaryInGroup",
                    "SecondaryOutOfGroup"
                ),
            "ShopID", MAXX ( FILTER ( 'shops', 'shops'[_id] = 'employees_shops'[shop] ), 'shops'[_id] )
        )
    RETURN
        SecondaryShopsWithDetails
)

关键说明 #

• GENERATE函数会将第一个参数（Codes表）的每一行，与第二个参数（针对当前行的计算结果表）进行笛卡尔积合并，实现遍历批量处理的效果
• 将原硬编码的"100"替换为当前遍历的CurrentCode变量，确保每个Code都执行独立的分析逻辑
• 新增SourceCodeName列，标注当前结果对应的Codes表中的门店名称
• 调整列名使其语义更清晰，同时修正原代码中的疑似笔误

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