瞭解 ORDERBY、PARTITIONBY和 MATCHBY 函式

DAX 中的 ORDERBY、PARTITIONBY和 MATCHBY 函式是只能與 DAX Window 函式搭配使用的特殊函式：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。 結果如下：

色彩	CalendarYear	CurrentYearSales
"Black"	2017	393885
"Black"	2018	1818835
"Black"	2019	3981638
"Black"	2020	2644054
"Blue"	2019	994448
"Blue"	2020	1284648
"Multi"	2019	48622
"Multi"	2020	57849
"NA"	2019	207822
"NA"	2020	227295
"Red"	2017	2961198
"Red"	2018	3686935
"Red"	2019	900175
"Red"	2020	176022
"Silver"	2017	326399
"Silver"	2018	750026
"Silver"	2019	2165176
"Silver"	2020	1871788
"White"	2019	2517
"White"	2020	2589
"Yellow"	2018	163071
"Yellow"	2019	2072083
"Yellow"	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 的欄。 該欄的內容會設定為 CurrentYearSales，即 SUM(FactInternetSales[SalesAmount])，是根據上一個顏色（使用 OFFSET擷取）來設定的。

結果如下：

色彩	CalendarYear	CurrentYearSales	PreviousColorSales
"Black"	2017	393885
"Black"	2018	1818835	393885
"Black"	2019	3981638	1818835
"Black"	2020	2644054	3981638
"Blue"	2019	994448	2644054
"Blue"	2020	1284648	994448
"Multi"	2019	48622	1284648
"Multi"	2020	57849	48622
"NA"	2019	207822	57849
"NA"	2020	227295	207822
"Red"	2017	2961198	227295
"Red"	2018	3686935	2961198
"Red"	2019	900175	3686935
"Red"	2020	176022	900175
"Silver"	2017	326399	176022
"Silver"	2018	750026	326399
"Silver"	2019	2165176	750026
"Silver"	2020	1871788	2165176
"White"	2019	2517	1871788
"White"	2020	2589	2517
"Yellow"	2018	163071	2589
"Yellow"	2019	2072083	163071
"Yellow"	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 時需要明確，以避免產生混淆和非預期的結果。

這兩個運算式都會傳回我們追求的結果：

色彩	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
"Black"	2017	393885
"Black"	2018	1818835	393885
"Black"	2019	3981638	1818835
"Black"	2020	2644054	3981638
"Blue"	2019	994448
"Blue"	2020	1284648	994448
"Multi"	2019	48622
"Multi"	2020	57849	48622
"NA"	2019	207822
"NA"	2020	227295	207822
"Red"	2017	2961198
"Red"	2018	3686935	2961198
"Red"	2019	900175	3686935
"Red"	2020	176022	900175
"Silver"	2017	326399
"Silver"	2018	750026	326399
"Silver"	2019	2165176	750026
"Silver"	2020	1871788	2165176
"White"	2019	2517
"White"	2020	2589	2517
"Yellow"	2018	163071
"Yellow"	2019	2072083	163071
"Yellow"	2020	2621602	2072083

如您在此表格中所見，PreviousYearSalesForSameColor 欄會顯示上一年相同色彩的銷售量。 針對 [Red， 2020]，它會傳回 [Red， 2019] 等的銷售。 如果沒有前一年，例如在 [Red， 2017] 的情況下，則不會傳回任何值。

您可以將 PARTITIONBY 視為將資料表分割成執行 OFFSET 計算的元件。 在上述範例中，資料表會分割成有任意色彩的多個部分，每種色彩各有一個。 然後，在每個部分中計算 OFFSET，並按 CalendarYear 進行排序。

視覺上發生的情況如下：

首先，調用 PARTITIONBY 會導致數據表被分成多個部分，每個部分對應一種顏色。 在資料表影像中會以淺藍色方塊表示。 接下來，ORDERBY 確定每個部分都依 CalendarYear 排序（以橙色箭號表示）。 最後，在每個已排序的部分中，對於每一行，OFFSET 會找到其上方的行，並在 PreviousYearSalesForSameColor 欄中返回該值。 由於對於各部分中的每一個第一行而言，在相同的部分中沒有上一行，所以 PreviousYearSalesForSameColor 列的結果是空的。

為了達到最終結果，我們只需要從前一年的銷售額中減去呼叫 OFFSET所指定色彩的當年度銷售額。 因為我們不想要顯示相同色彩的去年銷售額，而是只專注於顯示當年的銷售額和每年差異。 以下是最終導出資料表運算式：

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]
        )
    )

以下是該表達式的結果：

色彩	CalendarYear	CurrentYearSales	YoYSalesForSameColor
"Black"	2017	393885	393885
"Black"	2018	1818835	1424950
"Black"	2019	3981638	2162803
"Black"	2020	2644054	-1337584
"Blue"	2019	994448	994448
"Blue"	2020	1284648	290200
"Multi"	2019	48622	48622
"Multi"	2020	57849	9227
"NA"	2019	207822	207822
"NA"	2020	227295	19473
"Red"	2017	2961198	2961198
"Red"	2018	3686935	725737
"Red"	2019	900175	-2786760
"Red"	2020	176022	-724153
"Silver"	2017	326399	326399
"Silver"	2018	750026	423627
"Silver"	2019	2165176	1415150
"Silver"	2020	1871788	-293388
"White"	2019	2517	2517
"White"	2020	2589	72
"Yellow"	2018	163071	163071
"Yellow"	2019	2072083	1909012
"Yellow"	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 中指定的數據行都會用來唯一識別數據列。 由於 Color 同時在 MATCHBY 和 PARTITIONBY中指定，因此下列運算式相當於上一個運算式：

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 表示）。 下列表達式無法運作，因為 vRelation 中可能會有多個資料列，因為它會傳遞至 OFFSET：

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