如何在DAX Studio中对Datatable执行逆透视操作？

现有如下DAX定义的数据表，需要完成逆透视操作，将月份列（Apr FY23、May FY24等）转换为MonthFY日期列，并保留Group A、Variable和对应数值：

EVALUATE
Var mydf =
DATATABLE (
    "Group A", STRING, "Variable", STRING,
    "Apr FY23", CURRENCY, "May FY24", CURRENCY,
    "Jun FY24", CURRENCY,"Jul FY24", CURRENCY,

    {
    
        { "Gorup A", "Additions_at_Cost", 10000, 20000 ,50000,60000},
        { "Group A", "Accum_Dep", 5000, 6000, 75000,8500},
        { "Group A", "Accum_Dep Dispositions Impairments",10 ,10,1000, 1000},
        { "Group A", "Dispositions", 999,999, 5000,5000},
        
        { "Gorup B", "Additions_at_Cost", 10000, 20000 ,50000,60000},
        { "Group B", "Accum_Dep", 5000, 6000, 75000,8500},
        { "Group B", "Accum_Dep Dispositions Impairments", 10,10,1000, 1000},
        { "Group B", "Dispositions", 999,999, 5000,5000}
    }
)
Return mydf

你之前的代码存在以下问题：

• SELECTCOLUMNS中直接用数组{"Group A", "Group B"}赋值错误，应引用原表的[Group A]列
• 原表列名为Jul FY24，代码中误写为July FY24，列名不匹配
• 未将MonthFY转换为日期类型，仅以文本存储

正确的逆透视代码 #

EVALUATE
VAR mydf =
    DATATABLE (
        "Group A", STRING, "Variable", STRING,
        "Apr FY23", CURRENCY, "May FY24", CURRENCY,
        "Jun FY24", CURRENCY,"Jul FY24", CURRENCY,
        {
            { "Group A", "Additions_at_Cost", 10000, 20000 ,50000,60000},
            { "Group A", "Accum_Dep", 5000, 6000, 75000,8500},
            { "Group A", "Accum_Dep Dispositions Impairments",10 ,10,1000, 1000},
            { "Group A", "Dispositions", 999,999, 5000,5000},
            { "Group B", "Additions_at_Cost", 10000, 20000 ,50000,60000},
            { "Group B", "Accum_Dep", 5000, 6000, 75000,8500},
            { "Group B", "Accum_Dep Dispositions Impairments", 10,10,1000, 1000},
            { "Group B", "Dispositions", 999,999, 5000,5000}
        }
    )
VAR PivotApr = 
    SELECTCOLUMNS (
        mydf,
        "Group A", [Group A],
        "Variable", [Variable],
        "MonthFY", DATEVALUE("01 " & "Apr FY23"),
        "Value", [Apr FY23]
    )
VAR PivotMay = 
    SELECTCOLUMNS (
        mydf,
        "Group A", [Group A],
        "Variable", [Variable],
        "MonthFY", DATEVALUE("01 " & "May FY24"),
        "Value", [May FY24]
    )
VAR PivotJun = 
    SELECTCOLUMNS (
        mydf,
        "Group A", [Group A],
        "Variable", [Variable],
        "MonthFY", DATEVALUE("01 " & "Jun FY24"),
        "Value", [Jun FY24]
    )
VAR PivotJul = 
    SELECTCOLUMNS (
        mydf,
        "Group A", [Group A],
        "Variable", [Variable],
        "MonthFY", DATEVALUE("01 " & "Jul FY24"),
        "Value", [Jul FY24]
    )
RETURN
    UNION (PivotApr, PivotMay, PivotJun, PivotJul)

代码说明 #

• 修正了原表中Gorup A的拼写错误为Group A，确保数据一致性
• 通过DATEVALUE("01 " & 月份文本)将月份文本转换为当月第一天的日期值，满足MonthFY为日期类型的要求
• SELECTCOLUMNS中正确引用原表的[Group A]和[Variable]列，保证每行数据对应正确的分组和变量
• 使用UNION合并所有月份的子表，完成逆透视操作

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