Konteks memungkinkan Anda melakukan analisis dinamis, di mana hasil rumus dapat berubah untuk mencerminkan pemilihan baris atau sel saat ini dan juga data terkait. Memahami konteks dan menggunakan konteks secara efektif sangat penting untuk membangun rumus berkinerja tinggi, analisis dinamis, dan untuk memecahkan masalah dalam rumus.
Bagian ini menentukan berbagai jenis konteks: konteks baris, konteks kueri, dan konteks filter. Contoh ini menjelaskan cara konteks dievaluasi untuk rumus dalam kolom terhitung dan dalam PivotTable.
Bagian terakhir dari artikel ini menyediakan tautan ke contoh mendetail yang menggambarkan bagaimana hasil rumus berubah sesuai dengan konteks.
Memahami konteks
Rumus di Power Pivot dapat dipengaruhi oleh filter yang diterapkan dalam PivotTable, oleh hubungan antar tabel, dan oleh filter yang digunakan dalam rumus. Konteks adalah apa yang memungkinkan untuk melakukan analisis dinamis. Memahami konteks penting untuk membangun dan memecahkan masalah rumus.
Ada berbagai jenis konteks: konteks baris, konteks kueri, dan konteks filter.
Konteks baris dapat dianggap sebagai "baris saat ini." Jika Anda telah membuat kolom terhitung, konteks baris terdiri dari nilai di setiap baris individu dan nilai dalam kolom yang terkait dengan baris saat ini. Ada juga beberapa fungsi (EARLIER dan EARLIEST) yang mendapatkan nilai dari baris saat ini, lalu menggunakan nilai tersebut saat melakukan operasi di seluruh tabel.
Konteks kueri mengacu pada subset data yang dibuat secara implisit untuk setiap sel dalam PivotTable, tergantung pada header baris dan kolom.
Konteks filter adalah kumpulan nilai yang diizinkan di setiap kolom, berdasarkan batasan filter yang diterapkan ke baris atau yang ditentukan oleh ekspresi filter dalam rumus.
Konteks baris
Jika Anda membuat rumus dalam kolom terhitung, konteks baris untuk rumus tersebut menyertakan nilai dari semua kolom di baris saat ini. Jika tabel terkait dengan tabel lain, konten juga menyertakan semua nilai dari tabel lain yang terkait dengan baris saat ini.
Misalnya, Anda membuat kolom terhitung, =[Pengangkutan] + [Pajak], yang menjumlahkan dua kolom dari tabel yang sama. Rumus ini berlaku seperti rumus dalam tabel Excel, yang secara otomatis mereferensikan nilai dari baris yang sama. Perhatikan bahwa tabel berbeda dengan rentang: Anda tidak dapat mereferensikan nilai dari baris sebelum baris saat ini dengan menggunakan notasi rentang, dan Anda tidak dapat mereferensikan nilai tunggal arbitrer dalam tabel atau sel. Anda harus selalu bekerja dengan tabel dan kolom.
Konteks baris secara otomatis mengikuti hubungan antar tabel untuk menentukan baris mana dalam tabel terkait yang terkait dengan baris saat ini.
Misalnya, rumus berikut menggunakan fungsi RELATED untuk mengambil nilai pajak dari tabel terkait, berdasarkan wilayah tujuan pengiriman pesanan. Nilai pajak ditentukan dengan menggunakan nilai untuk wilayah dalam tabel saat ini, mencari wilayah dalam tabel terkait, lalu mendapatkan tarif pajak untuk wilayah tersebut dari tabel terkait.
= [Pengangkutan] + TERKAIT('Wilayah'[TaxRate])
Rumus ini hanya mendapatkan tarif pajak untuk wilayah saat ini, dari tabel Wilayah. Anda tidak perlu mengetahui atau menentukan kunci yang menghubungkan tabel.
Konteks beberapa baris
Selain itu, DAX menyertakan fungsi yang mengulangi penghitungan pada tabel. Fungsi ini dapat memiliki beberapa baris saat ini dan konteks baris saat ini. Dalam istilah pemrograman, Anda dapat membuat rumus yang berulang pada loop dalam dan luar.
Misalnya, buku kerja Anda berisi tabel Produk dan tabel Penjualan . Anda mungkin ingin menelusuri seluruh tabel penjualan, yang penuh dengan transaksi yang melibatkan beberapa produk, dan menemukan jumlah terbesar yang dipesan untuk setiap produk dalam satu transaksi.
Di Excel, penghitungan ini memerlukan serangkaian ringkasan menengah, yang harus dibuat ulang jika data berubah. Jika merupakan pengguna hebat Excel, Anda mungkin dapat membuat rumus array yang berhasil. Atau, dalam database relasional Anda dapat menulis subpilihan bertumpuk.
Namun, dengan DAX, Anda dapat membuat satu rumus yang mengembalikan nilai yang benar, dan hasilnya diperbarui secara otomatis setiap kali Anda menambahkan data ke tabel.
=MAXX(FILTER(Sales,[ProdKey]=SEBELUMNYA([ProdKey])),Sales[OrderQty])
Untuk panduan mendetail tentang rumus ini, lihat Fungsi SEBELUMNYA.
Singkatnya, fungsi EARLIER menyimpan konteks baris dari operasi yang mendahului operasi saat ini. Setiap saat, fungsi ini menyimpan dua set konteks dalam memori: satu kumpulan konteks mewakili baris saat ini untuk loop dalam rumus, dan kumpulan konteks lainnya mewakili baris saat ini untuk loop luar rumus. DAX secara otomatis memberi makan nilai di antara dua loop sehingga Anda dapat membuat agregat yang kompleks.
Konteks kueri
Konteks kueri mengacu pada subset data yang diambil secara implisit untuk sebuah rumus. Saat Anda menjatuhkan ukuran atau bidang nilai lainnya ke dalam sel dalam PivotTable, mesin Power Pivot memeriksa header baris dan kolom, Pemotong, dan filter laporan untuk menentukan konteks. Kemudian, Power Pivot membuat penghitungan yang diperlukan untuk mengisi setiap sel dalam PivotTable. Kumpulan data yang diambil adalah konteks kueri untuk setiap sel.
Karena konteks dapat berubah tergantung pada tempat Anda menempatkan rumus, hasil rumus juga berubah tergantung pada apakah Anda menggunakan rumus dalam PivotTable dengan banyak pengelompokan dan filter, atau dalam kolom terhitung tanpa filter dan konteks minimal.
Misalnya, Anda membuat rumus sederhana ini yang menjumlahkan nilai dalam kolom Laba tabel Penjualan :
=SUM('Penjualan'[Keuntungan])
Jika Anda menggunakan rumus ini dalam kolom terhitung dalam tabel Penjualan , hasil untuk rumus akan sama untuk seluruh tabel, karena konteks kueri untuk rumus selalu merupakan seluruh kumpulan data tabel Penjualan . Hasil Anda akan mendapat keuntungan untuk semua wilayah, semua produk, semua tahun, dan sebagainya.
Namun, biasanya Anda tidak ingin melihat hasil yang sama ratusan kali, tetapi sebaliknya Anda ingin mendapatkan keuntungan untuk tahun tertentu, negara atau kawasan tertentu, produk tertentu, atau beberapa kombinasi dari ini, dan kemudian mendapatkan total keseluruhan.
Dalam PivotTable, sangat mudah untuk mengubah konteks dengan menambahkan atau menghapus header kolom dan baris dan dengan menambahkan atau menghapus Pemotong. Anda dapat membuat rumus seperti yang di atas, dalam ukuran, lalu meletakkannya ke dalam PivotTable. Setiap kali menambahkan judul kolom atau baris ke PivotTable, Anda mengubah konteks kueri tempat pengukuran dievaluasi. Operasi pemotongan dan pemfilteran juga memengaruhi konteks. Oleh karena itu, rumus yang sama, yang digunakan dalam PivotTable, dievaluasi dalam konteks kueri yang berbeda untuk setiap sel.
Filter konteks
Konteks filter ditambahkan saat Anda menentukan batasan filter pada kumpulan nilai yang diizinkan dalam kolom atau tabel, dengan menggunakan argumen ke rumus. Konteks filter berlaku di atas konteks lain, seperti konteks baris atau konteks kueri.
Misalnya, PivotTable menghitung nilainya untuk setiap sel berdasarkan judul baris dan kolom, seperti yang dijelaskan di bagian sebelumnya tentang konteks kueri. Namun, dalam pengukuran atau kolom terhitung yang ditambahkan ke PivotTable, Anda dapat menentukan ekspresi filter untuk mengontrol nilai yang digunakan oleh rumus. Anda juga dapat menghapus filter pada kolom tertentu secara selektif.
Untuk informasi selengkapnya tentang cara membuat filter dalam rumus, lihat fungsi Filter.
Untuk contoh cara filter dapat dibersihkan untuk membuat jumlah total, lihat Fungsi ALL.
Untuk contoh cara menghapus dan menerapkan filter secara selektif dalam rumus, lihat Fungsi ALLEXCEPT.
Oleh karena itu, Anda harus meninjau definisi pengukuran atau rumus yang digunakan dalam PivotTable agar Anda mengetahui konteks filter saat menafsirkan hasil rumus.
Menentukan konteks dalam rumus
Saat Anda membuat rumus, Power Pivot untuk Excel terlebih dahulu memeriksa sintaks umum, lalu memeriksa nama kolom dan tabel yang Anda berikan terhadap kemungkinan kolom dan tabel dalam konteks saat ini. Jika Power Pivot tidak dapat menemukan kolom dan tabel yang ditentukan oleh rumus, Anda akan mendapatkan kesalahan.
Konteks ditentukan seperti yang dijelaskan di bagian sebelumnya, dengan menggunakan tabel yang tersedia dalam buku kerja, hubungan apa pun antar tabel, dan filter apa pun yang telah diterapkan.
Misalnya, jika Anda baru saja mengimpor beberapa data ke tabel baru dan belum menerapkan filter apa pun, seluruh kumpulan kolom dalam tabel adalah bagian dari konteks saat ini. Jika Anda memiliki beberapa tabel yang ditautkan oleh hubungan dan Anda bekerja dalam PivotTable yang telah difilter dengan menambahkan judul kolom dan menggunakan Pemotong, konteksnya mencakup tabel terkait dan filter apa pun pada data.
Konteks adalah konsep kuat yang juga dapat mempersulit pemecahan masalah rumus. Sebaiknya mulai dengan rumus dan hubungan sederhana untuk melihat cara kerja konteks, lalu mulai bereksperimen dengan rumus sederhana di PivotTable. Bagian berikut ini juga menyediakan beberapa contoh bagaimana rumus menggunakan berbagai tipe konteks untuk mengembalikan hasil secara dinamis.
Contoh konteks dalam rumus
- Fungsi RELATED memperluas konteks baris saat ini untuk menyertakan nilai dalam kolom terkait. Tindakan ini memungkinkan Anda melakukan pencarian. Contoh dalam topik ini menggambarkan interaksi pemfilteran dan konteks baris.
- Fungsi FILTER memungkinkan Anda menentukan baris yang akan disertakan dalam konteks saat ini. Contoh dalam topik ini juga menggambarkan cara menyematkan filter dalam fungsi lain yang melakukan agregat.
- Fungsi ALL mengatur konteks dalam rumus. Anda dapat menggunakannya untuk menimpa filter yang diterapkan sebagai hasil dari konteks kueri.
- Fungsi ALLEXCEPT memungkinkan Anda menghapus semua filter kecuali satu filter yang Anda tentukan. Kedua topik menyertakan contoh yang memandu Anda menyusun rumus dan memahami konteks yang kompleks.
- Fungsi EARLIER dan EARLIEST memungkinkan Anda menelusuri tabel dengan melakukan penghitungan, sambil mereferensikan nilai dari perulangan dalam. Jika Anda terbiasa dengan konsep rekursi dan dengan loop dalam dan luar, Anda akan menghargai kekuatan yang disediakan oleh fungsi EARLIER dan EARLY. Jika Anda baru mengetahui konsep ini, Anda harus mengikuti langkah-langkah dalam contoh dengan hati-hati untuk melihat bagaimana konteks dalam dan luar digunakan dalam penghitungan.
integritas referensial
Bagian ini membahas beberapa konsep tingkat lanjut yang terkait dengan nilai yang hilang dalam tabel Power Pivot yang dihubungkan oleh hubungan. Bagian ini mungkin berguna bagi Anda jika memiliki buku kerja dengan beberapa tabel dan rumus kompleks dan ingin mendapatkan bantuan untuk memahami hasilnya.
Jika Anda baru dalam konsep data relasional, sebaiknya baca terlebih dahulu topik pengantar, Gambaran Umum Hubungan.
Integritas referensial dan hubungan Power Pivot
Power Pivot tidak mengharuskan integritas referensial diterapkan di antara dua tabel untuk menentukan hubungan yang valid. Sebagai gantinya, baris kosong dibuat di ujung "satu" dari setiap hubungan satu-ke-banyak dan digunakan untuk menangani semua baris yang tidak cocok dari tabel terkait. Ini secara efektif berperilaku sebagai gabungan luar SQL.
Dalam PivotTable, jika Anda mengelompokkan data berdasarkan satu sisi hubungan, setiap data yang tidak cocok di banyak sisi hubungan akan dikelompokkan bersama dan akan disertakan dalam total dengan judul baris kosong. Judul kosong kira-kira setara dengan "anggota tidak diketahui."
Memahami anggota yang tidak dikenal
Konsep anggota yang tidak dikenal mungkin tidak asing bagi Anda jika Anda pernah bekerja dengan sistem database multidimensi, seperti SQL Server Analysis Services. Jika istilah tersebut baru bagi Anda, contoh berikut menjelaskan apa itu anggota yang tidak dikenal dan pengaruhnya terhadap penghitungan.
Misalnya, Anda membuat penghitungan yang menjumlahkan penjualan bulanan untuk setiap toko, tetapi satu kolom dalam tabel Penjualan tidak memiliki nilai untuk nama toko. Mengingat bahwa tabel untuk Store dan Sales dihubungkan oleh nama toko, apa yang Anda harapkan terjadi dalam rumus? Bagaimana PivotTable harus mengelompokkan atau menampilkan angka penjualan yang tidak terkait dengan toko yang sudah ada?
Masalah ini umum terjadi di gudang data, di mana tabel data fakta yang besar harus terkait secara logis dengan tabel dimensi yang berisi informasi tentang toko, wilayah, dan atribut lain yang digunakan untuk mengkategorikan dan menghitung fakta. Untuk menyelesaikan masalah, setiap fakta baru yang tidak terkait dengan entitas yang ada untuk sementara ditetapkan ke anggota yang tidak dikenal. Itulah sebabnya fakta yang tidak terkait akan muncul dikelompokkan dalam PivotTable di bawah judul kosong.
Perlakuan terhadap nilai kosong vs. baris kosong
Nilai kosong berbeda dengan baris kosong yang ditambahkan untuk mengakomodasi anggota yang tidak diketahui. Nilai kosong adalah nilai khusus yang digunakan untuk mewakili nilai null, string kosong, dan nilai lain yang hilang. Untuk informasi selengkapnya tentang nilai kosong, serta tipe data DAX lainnya, lihat Tipe data dalam Model Data.