了解 ORDERBY、PARTITIONBY 和 MATCHBY 函式

DAX 中的 ORDERBY、PARTITIONBY 和 MATCHBY 函式是特殊函式，只能與 DAX 視窗函式搭配使用：INDEX、OFFSET、WINDOW、RANK、ROWNUMBER。

了解 ORDERBY、PARTITIONBY 和 MATCHBY 對於成功使用視窗函式非常重要。 此處提供的範例使用 OFFSET，但同樣適用於其他視窗函式。

案例

讓我們從完全不使用視窗函式的範例開始。 如下所示的資料表會傳回銷售總量，依色彩和行事曆年度區分。 有多種方式可以定義此資料表，但是由於我們有興趣了解 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])，用於先前 Color (使用 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 的呼叫會導致資料表分割成許多部分，每個 Color 各有一個。 在資料表影像中會以淺藍色方塊表示。 接下來，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]
        )
    )

以下是該運算式的結果：

色彩	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