了解 ORDERBY、PARTITIONBY 和 MATCHBY 函数

DAX 中的ORDERBY、PARTITIONBY和MATCHBY函数是只能与 DAX 窗口函数一起使用的特殊函数：INDEX、OFFSET、WINDOW、RANK、ROWNUMBER。

了解 ORDERBY、PARTITIONBY 和 MATCHBY 对于成功使用 Window 函数至关重要。 此处提供的示例使用 OFFSET，但同样适用于其他 Window 函数。

方案

让我们从根本不使用 Window 函数的示例开始。 下面显示的表返回了每个日历年每种颜色的总销售额。 有多种方法可以定义此表，但由于我们有兴趣了解 DAX 中发生的情况，因此我们将使用计算表。 下面是表表达式：

BasicTable = 
    SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
    )

你将看到，此计算表表达式使用SUMMARIZECOLUMNS根据 DimProduct 表中的 Color 列和 DimDate 表中的 CalendarYear 列计算 FactInternetSales 表中 SalesAmount 列的 SUM。 下面是结果：

Color	CalendarYear	CurrentYearSales
“黑色”	2017	393885
“黑色”	2018	1818835
“黑色”	2019	3981638
“黑色”	2020	2644054
“Blue”	2019	994448
“Blue”	2020	1284648
“彩色”	2019	48622
“彩色”	2020	57849
"NA"	2019	207822
"NA"	2020	227295
“红色”	2017	2961198
“红色”	2018	3686935
“红色”	2019	900175
“红色”	2020	176022
“银色”	2017	326399
“银色”	2018	750026
“银色”	2019	2165176
“银色”	2020	1871788
“白色”	2019	2517
“白色”	2020	2589
“黄色”	2018	163071
“黄色”	2019	2072083
“黄色”	2020	2621602

现在，让我们想象一下，我们正试图解决一个业务问题，即计算每种颜色的同比销售额差异。 实际上，我们需要一种方法来查找上一年度相同颜色的销售额，并在上下文中从当年的销售额中减去该销售额。 例如，对于组合 [Red, 2019] ，我们要查找 [Red, 2018] 的销售额。 找到后，就可以从当前销售额中减去该值，并返回所需的值。

使用 OFFSET

OFFSET 非常适合与回答上述业务问题所需的先前计算进行典型比较，因为它允许我们执行相对移动。 我们的第一次尝试可能是：

1stAttempt = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation
            ),
            [CurrentYearSales]
        )
    )

此表达式正在发生很多事情。 我们使用ADDCOLUMNS将表从之前的情况扩展为具有名为 PreviousColorSales 的列。 对于使用 OFFSET 检索到的上一个 Color，该列的内容设置为 CurrentYearSales，即 SUM (FactInternetSales[SalesAmount])。

结果为：

Color	CalendarYear	CurrentYearSales	PreviousColorSales
“黑色”	2017	393885
“黑色”	2018	1818835	393885
“黑色”	2019	3981638	1818835
“黑色”	2020	2644054	3981638
“Blue”	2019	994448	2644054
“Blue”	2020	1284648	994448
“彩色”	2019	48622	1284648
“彩色”	2020	57849	48622
"NA"	2019	207822	57849
"NA"	2020	227295	207822
“红色”	2017	2961198	227295
“红色”	2018	3686935	2961198
“红色”	2019	900175	3686935
“红色”	2020	176022	900175
“银色”	2017	326399	176022
“银色”	2018	750026	326399
“银色”	2019	2165176	750026
“银色”	2020	1871788	2165176
“白色”	2019	2517	1871788
“白色”	2020	2589	2517
“黄色”	2018	163071	2589
“黄色”	2019	2072083	163071
“黄色”	2020	2621602	2072083

这离我们的目标又近了一步，但如果我们仔细观察，它并不完全匹配我们想要的结果。 例如，对于 [Silver, 2017] ，PreviousColorSales 设置为 [Red, 2020]。

添加 ORDERBY

该定义等效于：

1stAttemptWithORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([Color], ASC, [CalendarYear], ASC, [CurrentYearSales], ASC)      
            ),
            [CurrentYearSales]
        )
    )

在本例中，对 OFFSET 的调用使用 ORDERBY 按 Color 和 CalendarYear 升序对表进行排序，从而确定被视为返回的上一行。

这两个结果等效的原因是 ORDERBY 自动包含关系中不在 PARTITIONBY 中的所有列。 由于未指定 PARTITIONBY，因此 ORDERBY 设置为 Color、CalendarYear 和 CurrentYearSales。 但是，由于关系中的 Color 和 CalendarYear 对是唯一的，因此添加 CurrentYearSales 不会更改结果。 事实上，即使我们只在 ORDERBY 中指定 Color，结果也是相同的，因为 CalendarYear 会自动添加。 这是因为函数将根据需要向 ORDERBY 添加任意数量的列，从而确保 ORDERBY 和 PARTITIONBY 列可以唯一识别每一行：

1stAttemptWithORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS(
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

添加 PARTITIONBY

现在，为了几乎获得我们想要的结果，可以使用 PARTITIONBY，如以下计算表表达式所示：

UsingPARTITIONBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]), 
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

请注意，在此处指定 ORDERBY 是可选项，因为 ORDERBY 会自动包含 PARTITIONBY 中未指定的关系中的所有列。 因此，以下表达式会返回相同的结果，因为 ORDERBY 自动设置为 CalendarYear 和 CurrentYearSales：

UsingPARTITIONBYWithoutORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

注意

虽然 ORDERBY 自动设置为 CalendarYear 和 CurrentYearSales，但无法保证其添加顺序。 如果在 CalendarYear 之前添加 CurrentYearSales，生成的顺序与预期顺序会不一致。 指定 ORDERBY 和 PARTITIONBY 时要显式，从而避免混淆和意外结果。

这两个表达式都会返回我们想要的结果：

Color	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
“黑色”	2017	393885
“黑色”	2018	1818835	393885
“黑色”	2019	3981638	1818835
“黑色”	2020	2644054	3981638
“Blue”	2019	994448
“Blue”	2020	1284648	994448
“彩色”	2019	48622
“彩色”	2020	57849	48622
"NA"	2019	207822
"NA"	2020	227295	207822
“红色”	2017	2961198
“红色”	2018	3686935	2961198
“红色”	2019	900175	3686935
“红色”	2020	176022	900175
“银色”	2017	326399
“银色”	2018	750026	326399
“银色”	2019	2165176	750026
“银色”	2020	1871788	2165176
“白色”	2019	2517
“白色”	2020	2589	2517
“黄色”	2018	163071
“黄色”	2019	2072083	163071
“黄色”	2020	2621602	2072083

如此表所示，PreviousYearSalesForSameColor 列显示上一年相同颜色的销售额。 对于 [Red, 2020]，它会返回 [Red, 2019] 的销售额，以此类推。 如果没有上一年（例如，在 [Red, 2017] 的情况下），则不返回任何值。

可以将 PARTITIONBY 视为一种将表划分为多个部分以执行 OFFSET 计算的方法。 在上面的示例中，该表划分为多个部分，存在多种颜色，每种颜色各一个。 然后，在每个部分内计算 OFFSET，按 CalendarYear 排序。

从视觉上看，发生的情况是：

首先，调用 PARTITIONBY 会导致表划分为多个部分，每种颜色各一个。 这由表图像中的浅蓝色框表示。 接下来，ORDERBY 确保按 CalendarYear（由橙色箭头表示）对每个部分进行排序。 最后，在每个排序的部分中，对于每一行，OFFSET 会查找其上方的行，并在 PreviousYearSalesForSameColor 列中返回该值。 由于对于每个部分的第一行，同一部分中没有上一行，因此 PreviousYearSalesForSameColor 列的该行的结果为空。

要获得最终结果，只需从调用 OFFSET 返回的相同颜色的上一年销售额中减去 CurrentYearSales。 由于我们没兴趣显示相同颜色的上一年销售额，因此只显示当年销售额和同比差异。 下面是最终的计算表表达式：

FinalResult = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

下面是该表达式的结果：

Color	CalendarYear	CurrentYearSales	YoYSalesForSameColor
“黑色”	2017	393885	393885
“黑色”	2018	1818835	1424950
“黑色”	2019	3981638	2162803
“黑色”	2020	2644054	-1337584
“Blue”	2019	994448	994448
“Blue”	2020	1284648	290200
“彩色”	2019	48622	48622
“彩色”	2020	57849	9227
"NA"	2019	207822	207822
"NA"	2020	227295	19473
“红色”	2017	2961198	2961198
“红色”	2018	3686935	725737
“红色”	2019	900175	-2786760
“红色”	2020	176022	-724153
“银色”	2017	326399	326399
“银色”	2018	750026	423627
“银色”	2019	2165176	1415150
“银色”	2020	1871788	-293388
“白色”	2019	2517	2517
“白色”	2020	2589	72
“黄色”	2018	163071	163071
“黄色”	2019	2072083	1909012
“黄色”	2020	2621602	549519

使用 MATCHBY

你可能已经注意到，我们根本没有指定 MATCHBY。 在这种情况下，没有必要这样做。 ORDERBY 和 PARTITIONBY 中的列（就上面示例中指定的而言）足以唯一识别每一行。 由于未指定 MATCHBY，因此 ORDERBY 和 PARTITIONBY 中指定的列用于唯一识别每一行，以便可以比较它们，使 OFFSET 能够给出有意义的结果。 如果 ORDERBY 和 PARTITIONBY 中的列无法唯一识别每一行，可以将这些额外的列添加到 ORDERBY 子句中，前提是这些列允许对每一行进行唯一识别。 如果无法实现，会返回错误。 在最后一种情况下，指定 MATCHBY 可能有助于解决错误。

如果指定了 MATCHBY，则 MATCHBY 和 PARTITIONBY 中的列用于唯一识别每一行。 如果无法实现，会返回错误。 即使不需要 MATCHBY，也可以考虑显式指定 MATCHBY 以避免任何混淆。

继续上面的示例，下面是最后一个表达式：

FinalResult = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

如果要明确应如何唯一识别行，可以指定 MATCHBY，如以下等效表达式所示：

FinalResultWithExplicitMATCHBYOnColorAndCalendarYear = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color]),
                MATCHBY ([Color], [CalendarYear])
            ),
            [CurrentYearSales]
        )
    )

由于指定了 MATCHBY，因此 MATCHBY 和 PARTITIONBY 中指定的列都用于唯一识别行。 由于 MATCHBY 和 PARTITIONBY 中都指定了 Color ，因此以下表达式等效于上一个表达式：

FinalResultWithExplicitMATCHBYOnCalendarYear = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color]),
                MATCHBY ([CalendarYear])
            ),
            [CurrentYearSales]
        )
    )

由于在到目前为止我们介绍的示例中不需要指定 MATCHBY，因此让我们看一个需要 MATCHBY、略有不同的示例。 在本例中，我们有一个订单行列表。 每一行都表示订单的订单行。 一个订单可以有多个订单行，订单行 1 显示在多个订单上。 此外，对于每个订单行，我们都有 ProductKey 和 SalesAmount。 表中相关列的示例如下所示：

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount
SO51900	1	528	4.99
SO51948	1	528	5.99
SO52043	1	528	4.99
SO52045	1	528	4.99
SO52094	1	528	4.99
SO52175	1	528	4.99
SO52190	1	528	4.99
SO52232	1	528	4.99
SO52234	1	528	4.99
SO52234	2	529	3.99

请注意，需有 SalesOrderNumber 和 SalesOrderLineNumber 才能唯一识别行。

对于每个订单，我们希望返回按 SalesAmount 降序排列的同一产品（由 ProductKey 表示）的先前销售金额。 以下表达式不起作用，因为传递到 OFFSET 时，vRelation 中可能有多个行：

ThisExpressionFailsBecauseMATCHBYIsMissing = 
    ADDCOLUMNS (
        FactInternetSales,
        "Previous Sales Amount",
            SELECTCOLUMNS (
                OFFSET (
                    -1,
                    FactInternetSales,
                    ORDERBY ( FactInternetSales[SalesAmount], DESC ),
                    PARTITIONBY ( FactInternetSales[ProductKey] )
                ),
                FactInternetSales[SalesAmount]
            )
    )

此表达式返回错误：“OFFSET 的 Relation 参数可能有重复的行，这是不允许的。”

为了使此表达式正常工作，必须指定 MATCHBY，并且必须包含唯一定义行的所有列。 此处需要 MATCHBY，因为关系 FactInternetSales 不包含任何显式键或唯一列。 但是，SalesOrderNumber 和 SalesOrderLineNumber 列共同构成组合键，其中它们的存在在关系中是唯一的，因此可以唯一地识别每一行。 仅指定 SalesOrderNumber 或 SalesOrderLineNumber 是不够的，因为两列都包含重复值。 以下表达式解决了该问题：

ThisExpressionWorksBecauseOfMATCHBY = 
    ADDCOLUMNS (
        FactInternetSales,
        "Previous Sales Amount",
            SELECTCOLUMNS (
                OFFSET (
                    -1,
                    FactInternetSales,
                    ORDERBY ( FactInternetSales[SalesAmount], DESC ),
                    PARTITIONBY ( FactInternetSales[ProductKey] ),
                    MATCHBY ( FactInternetSales[SalesOrderNumber], 
                                FactInternetSales[SalesOrderLineNumber] )
                ),
                FactInternetSales[SalesAmount]
            )
    )

此表达式确实会返回我们想要的结果：

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount	先前的销售额
SO51900	1	528	5.99
SO51948	1	528	4.99	5.99
SO52043	1	528	4.99	4.99
SO52045	1	528	4.99	4.99
SO52094	1	528	4.99	4.99
SO52175	1	528	4.99	4.99
SO52190	1	528	4.99	4.99
SO52232	1	528	4.99	4.99
SO52234	1	528	4.99	4.99
SO52234	2	529	3.99

相关内容

ORDERBY
PARTITIONBY
MATCHBY
INDEX
OFFSET
WINDOW
RANK
ROWNUMBER