瞭解 ORDERBY、PARTITIONBY、and與MATCHBY 函式

DAX 中的 ORDERBY、PARTITIONBY、andMATCHBY 函式是只能與 DAXWindow 函式搭配使用的特殊函式：INDEX、OFFSET、WINDOW、RANK、ROWNUMBER。

瞭解 ORDERBY、PARTITIONBY、andMATCHBY 對於成功使用 Window 函式至關重要。 此處提供的範例使用 OFFSET，但同樣適用於其他 Window 函式。

案例

讓我們從在 all不使用 Window 函式的範例開始。 下面的表格顯示每 calendaryear每一色彩的總銷售額。 有多種方式可以define此表格，但由於我們有興趣瞭解DAX中發生的情況，因此我們將使用計算的表格。 以下是資料表運算式：

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

您會看到這個導出數據表表達式使用 SUMMARIZECOLUMNS，calculate FactInternetSales 數據表中 SalesAmount 數據行的 SUM，方法是 DimProduct 數據表中的 Color 數據行，and DimDate 數據表中的 CalendarYear 數據行。 結果如下：

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

Now，假設我們正在嘗試解決有關計算銷售差異的商業問題，year-over-year 每個顏色。 實際上，我們需要一個方法，在 previousyearand 中 find 相同色彩的銷售量減去目前 year中的銷售額。 例如，針對 [Red, 2019] 的組合，我們要尋找 [Red, 2018] 的銷售。 一旦有了，就可以從目前的銷售減去它，and 傳回所需的 value。

使用 OFFSET

OFFSET 非常適合執行典型的 類型計算，相較於 previous 類型的計算，此類計算需要解答上述商務問題，因為它允許我們進行相對移動。 我們的 first 嘗試可能是：

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

使用這個運算式發生很多情況。 我們使用 ADDCOLUMNS 到 expand 的範圍，更新之前的表格，並新增一個名為 PreviousColorSales 的欄位。 該欄的內容會設定為 CurrentYearSales（SUM，FactInternetSales[SalesAmount]），這是使用 OFFSET擷取的 previous 色彩。

結果如下：

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

這是我們進球的一步，但 if 我們仔細觀察，這與我們之後的完全不符。 例如，針對 [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 and CalendarYear 以遞增順序排序數據表，這會決定所傳回的 previous 數據列。

這兩個結果相等的原因是，ORDERBY 會自動從不在 PARTITIONBY中的關係資料中選擇 containsall 的數據行。 由於未指定 PARTITIONBY，ORDERBY 會設定為 Color、CalendarYear、and CurrentYearSales。 不過，由於關聯中 Color and CalendarYear 配對是唯一的，因此新增 CurrentYearSales 不會變更結果。 在 fact中，evenif 我們只指定 ORDERBY中的 Color，結果會相同，因為會自動新增 CalendarYear。 這是因為函式會根據需要新增多個數據列到 ORDERBY，以確保每個數據列可以由 ORDERBYandPARTITIONBY 列唯一識別：

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

Now，若要 幾乎 取得我們在使用 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 會自動 containsall 在 PARTITIONBY中未指定的關係欄位。 因此，下列表達式會傳回相同的結果，因為 ORDERBY 會自動設定為 CalendarYear and 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 並且 and 為 CurrentYearSales，但無法保證它們會以何種順序新增。 If 在 CalendarYear 之前加入 CurrentYearSales，結果的順序不符合預期。 指定 ORDERBYandPARTITIONBY 時明確，以避免 and 非預期的結果混淆。

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

色彩	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 資料行會顯示相同色彩 previousyear 的銷售量。 針對 [Red， 2020]，它會傳回 [Red， 2019] 的銷售，and 等等。 If 沒有 previousyear，例如在 [Red， 2017] 的情況下，不會傳回任何 value。

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

視覺上發生的情況如下：

顯示根據 CalendarYear 的 數據表

First，呼叫 PARTITIONBY 會導致數據表分成各部分，每個 Color 各一個。 在資料表影像中會以淺藍色方塊表示。 Next，ORDERBY 確保每個部分都依 CalendarYear 排序（以橙色箭號表示）。 最後，在每個已排序的部分中，對於每一行，OFFSET 尋找其上方的行，and 會傳回 PreviousYearSalesForSameColor 欄中的 value。 由於在每個元件中的每個 first 數據列對應的相同元件中沒有 previous 列，因此 PreviousYearSalesForSameColor 列中的結果是空的。

為了達成最終結果，我們只需從呼叫 OFFSET所傳回的相同色彩的 previousyear 銷售中減去 CurrentYearSales。 由於我們 not 有興趣顯示相同色彩的銷售額 previousyear，但只有在目前的銷售 year 中顯示 andyear 的差異 year。 以下是最終導出資料表運算式：

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

And 以下是該運算式的結果：

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

您可能注意到我們並未在 all指定 MATCHBY。 在此案例中，這並非必要。 ORDERBY and PARTITIONBY 中的數據列（就上述範例中指定的數據列而言），足以唯一識別每個數據行。 因為我們未指定 MATCHBY，所以 ORDERBYandPARTITIONBY 中指定的數據行可用來唯一識別每個數據列，以便比較它們，讓 OFFSET 提供有意義的結果。 If ORDERBY and PARTITIONBY 中的數據行無法唯一識別每個數據列，可以將其他數據行新增至 ORDERBY 子句，if 這些額外數據行允許唯一識別每個數據列。 If 如果有可能 not，就會傳回 error。 在此 last 案例中，指定 MATCHBY 可能有助於解決 error。

指定 IfMATCHBY 時，MATCHBYandPARTITIONBY 中的數據行會用來唯一識別每個數據列。 If 如果 not 可能，就會傳回 error。 不需要 EvenifMATCHBY，請考慮明確指定 MATCHBY 以避免任何混淆。

延續上述範例，接下來是 last 表達式：

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

If 我們想要明確說明應該如何唯一識別數據列，我們可以指定 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 同時在 MATCHBYandPARTITIONBY中指定，因此下列表達式相當於 previous 表示式：

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的稍微不同的範例。 在此案例中，我們有訂單明細行清單。 每個資料列都代表訂單的訂單明細行。 訂單可以有多個訂單行，and 訂單行 1 出現在許多訂單上。 此外，針對每個訂單行，我們有一個 ProductKey and 以及一個 SalesAmount。 數據表上相關列的 sample 如下所示：

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 and 和 SalesOrderLineNumber 都是唯一識別行的必要條件。

針對每個訂單，我們想要以遞減順序傳回 SalesAmount 所訂購之相同 product（以 ProductKey 表示）的 previous 銷售金額。 下列表達式無法運作，因為 vRelation 中可能會有多個資料列，因為它會傳遞至 OFFSET：

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

此表達式會傳回 error：「OFFSET的 Relation 參數可能有重複的數據列，不允許。

若要讓此表達式正常運作，MATCHBY 必須指定 and 必須包含唯一 define 數據列 all 數據行。 這裡需要 MATCHBY，因為關聯 FactInternetSales 不包含任何明確的索引鍵 or 唯一數據行。 不過，SalesOrderNumber and 和 SalesOrderLineNumber 數據行會形成一個 複合鍵，它們的組合在關聯 and 中是唯一的，因此能夠唯一識別每行資料。 僅僅指定 SalesOrderNumber or SalesOrderLineNumber 是不夠的，因為這兩個欄位都包含重複的 values。 下列運算式可解決問題：

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

And 此表示式確實會傳回我們想要的結果：

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount	Previous 銷售金額
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

Related 內容

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER