Pencarian dalam Rumus Power Pivot

Salah satu fitur tercanggih dalam Power Pivot adalah kemampuan membuat hubungan di antara tabel lalu menggunakan tabel tersebut untuk mencari atau memfilter data yang terkait. Nilai yang terkait diambil dari tabel menggunakan bahasa rumus yang tersedia di Power Pivot, Data Analysis Expressions (DAX). DAX menggunakan model relasi sehingga nilai yang terkait atau sesuai dapat diambil dengan mudah dan akurat dari tabel maupun kolom lainnya. Jika Anda terbiasa dengan VLOOKUP di Excel, fungsi ini mirip dengan fungsi di Power Pivot, namun fungsi di PowerPivot jauh lebih mudah digunakan.

Anda dapat membuat rumus yang menjalankan pencarian sebagai bagian dari kolom hitung maupun bidang hitung untuk digunakan dalam PivotTable atau PivotChart. Untuk informasi lebih lanjut, lihat topik berikut ini:

Bidang Terhitung di Power Pivot

Kolom Terhitung di Power Pivot

Bagian ini menjelaskan tentang fungsi DAX yang tersedia untuk pencarian serta beberapa contoh cara menggunakan fungsi tersebut.

 Catatan    Tergantung pada tipe operasi pencarian atau rumus pencarian yang akan digunakan, Anda mungkin harus membuat relasi di antara tabel terlebih dulu.

Memahami Fungsi Pencarian

Kemampuan mencari data yang cocok atau terkait dari tabel lain akan bermanfaat khususnya dalam situasi bila tabel saat ini hanya memiliki pengidentifikasi tipe yang sama, namun data yang diperlukan (misalnya, harga produk, nama, atau nilai rinci lainnya) disimpan dalam tabel yang terkait. Kemampuan ini juga bermanfaat bila terdapat beberapa baris dalam tabel lain yang terkait dengan baris atau nilai saat ini. Misalnya, Anda dapat dengan mudah mengambil semua penjualan yang terkait dengan wilayah, toko, atau staf penjualan tertentu.

Berbeda dengan fungsi pencarian Excel seperti VLOOKUP yang didasarkan pada larik atau LOOKUP yang mengambil beberapa nilai pencocokan pertama, DAX mengikuti relasi yang ada di antara tabel yang digabungkan dengan kunci untuk mengambil nilai tunggal terkait yang benar-benar cocok. DAX juga dapat mengambil tabel data yang terkait dengan data saat ini.

 Catatan    Jika Anda terbiasa dengan database relasional, Anda akan berpendapat bahwa pencarian di Power Pivot mirip dengan pernyataan subpilihan bersarang dalam Transact-SQL.

Mengambil Nilai Tunggal Terkait

Fungsi RELATED menghasilkan nilai tunggal dari tabel lain yang terkait dengan nilai dalam tabel saat ini. Anda menetapkan kolom berisi data yang diinginkan, lalu fungsi akan mengikuti relasi yang ada di antara tabel untuk mengambil nilai dari kolom yang ditetapkan tersebut dalam tabel terkait. Dalam beberapa kasus, fungsi harus mengikuti rantai relasi untuk mengambil data.

Misalnya, Anda memiliki daftar pengiriman hari ini dalam Excel. Namun, daftar hanya berisi nomor ID karyawan, nomor ID pesanan, dan nomor ID pengirim sehingga membuat laporan sulit dibaca. Untuk mendapatkan informasi tambahan yang diinginkan, Anda dapat mengkonversi daftar ke tabel yang tertaut dengan Power Pivot, lalu membuat hubungan ke tabel Karyawan dan Penjual yang mencocokkan bidang EmployeeID ke EmployeeKey serta bidang ResellerID ke ResellerKey.

Untuk menampilkan informasi pencarian dalam tabel terkait, Anda menambahkan dua kolom penghitungan baru dengan rumus berikut:

= RELATED('Employees'[EmployeeName])
= RELATED('Resellers'[CompanyName])

Sebelum pencarian

Setelah pencarian

ID Pesanan

EmployeeID

ResellerID

100314 230 445
100315 15 445
100316 76 108

ID Pesanan

EmployeeID

ResellerID

Karyawan

Penjual

100314 230 445 Kuppa Vamsi Modular Cycle Systems
100315 15 445 Pilar Ackeman Modular Cycle Systems

EmployeeID

Karyawan

Penjual

100316 76 108 Kim Ralls Associated Bikes
230 Kuppa Vamsi Modular Cycle Systems
15 Pilar Ackeman Modular Cycle Systems
76 Kim Ralls Associated Bikes

Fungsi menggunakan relasi antara tabel terkait dengan tabel Karyawan dan Penjual untuk mendapatkan nama yang tepat pada setiap baris dalam laporan. Anda juga dapat menggunakan nilai terkait untuk penghitungan. Untuk contoh dan informasi lebih lanjut, lihat Fungsi RELATED.

Mengambil Daftar Nilai Terkait

Fungsi RELATEDTABLE mengikuti relasi yang ada dan menghasilkan tabel berisi semua baris yang cocok dari tabel yang ditetapkan. Misalnya, Anda ingin mengetahui jumlah pesanan yang telah diajukan setiap penjual tahun ini. Anda dapat membuat kolom penghitungan baru dalam tabel Penjual dengan mencakup rumus berikut, yang mencari data setiap penjual dalam tabel ResellerSales_USD, lalu menghitung jumlah setiap pesanan yang diajukan oleh masing-masing penjual. Tabel ini merupakan bagian dari workbook sampel DAX. Untuk informasi lebih lanjut tentang sampel data, lihat Mengambil data sampel untuk tutorial DAX dan Model Data.

=COUNTROWS(RELATEDTABLE(ResellerSales_USD))

Dalam rumus ini, fungsi RELATEDTABLE lebih dulu mengambil nilai ResellerKey untuk setiap penjual dalam tabel saat ini. (Anda tidak harus menentukan kolom ID di mana pun dalam rumus karena Power Pivot menggunakan relasi yang ada di antara tabel.) Fungsi RELATEDTABLE mengambil semua baris dari tabel ResellerSales_USD yang terkait dengan setiap penjual, lalu menghitung baris. Jika tidak ada relasi (langsung atau tidak langsung) di antara kedua tabel, maka Anda akan mendapatkan semua baris dari tabel ResellerSales_USD.

Untuk penjual Modular Cycle Systems dalam database sampel, terdapat empat pesanan dalam tabel penjualan sehingga fungsi menghasilkan 4 baris data. Untuk penjual Associated Bikes yang tidak memiliki penjualan, fungsi menghasilkan baris data kosong.

Penjual

Data dalam tabel penjualan untuk penjual ini

Modular Cycle Systems

Reseller ID

SalesOrderNumber

445 SO53494
445 SO71872
445 SO65233
445 SO59000
Reseller ID SalesOrderNumber

Associated Bikes

 Catatan    Karena fungsi RELATEDTABLE menghasilkan tabel, bukan nilai tunggal, maka fungsi harus digunakan sebagai argumen untuk fungsi yang menjalankan operasi pada tabel. Untuk informasi lebih lanjut, lihat Fungsi RELATEDTABLE.

Atas Halaman Atas Halaman

 
 
Berlaku pada:
Excel 2013, Power Pivot in Excel 2013