Salah satu fitur paling canggih di Power Pivot adalah kemampuan untuk membuat hubungan antar tabel lalu menggunakan tabel terkait untuk mencari atau memfilter data terkait. Anda mengambil nilai terkait dari tabel dengan menggunakan bahasa rumus yang disediakan dengan Power Pivot, Ekspresi Analisis Data (DAX). DAX menggunakan model relasional dan oleh karena itu dapat dengan mudah dan akurat mengambil nilai terkait atau terkait dalam tabel atau kolom lain. Jika sudah terbiasa dengan VLOOKUP di Excel, fungsionalitas di Power Pivot ini serupa, tetapi jauh lebih mudah diterapkan.
Anda dapat membuat rumus yang melakukan pencarian sebagai bagian dari kolom terhitung, atau sebagai bagian dari pengukuran untuk digunakan dalam PivotTable atau PivotChart. Untuk informasi selengkapnya, lihat topik berikut ini:
Kolom Terhitung di Power Pivot
Bagian ini menjelaskan fungsi DAX yang disediakan untuk pencarian, bersama dengan beberapa contoh cara menggunakan fungsi tersebut.
Catatan
Tergantung pada tipe operasi pencarian atau rumus pencarian yang ingin digunakan, Anda mungkin perlu membuat hubungan antar tabel terlebih dahulu.
Memahami fungsi pencarian
Kemampuan untuk mencari data yang cocok atau terkait dari tabel lain sangat berguna dalam situasi ketika tabel saat ini hanya memiliki pengidentifikasi tertentu, tetapi data yang Anda perlukan (seperti harga produk, nama, atau nilai detail lainnya) disimpan dalam tabel terkait. Ini juga berguna ketika ada beberapa baris di tabel lain yang terkait dengan baris saat ini atau nilai saat ini. Misalnya, Anda dapat dengan mudah mengambil semua penjualan yang terkait dengan kawasan, toko, atau tenaga penjualan tertentu.
Berbeda dengan fungsi pencarian Excel seperti VLOOKUP, yang didasarkan pada array, atau LOOKUP, yang mendapatkan nilai pertama dari beberapa nilai yang cocok, DAX mengikuti hubungan yang ada di antara tabel yang digabungkan dengan kunci untuk mendapatkan satu nilai terkait yang sama persis. DAX juga dapat mengambil tabel rekaman yang terkait dengan catatan saat ini.
Catatan
Jika terbiasa dengan database relasional, Anda dapat menganggap pencarian di Power Pivot mirip dengan pernyataan subselect bertumpuk di Transact-SQL.
Mengambil satu nilai terkait
Fungsi RELATED menghasilkan satu nilai dari tabel lain yang terkait dengan nilai saat ini dalam tabel saat ini. Anda menentukan kolom yang berisi data yang Anda inginkan, dan fungsi mengikuti hubungan yang ada antar tabel untuk mengambil nilai dari kolom yang ditentukan dalam tabel terkait. Dalam beberapa kasus, fungsi harus mengikuti rantai hubungan untuk mengambil data.
Sebagai contoh, misalnya Anda memiliki daftar pengiriman hari ini di Excel. Namun, daftar tersebut hanya berisi nomor ID karyawan, nomor ID pesanan, dan nomor ID pengiriman, sehingga laporan sulit dibaca. Untuk mendapatkan informasi tambahan yang diinginkan, Anda dapat mengonversi daftar tersebut menjadi tabel tertaut Power Pivot, lalu membuat hubungan ke tabel Karyawan dan Penjual (Reseller), mencocokkan EmployeeID dengan bidang EmployeeKey, dan ResellerID dengan bidang ResellerKey.
Untuk menampilkan informasi pencarian dalam tabel tertaut, tambahkan dua kolom kalkulasi baru, dengan rumus berikut:
= RELATED('Employees'[EmployeeName])
= RELATED('Resellers'[CompanyName])
Pengiriman hari ini sebelum pencarian
| IDPesanan | ID Karyawan | ID Penjual |
|---|---|---|
| 100314 | 230 | 445 |
| 100315 | 15 | 445 |
| 100316 | 76 | 108 |
Tabel Karyawan
| ID Karyawan | Karyawan | Penjual |
|---|---|---|
| 230 | Kuppa Vamsi | Sistem Siklus Modular |
| 15 | Pilar Ackeman | Sistem Siklus Modular |
| 76 | Kim Ralls | Sepeda Terkait |
Pengiriman hari ini dengan pencarian
| IDPesanan | ID Karyawan | ID Penjual | Karyawan | Penjual |
|---|---|---|---|---|
| 100314 | 230 | 445 | Kuppa Vamsi | Sistem Siklus Modular |
| 100315 | 15 | 445 | Pilar Ackeman | Sistem Siklus Modular |
| 100316 | 76 | 108 | Kim Ralls | Sepeda Terkait |
Fungsi ini menggunakan hubungan antara tabel tertaut dan tabel Karyawan dan Penjual untuk mendapatkan nama yang benar untuk setiap baris dalam laporan. Anda juga dapat menggunakan nilai terkait untuk penghitungan. Untuk informasi dan contoh selengkapnya, lihat Fungsi TERKAIT.
Mengambil daftar nilai terkait
Fungsi RELATEDTABLE mengikuti hubungan yang ada, dan mengembalikan tabel yang berisi semua baris yang cocok dari tabel yang ditentukan. Misalnya, asumsikan Anda ingin mencari tahu berapa banyak pesanan yang telah dilakukan setiap reseller tahun ini. Anda dapat membuat kolom terhitung baru di tabel Penjual yang menyertakan rumus berikut, yang mencari catatan untuk setiap penjual dalam tabel ResellerSales_USD, dan menghitung jumlah pesanan individu yang dilakukan oleh setiap pengecer.
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
Dalam rumus ini, fungsi RELATEDTABLE terlebih dahulu mendapatkan nilai ResellerKey untuk setiap penjual dalam tabel saat ini. (Anda tidak perlu menentukan kolom ID di mana pun dalam rumus, karena Power Pivot menggunakan hubungan yang ada antar tabel.) Fungsi RELATEDTABLE kemudian mendapatkan semua baris dari tabel ResellerSales_USD yang terkait dengan setiap reseller, dan menghitung baris. Jika tidak ada hubungan (langsung atau tidak langsung) antara dua tabel, maka Anda akan mendapatkan semua baris dari tabel ResellerSales_USD.
Untuk pengecer Sistem Siklus Modular dalam database sampel kami, ada empat pesanan dalam tabel penjualan, sehingga fungsi menghasilkan 4. Untuk Sepeda Terkait, penjual tidak memiliki penjualan, sehingga fungsi ini mengembalikan kosong.
| Penjual | Catatan dalam tabel penjualan untuk penjual ini |
|---|---|
| Sistem Siklus Modular | ID Penjual |
| 445 | |
| 445 | |
| 445 | |
| 445 | |
| ID Penjual | |
| Sepeda Terkait |
Catatan
Karena fungsi RELATEDTABLE mengembalikan tabel, bukan satu nilai, fungsi tersebut harus digunakan sebagai argumen untuk fungsi yang melakukan operasi pada tabel. Untuk informasi selengkapnya, lihat Fungsi RELATEDTABLE.