Salah satu fitur yang paling canggih dalam 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 menggunakan bahasa rumus yang disediakan denganPower Pivot, Data Analysis Expressions (DAX). DAX menggunakan model relasi dan oleh karena itu dapat mengambil nilai terkait atau terkait dengan mudah dan akurat dalam tabel atau kolom lain. Jika Anda terbiasa dengan VLOOKUP di Excel, fungsionalitas dalam Power Pivot ini serupa, tetapi jauh lebih mudah diterapkan.
Anda bisa 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 menguraikan fungsi DAX yang disediakan untuk pencarian, bersama dengan beberapa contoh cara menggunakan fungsi.
Catatan: Bergantung pada tipe operasi pencarian atau rumus pencarian yang ingin Anda gunakan, 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 di mana tabel saat ini hanya memiliki pengidentifikasi dari beberapa jenis, tetapi data yang Anda perlukan (seperti harga produk, nama, atau nilai mendetail lainnya) disimpan dalam tabel terkait. Ini juga berguna ketika ada beberapa baris dalam tabel lain yang terkait dengan baris saat ini atau nilai saat ini. Misalnya, Anda dapat dengan mudah mengambil semua penjualan yang terikat ke kawasan, toko, atau tenaga penjual 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 sudah ada di antara tabel yang digabungkan dengan kunci untuk mendapatkan nilai terkait tunggal yang sama persis. DAX juga dapat mengambil tabel rekaman yang terkait dengan rekaman saat ini.
Catatan: Jika Anda terbiasa dengan database relasional, Anda dapat menganggap pencarian dalam Power Pivot mirip dengan pernyataan subpilih bertumpuk di Transact-SQL.
Mengambil Satu Nilai Terkait
Fungsi RELATED mengembalikan nilai tunggal 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 sudah ada di antara tabel untuk mengambil nilai dari kolom tertentu dalam tabel terkait. Dalam beberapa kasus, fungsi harus mengikuti rantai hubungan untuk mengambil data.
Misalnya, Anda memiliki daftar pengiriman hari ini di Excel. Namun, daftar hanya berisi nomor ID karyawan, nomor ID pesanan, dan nomor ID pengirim, membuat laporan sulit dibaca. Untuk mendapatkan informasi tambahan yang Anda inginkan, Anda bisa mengonversi daftar itu menjadi tabel Power Pivot tertaut, lalu membuat hubungan ke tabel Karyawan dan Penjual, mencocokkan IDPegawai ke bidang EmployeeKey, dan Id Penjual ke bidang PenjualKey.
Untuk menampilkan informasi pencarian dalam tabel tertaut, tambahkan dua kolom terhitung baru, dengan rumus berikut:
= RELATED('Employees'[EmployeeName]) = RELATED('Resellers'[CompanyName])
Pengiriman hari ini sebelum pencarian
|
IDPesanan |
IDPegawai |
ID Penjual |
|---|---|---|
|
100314 |
230 |
445 |
|
100315 |
15 |
445 |
|
100316 |
76 |
108 |
Tabel Karyawan
|
IDPegawai |
Karyawan |
Reseller |
|---|---|---|
|
230 |
Kuppa Vamsi |
Sistem Siklus Modular |
|
15 |
Pilar Ackeman |
Sistem Siklus Modular |
|
76 |
Kim Ralls |
Motor Terkait |
Pengiriman hari ini dengan pencarian
|
IDPesanan |
IDPegawai |
ID Penjual |
Karyawan |
Reseller |
|---|---|---|---|---|
|
100314 |
230 |
445 |
Kuppa Vamsi |
Sistem Siklus Modular |
|
100315 |
15 |
445 |
Pilar Ackeman |
Sistem Siklus Modular |
|
100316 |
76 |
108 |
Kim Ralls |
Motor 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 bisa menggunakan nilai terkait untuk penghitungan. Untuk informasi dan contoh selengkapnya, lihat Fungsi RELATED.
Mengambil Daftar Nilai Terkait
Fungsi RELATEDTABLE mengikuti hubungan yang sudah ada, dan mengembalikan tabel yang berisi semua baris yang cocok dari tabel tertentu. Misalnya, anggaplah Anda ingin mengetahui berapa banyak pesanan yang telah diletakkan oleh setiap penjual tahun ini. Anda dapat membuat kolom terhitung baru dalam tabel Pengecer yang menyertakan rumus berikut, yang mencari rekaman untuk setiap penjual dalam tabel ResellerSales_USD, dan menghitung jumlah pesanan individual yang diletakkan oleh setiap penjual.
=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 saja dalam rumus, karena Power Pivot menggunakan hubungan yang sudah ada di antara tabel.) Fungsi RELATEDTABLE kemudian mendapatkan semua baris dari tabel ResellerSales_USD yang terkait dengan setiap penjual, dan menghitung baris. Jika tidak ada hubungan (langsung atau tidak langsung) di antara kedua tabel, maka Anda akan mendapatkan semua baris dari tabel ResellerSales_USD.
Untuk reseller Modular Cycle Systems dalam database sampel kami, terdapat empat pesanan dalam tabel penjualan, sehingga fungsi mengembalikan 4. Untuk Motor Terkait, penjual tidak memiliki penjualan, sehingga fungsi mengembalikan kosong.
|
Reseller |
Rekaman dalam tabel penjualan untuk penjual ini |
|
|---|---|---|
|
Sistem Siklus Modular |
ID Penjual |
SalesOrderNumber |
|
445 |
SO53494 |
|
|
445 |
SO71872 |
|
|
445 |
SO65233 |
|
|
445 |
SO59000 |
|
|
ID Penjual |
SalesOrderNumber |
|
|
Motor Terkait |
Catatan: Karena fungsi RELATEDTABLE mengembalikan tabel, bukan nilai tunggal, fungsi harus digunakan sebagai argumen untuk fungsi yang menjalankan operasi pada tabel. Untuk informasi selengkapnya, lihat Fungsi RELATEDTABLE.
