如何调整DAX代码实现按设施编码、月份、指标的聚合逆透视

问题描述 #

从ODBC数据源读取数据后，使用DAX创建度量值，尝试将宽表转换为目标长表格式。现有DAX代码仅能按IndicatorName聚合，无法实现按FacilityCode、Month、IndicatorName三个维度分组聚合。

现有代码 #

risk - MTD = UNION(
SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[CV] >=0), "FacilityCode", 'RSQL'[FacilityCode], "Month", 'RSQL'[Month], "IndicatorName", "CV"),
SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[Closed] >=0), "FacilityCode", 'RSQL'[FacilityCode], "Month", 'RSQL'[Month], "IndicatorName", "CP"),
SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[EF] >=0), "FacilityCode", 'RSQL'[FacilityCode], "Month", 'RSQL'[Month], "IndicatorName", "EF"),
SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[IOF] >=0), "FacilityCode", 'RSQL'[FacilityCode], "Month", 'RSQL'[Month], "IndicatorName", "IOF"),
SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[MRP] >=0), "FacilityCode", 'RSQL'[FacilityCode], "Month", 'RSQL'[Month], "IndicatorName", "MRP"),
SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[MR] >=0), "FacilityCode", 'RSQL'[FacilityCode], "Month", 'RSQL'[Month], "IndicatorName", "MR"),
SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[OF] >=0), "FacilityCode", 'RSQL'[FacilityCode], "Month", 'RSQL'[Month], "IndicatorName", "OF"),
SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[AP] >=0), "FacilityCode", 'RSQL'[FacilityCode], "Month", 'RSQL'[Month], "IndicatorName", "AP"))

问题分析 #

现有代码仅提取了FacilityCode、Month、IndicatorName三个维度列，未包含对应指标的数值数据，且未对多维度组合做聚合处理，导致结果仅按IndicatorName聚合。

调整后代码 #

risk - MTD = 
VAR LongTable = UNION(
    SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[CV] >= 0), 
        "FacilityCode", 'RSQL'[FacilityCode], 
        "Month", 'RSQL'[Month], 
        "IndicatorName", "CV",
        "IndicatorValue", 'RSQL'[CV]
    ),
    SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[Closed] >= 0), 
        "FacilityCode", 'RSQL'[FacilityCode], 
        "Month", 'RSQL'[Month], 
        "IndicatorName", "CP",
        "IndicatorValue", 'RSQL'[Closed]
    ),
    SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[EF] >= 0), 
        "FacilityCode", 'RSQL'[FacilityCode], 
        "Month", 'RSQL'[Month], 
        "IndicatorName", "EF",
        "IndicatorValue", 'RSQL'[EF]
    ),
    SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[IOF] >= 0), 
        "FacilityCode", 'RSQL'[FacilityCode], 
        "Month", 'RSQL'[Month], 
        "IndicatorName", "IOF",
        "IndicatorValue", 'RSQL'[IOF]
    ),
    SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[MRP] >= 0), 
        "FacilityCode", 'RSQL'[FacilityCode], 
        "Month", 'RSQL'[Month], 
        "IndicatorName", "MRP",
        "IndicatorValue", 'RSQL'[MRP]
    ),
    SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[MR] >= 0), 
        "FacilityCode", 'RSQL'[FacilityCode], 
        "Month", 'RSQL'[Month], 
        "IndicatorName", "MR",
        "IndicatorValue", 'RSQL'[MR]
    ),
    SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[OF] >= 0), 
        "FacilityCode", 'RSQL'[FacilityCode], 
        "Month", 'RSQL'[Month], 
        "IndicatorName", "OF",
        "IndicatorValue", 'RSQL'[OF]
    ),
    SELECTCOLUMNS(FILTER('RSQL', 'RSQL'[AP] >= 0), 
        "FacilityCode", 'RSQL'[FacilityCode], 
        "Month", 'RSQL'[Month], 
        "IndicatorName", "AP",
        "IndicatorValue", 'RSQL'[AP]
    )
)
RETURN
SUMMARIZE(
    LongTable,
    LongTable[FacilityCode],
    LongTable[Month],
    LongTable[IndicatorName],
    "AggregatedValue", SUM(LongTable[IndicatorValue])
)

代码说明 #

1. 生成长表：通过UNION+SELECTCOLUMNS将宽表中各指标列转换为长表格式，新增IndicatorValue列存储对应指标的数值。
2. 多维度聚合：使用SUMMARIZE按FacilityCode、Month、IndicatorName三个维度分组，对IndicatorValue执行聚合计算（示例用SUM，可根据需求替换为COUNT/AVERAGE等）。

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