DAX Studio中OrderBy列无法解析：如何正确引用新增列？

尝试在DAX Studio中为表格计算排名，并基于该排名计算累计值，使用Contoso Light模型测试时，启用累计值的EVALUATE语句后报错：OrderBy column 'QtyRank' could not be resolved.

测试代码如下：

DEFINE
    VAR _SalesSummary =
        FILTER (
            SUMMARIZECOLUMNS (
                'Date'[Year],
                'Product'[Category],
                'Product'[Product Name],
                "Sales Qty", [Total Quantity]
            ),
            'Date'[Year] = 2020
                && 'Product'[Category] = "Audio"
        )
    VAR _SalesSummaryWithRank =
        ADDCOLUMNS (
            _SalesSummary,
            "QtyRank", RANKX ( _SalesSummary, [Sales Qty],, DESC, DENSE )
        )
    VAR _SalesSummaryWithRollingTotal =
        ADDCOLUMNS (
            _SalesSummaryWithRank,
            "Sales Qty RT", SUMX ( WINDOW ( 1, ABS, 0, REL, ORDERBY ( [QtyRank], ASC ) ), [Sales Qty] )
        )

EVALUATE
_SalesSummaryWithRank
ORDER BY [Sales Qty] DESC

/*EVALUATE
_SalesSummaryWithRollingTotal*/

疑问：在后续查询中引用新增列的正确方式是什么？

问题出在WINDOW函数无法直接识别ADDCOLUMNS新增的QtyRank列——WINDOW需要当前上下文的表结构中明确存在该列，而通过ADDCOLUMNS生成的计算列在嵌套使用时无法被直接引用。

可以通过两种方式解决：

方式一：明确表的列结构 #

先通过SELECTCOLUMNS将_SalesSummaryWithRank的列明确化，让QtyRank成为表的固有列，再嵌套计算累计值：

VAR _SalesSummaryWithRollingTotal =
    ADDCOLUMNS (
        -- 明确指定所有列，让QtyRank成为可被WINDOW识别的列
        SELECTCOLUMNS(
            _SalesSummaryWithRank,
            "Year", 'Date'[Year],
            "Category", 'Product'[Category],
            "Product Name", 'Product'[Product Name],
            "Sales Qty", [Sales Qty],
            "QtyRank", [QtyRank]
        ),
        "Sales Qty RT", SUMX ( WINDOW ( 1, ABS, 0, REL, ORDERBY ( [QtyRank], ASC ) ), [Sales Qty] )
    )

方式二：直接使用排名依据替代新增列 #

不直接引用QtyRank，而是在WINDOW的ORDERBY中使用排名的原始依据（即[Sales Qty]），因为按销量降序排序的效果和按QtyRank升序的逻辑一致：

VAR _SalesSummaryWithRollingTotal =
    ADDCOLUMNS (
        _SalesSummaryWithRank,
        "Sales Qty RT", SUMX ( 
            WINDOW ( 1, ABS, 0, REL, ORDERBY ( [Sales Qty], DESC ) ), 
            [Sales Qty] 
        )
    )

完整可运行代码 #

DEFINE
    VAR _SalesSummary =
        FILTER (
            SUMMARIZECOLUMNS (
                'Date'[Year],
                'Product'[Category],
                'Product'[Product Name],
                "Sales Qty", [Total Quantity]
            ),
            'Date'[Year] = 2020
                && 'Product'[Category] = "Audio"
        )
    VAR _SalesSummaryWithRank =
        ADDCOLUMNS (
            _SalesSummary,
            "QtyRank", RANKX ( _SalesSummary, [Sales Qty],, DESC, DENSE )
        )
    VAR _SalesSummaryWithRollingTotal =
        ADDCOLUMNS (
            SELECTCOLUMNS(
                _SalesSummaryWithRank,
                "Year", 'Date'[Year],
                "Category", 'Product'[Category],
                "Product Name", 'Product'[Product Name],
                "Sales Qty", [Sales Qty],
                "QtyRank", [QtyRank]
            ),
            "Sales Qty RT", SUMX ( WINDOW ( 1, ABS, 0, REL, ORDERBY ( [QtyRank], ASC ) ), [Sales Qty] )
        )

EVALUATE
_SalesSummaryWithRollingTotal
ORDER BY [QtyRank] ASC

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