Memahami fungsi ORDERBY, PARTITIONBY, dan MATCHBY

Fungsi ORDERBY, PARTITIONBY, dan MATCHBY di DAX adalah fungsi khusus yang hanya dapat digunakan bersama dengan fungsi Jendela DAX: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Memahami ORDERBY, PARTITIONBY, dan MATCHBY sangat penting untuk berhasil menggunakan fungsi Window. Contoh yang disediakan di sini menggunakan OFFSET, tetapi juga berlaku untuk fungsi Window lainnya.

Skenario

Mari kita mulai dengan contoh yang tidak menggunakan fungsi Window. Ditunjukkan di bawah ini adalah tabel yang mengembalikan total penjualan, per warna, per tahun kalender. Ada beberapa cara untuk menentukan tabel ini, tetapi karena kami tertarik untuk memahami apa yang terjadi di DAX, kita akan menggunakan tabel terhitung. Berikut adalah ekspresi tabel:

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

Anda akan melihat ekspresi tabel terhitung ini menggunakan SUMMARIZECOLUMNS untuk menghitung JUMLAH kolom SalesAmount di tabel FactInternetSales, dengan kolom Warna dari tabel DimProduct, dan kolom CalendarYear dari tabel DimDate. Berikut Hasilnya:

Warna	CalendarYear	CurrentYearSales
"Hitam"	2017	393885
"Hitam"	2018	1818835
"Hitam"	2019	3981638
"Hitam"	2020	2644054
"Biru"	2019	994448
"Biru"	2020	1284648
"Multi"	2019	48622
"Multi"	2020	57849
"NA"	2019	207822
"NA"	2020	227295
"Merah"	2017	2961198
"Merah"	2018	3686935
"Merah"	2019	900175
"Merah"	2020	176022
"Perak"	2017	326399
"Perak"	2018	750026
"Perak"	2019	2165176
"Perak"	2020	1871788
"Putih"	2019	2517
"Putih"	2020	2589
"Kuning"	2018	163071
"Kuning"	2019	2072083
"Kuning"	2020	2621602

Sekarang, mari kita bayangkan kita mencoba memecahkan pertanyaan bisnis menghitung perbedaan penjualan, tahun ke tahun untuk setiap warna. Secara efektif, kita membutuhkan cara untuk menemukan penjualan untuk warna yang sama di tahun sebelumnya dan menguranginya dari penjualan di tahun ini, dalam konteks. Misalnya, untuk kombinasi [Merah, 2019] kami mencari penjualan untuk [Merah, 2018]. Setelah memilikinya, kita kemudian dapat menguranginya dari penjualan saat ini dan mengembalikan nilai yang diperlukan.

Menggunakan OFFSET

OFFSET sangat cocok untuk perbandingan umum dengan jenis perhitungan sebelumnya yang diperlukan untuk menjawab pertanyaan bisnis yang dijelaskan di atas, karena memungkinkan kita untuk melakukan gerakan relatif. Upaya pertama kami mungkin:

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

Banyak yang terjadi dengan ekspresi ini. Kami menggunakan ADDCOLUMNS untuk memperluas tabel dari sebelumnya dengan kolom bernama PreviousColorSales. Konten kolom tersebut diatur ke CurrentYearSales, yaitu SUM(FactInternetSales[SalesAmount]), untuk Warna sebelumnya (diambil menggunakan OFFSET).

Hasilnya adalah:

Warna	CalendarYear	CurrentYearSales	PreviousColorSales
"Hitam"	2017	393885
"Hitam"	2018	1818835	393885
"Hitam"	2019	3981638	1818835
"Hitam"	2020	2644054	3981638
"Biru"	2019	994448	2644054
"Biru"	2020	1284648	994448
"Multi"	2019	48622	1284648
"Multi"	2020	57849	48622
"NA"	2019	207822	57849
"NA"	2020	227295	207822
"Merah"	2017	2961198	227295
"Merah"	2018	3686935	2961198
"Merah"	2019	900175	3686935
"Merah"	2020	176022	900175
"Perak"	2017	326399	176022
"Perak"	2018	750026	326399
"Perak"	2019	2165176	750026
"Perak"	2020	1871788	2165176
"Putih"	2019	2517	1871788
"Putih"	2020	2589	2517
"Kuning"	2018	163071	2589
"Kuning"	2019	2072083	163071
"Kuning"	2020	2621602	2072083

Ini adalah satu langkah lebih dekat dengan tujuan kami, tetapi jika kita melihat lebih dekat itu tidak cocok dengan apa yang kita cari. Misalnya, untuk [Silver, 2017] PreviousColorSales diatur ke [Red, 2020].

Menambahkan ORDERBY

Definisi di atas setara dengan:

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

Dalam hal ini, panggilan ke OFFSET menggunakan ORDERBY untuk mengurutkan tabel menurut Warna dan CalendarYear dalam urutan naik, yang menentukan apa yang dianggap sebagai baris sebelumnya yang dikembalikan.

Alasan kedua hasil ini setara adalah karena ORDERBY secara otomatis berisi semua kolom dari relasi yang tidak ada di PARTITIONBY. Karena PARTITIONBY tidak ditentukan, ORDERBY diatur ke Color, CalendarYear, dan CurrentYearSales. Namun, karena pasangan Color dan CalendarYear dalam relasi bersifat unik, menambahkan CurrentYearSales tidak mengubah hasilnya. Bahkan, bahkan jika kita hanya menentukan Warna dalam ORDERBY, hasilnya sama karena CalendarYear akan ditambahkan secara otomatis. Ini karena fungsi akan menambahkan kolom sebanyak yang diperlukan ke ORDERBY untuk memastikan setiap baris dapat diidentifikasi secara unik oleh kolom ORDERBY dan 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]
        )
    )

Menambahkan PARTITIONBY

Sekarang, untuk hampir mendapatkan hasilnya, kita setelah kita dapat menggunakan PARTITIONBY, seperti yang ditunjukkan dalam ekspresi tabel terhitung berikut:

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

Perhatikan bahwa menentukan ORDERBY bersifat opsional di sini karena ORDERBY secara otomatis berisi semua kolom dari relasi yang tidak ditentukan dalam PARTITIONBY. Jadi, ekspresi berikut mengembalikan hasil yang sama karena ORDERBY diatur ke CalendarYear dan CurrentYearSales secara otomatis:

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

Catatan

Meskipun ORDERBY diatur ke CalendarYear dan CurrentYearSales secara otomatis, tidak ada jaminan yang diberikan untuk urutan apa mereka akan ditambahkan. Jika CurrentYearSales ditambahkan sebelum CalendarYear, urutan yang dihasilkan tidak sejajar dengan apa yang diharapkan. Jadilah eksplisit saat menentukan ORDERBY dan PARTITIONBY untuk menghindari kebingungan dan hasil yang tidak terduga.

Kedua ekspresi mengembalikan hasil yang kita cari:

Warna	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
"Hitam"	2017	393885
"Hitam"	2018	1818835	393885
"Hitam"	2019	3981638	1818835
"Hitam"	2020	2644054	3981638
"Biru"	2019	994448
"Biru"	2020	1284648	994448
"Multi"	2019	48622
"Multi"	2020	57849	48622
"NA"	2019	207822
"NA"	2020	227295	207822
"Merah"	2017	2961198
"Merah"	2018	3686935	2961198
"Merah"	2019	900175	3686935
"Merah"	2020	176022	900175
"Perak"	2017	326399
"Perak"	2018	750026	326399
"Perak"	2019	2165176	750026
"Perak"	2020	1871788	2165176
"Putih"	2019	2517
"Putih"	2020	2589	2517
"Kuning"	2018	163071
"Kuning"	2019	2072083	163071
"Kuning"	2020	2621602	2072083

Seperti yang Anda lihat dalam tabel ini, kolom PreviousYearSalesForSameColor memperlihatkan penjualan untuk tahun sebelumnya untuk warna yang sama. Untuk [Merah, 2020], ia mengembalikan penjualan untuk [Merah, 2019], dan sebagainya. Jika tidak ada tahun sebelumnya, misalnya dalam kasus [Merah, 2017], tidak ada nilai yang dikembalikan.

Anda dapat menganggap PARTITIONBY sebagai cara untuk membagi tabel menjadi beberapa bagian untuk menjalankan perhitungan OFFSET. Dalam contoh di atas, tabel dibagi menjadi bagian sebanyak ada warna, satu untuk setiap warna. Kemudian, dalam setiap bagian, OFFSET dihitung, diurutkan menurut CalendarYear.

Secara visual, apa yang terjadi adalah ini:

Pertama, panggilan ke PARTITIONBY menghasilkan tabel dibagi menjadi beberapa bagian, satu untuk setiap Warna. Ini diwakili oleh kotak biru muda dalam gambar tabel. Selanjutnya, ORDERBY memastikan bahwa setiap bagian diurutkan menurut CalendarYear (diwakili oleh panah oranye). Terakhir, dalam setiap bagian yang diurutkan, untuk setiap baris, OFFSET menemukan baris di atasnya dan mengembalikan nilai tersebut di kolom PreviousYearSalesForSameColor. Karena untuk setiap baris pertama di setiap bagian tidak ada baris sebelumnya di bagian yang sama, hasilnya di baris tersebut untuk kolom PreviousYearSalesForSameColor kosong.

Untuk mencapai hasil akhir, kita hanya perlu mengurangi CurrentYearSales dari penjualan tahun sebelumnya untuk warna yang sama yang dikembalikan oleh panggilan ke OFFSET. Karena kami tidak tertarik untuk menunjukkan penjualan tahun sebelumnya untuk warna yang sama, tetapi hanya dalam penjualan tahun ini dan perbedaan tahun ke tahun. Berikut adalah ekspresi tabel terhitung akhir:

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

Dan berikut adalah hasil dari ekspresi tersebut:

Warna	CalendarYear	CurrentYearSales	YoYSalesForSameColor
"Hitam"	2017	393885	393885
"Hitam"	2018	1818835	1424950
"Hitam"	2019	3981638	2162803
"Hitam"	2020	2644054	-1337584
"Biru"	2019	994448	994448
"Biru"	2020	1284648	290200
"Multi"	2019	48622	48622
"Multi"	2020	57849	9227
"NA"	2019	207822	207822
"NA"	2020	227295	19473
"Merah"	2017	2961198	2961198
"Merah"	2018	3686935	725737
"Merah"	2019	900175	-2786760
"Merah"	2020	176022	-724153
"Perak"	2017	326399	326399
"Perak"	2018	750026	423627
"Perak"	2019	2165176	1415150
"Perak"	2020	1871788	-293388
"Putih"	2019	2517	2517
"Putih"	2020	2589	72
"Kuning"	2018	163071	163071
"Kuning"	2019	2072083	1909012
"Kuning"	2020	2621602	549519

Menggunakan MATCHBY

Anda mungkin telah memperhatikan bahwa kami tidak menentukan MATCHBY sama sekali. Dalam hal ini, tidak perlu. Kolom di ORDERBY dan PARTITIONBY (sejauh yang ditentukan dalam contoh di atas) cukup untuk mengidentifikasi setiap baris secara unik. Karena kami tidak menentukan MATCHBY, kolom yang ditentukan dalam ORDERBY dan PARTITIONBY digunakan untuk mengidentifikasi setiap baris secara unik sehingga dapat dibandingkan dengan mengaktifkan OFFSET untuk memberikan hasil yang bermakna. Jika kolom di ORDERBY dan PARTITIONBY tidak dapat mengidentifikasi setiap baris secara unik, kolom tambahan dapat ditambahkan ke klausa ORDERBY jika kolom tambahan tersebut memungkinkan setiap baris diidentifikasi secara unik. Jika itu tidak memungkinkan, kesalahan akan dikembalikan. Dalam kasus terakhir ini, menentukan MATCHBY dapat membantu mengatasi kesalahan.

Jika MATCHBY ditentukan, kolom di MATCHBY dan PARTITIONBY digunakan untuk mengidentifikasi setiap baris secara unik. Jika itu tidak memungkinkan, kesalahan akan dikembalikan. Bahkan jika MATCHBY tidak diperlukan, pertimbangkan untuk secara eksplisit menentukan MATCHBY untuk menghindari kebingungan.

Melanjutkan dari contoh di atas, berikut adalah ekspresi terakhir:

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

Jika kita ingin menjadi eksplisit tentang bagaimana baris harus diidentifikasi secara unik, kita dapat menentukan MATCHBY seperti yang ditunjukkan dalam ekspresi yang setara berikut:

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

Karena MATCHBY ditentukan, kedua kolom yang ditentukan dalam MATCHBY serta di PARTITIONBY digunakan untuk mengidentifikasi baris secara unik. Karena Warna ditentukan dalam MATCHBY dan PARTITIONBY, ekspresi berikut setara dengan ekspresi sebelumnya:

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

Karena menentukan MATCHBY tidak diperlukan dalam contoh yang telah kita lihat sejauh ini, mari kita lihat contoh yang sedikit berbeda yang memerlukan MATCHBY. Dalam hal ini, kami memiliki daftar baris pesanan. Setiap baris mewakili baris pesanan untuk pesanan. Pesanan dapat memiliki beberapa baris pesanan dan baris pesanan 1 muncul pada banyak pesanan. Selain itu, untuk setiap baris pesanan kami memiliki ProductKey dan SalesAmount. Sampel kolom yang relevan pada tabel terlihat seperti ini:

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

Perhatikan SalesOrderNumber dan SalesOrderLineNumber keduanya diperlukan untuk mengidentifikasi baris secara unik.

Untuk setiap pesanan, kami ingin mengembalikan jumlah penjualan sebelumnya dari produk yang sama (diwakili oleh ProductKey) yang dipesan oleh SalesAmount dalam urutan menurun. Ekspresi berikut tidak akan berfungsi karena berpotensi ada beberapa baris dalam vRelation karena diteruskan ke OFFSET:

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

Ekspresi ini mengembalikan kesalahan: "Parameter Relasi OFFSET mungkin memiliki baris duplikat, yang tidak diizinkan."

Untuk membuat ekspresi ini berfungsi, MATCHBY harus ditentukan dan harus menyertakan semua kolom yang secara unik menentukan baris. MATCHBY diperlukan di sini karena relasi, FactInternetSales, tidak berisi kunci eksplisit atau kolom unik. Namun, kolom SalesOrderNumber dan SalesOrderLineNumber bersama-sama membentuk kunci komposit, di mana keberadaannya bersama-sama unik dalam hubungan dan oleh karena itu dapat mengidentifikasi setiap baris secara unik. Hanya menentukan SalesOrderNumber atau SalesOrderLineNumber tidak cukup karena kedua kolom berisi nilai berulang. Ekspresi berikut menyelesaikan masalah:

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

Dan ekspresi ini memang mengembalikan hasil yang kita cari:

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount	Jumlah Penjualan Sebelumnya
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

Konten terkait

ORDERBY
PARTITIONBY
MATCHBY
INDEX
OFFSET
WINDOW
RANK
ROWNUMBER