Data Analysis Expressions (DAX) di Power Pivot

Memahami Rumus DAX

Rumus DAX sangat mirip dengan rumus Excel. Untuk membuatnya, ketik tanda sama dengan, diikuti dengan nama fungsi atau ekspresi, dan setiap nilai atau argumen yang diperlukan. Seperti Excel, DAX menyediakan berbagai fungsi yang bisa Anda gunakan untuk bekerja dengan string, melakukan penghitungan menggunakan tanggal dan waktu, atau membuat nilai bersyarat.

Namun, rumus DAX berbeda dengan cara penting berikut ini:

Jika Anda ingin mengkustomisasi penghitungan berdasarkan baris demi baris, DAX menyertakan fungsi yang memungkinkan Anda menggunakan nilai baris saat ini atau nilai terkait untuk melakukan penghitungan yang berbeda-beda menurut konteks.
DAX menyertakan tipe fungsi yang mengembalikan tabel sebagai hasilnya, bukan nilai tunggal. Fungsi ini dapat digunakan untuk memberikan input ke fungsi lain.
Fungsi Kecerdasan Waktudalam DAX memungkinkan penghitungan menggunakan rentang tanggal, dan membandingkan hasil di seluruh periode paralel.

Tempat Menggunakan Rumus DAX

Anda bisa membuat rumus di Power Pivot baik dalam olumnc terhitung atau dalam ieldf terhitung.

Kolom Terhitung

Kolom terhitung adalah kolom yang Anda tambahkan ke tabel Power Pivot yang sudah ada. Alih-alih menempelkan atau mengimpor nilai dalam kolom, Anda membuat rumus DAX yang menentukan nilai kolom. Jika Anda menyertakan tabel Power Pivot dalam PivotTable (atau PivotChart), kolom terhitung dapat digunakan seperti kolom data lainnya.

Rumus dalam kolom terhitung mirip dengan rumus yang Anda buat di Excel. Namun, tidak seperti di Excel, Anda tidak bisa membuat rumus berbeda untuk baris yang berbeda dalam tabel; sebagai gantinya, rumus DAX secara otomatis diterapkan ke seluruh kolom.

Ketika kolom berisi rumus, nilai dihitung untuk setiap baris. Hasilnya akan dihitung untuk kolom segera setelah Anda membuat rumus. Nilai kolom hanya dihitung ulang jika data yang mendasar direfresh atau jika pengkalkulasian ulang manual digunakan.

Anda bisa membuat kolom terhitung yang didasarkan pada pengukuran dan kolom terhitung lainnya. Namun, hindari menggunakan nama yang sama untuk kolom terhitung dan pengukuran, karena hal ini dapat mengakibatkan hasil yang membingungkan. Ketika merujuk ke kolom, sebaiknya gunakan referensi kolom yang sepenuhnya memenuhi syarat, untuk menghindari tindakan yang tidak disengaja.

Untuk informasi selengkapnya, lihat Kolom Terhitung di Power Pivot.

Langkah

Pengukuran adalah rumus yang dibuat khusus untuk digunakan dalam PivotTable (atau PivotChart) yang menggunakan data Power Pivot . Pengukuran bisa didasarkan pada fungsi agregasi standar, seperti COUNT atau SUM, atau Anda bisa menentukan rumus Anda sendiri dengan menggunakan DAX. Pengukuran digunakan dalam area Nilai pivotTable. Jika Anda ingin menempatkan hasil terhitung di area pivotTable yang berbeda, gunakan kolom terhitung sebagai gantinya.

Saat Anda menentukan rumus untuk pengukuran eksplisit, tidak ada yang terjadi hingga Anda menambahkan pengukuran ke dalam PivotTable. Saat Anda menambahkan pengukuran, rumus dievaluasi untuk setiap sel di area Nilai pivotTable. Karena hasil dibuat untuk setiap kombinasi header baris dan kolom, hasil untuk pengukuran bisa berbeda di setiap sel.

Definisi pengukuran yang Anda buat disimpan dengan tabel data sumbernya. Muncul dalam daftar Bidang PivotTable dan tersedia untuk semua pengguna buku kerja.

Untuk informasi selengkapnya, lihat Pengukuran di Power Pivot.

Membuat Rumus dengan Menggunakan Bilah Rumus

Power Pivot, seperti Excel, menyediakan bilah rumus untuk memudahkan membuat dan mengedit rumus, serta fungsionalitas LengkapiOtomatis, untuk meminimalkan kesalahan pengetikan dan sintaks.

Untuk memasukkan nama tabel Mulai ketikkan nama tabel. Rumus LengkapiOtomatis menyediakan daftar turun bawah yang berisi nama valid yang dimulai dengan huruf tersebut.

Untuk memasukkan nama kolom Ketikkan tanda kurung siku, lalu pilih kolom dari daftar kolom dalam tabel saat ini. Untuk kolom dari tabel lain, mulailah mengetik huruf pertama nama tabel, lalu pilih kolom dari daftar turun bawah LengkapiOtomatis.

Untuk detail selengkapnya dan panduan cara menyusun rumus, lihat Membuat Rumus untuk Penghitungan di Power Pivot.

Tips untuk Menggunakan LengkapiOtomatis

Anda bisa menggunakan Rumus LengkapiOtomatis di tengah rumus yang sudah ada dengan fungsi bertumpuk. Teks tepat sebelum titik penyisipan digunakan untuk menampilkan nilai dalam daftar menurun, dan semua teks setelah titik penyisipan tetap tidak berubah.

Nama yang ditentukan yang Anda buat untuk konstanta tidak ditampilkan dalam daftar turun bawah LengkapiOtomatis, tapi Anda masih bisa mengetiknya.

Power Pivot tidak menambahkan tanda kurung tutup fungsi atau mencocokkan tanda kurung secara otomatis. Anda harus memastikan bahwa setiap fungsi secara sinaktis benar atau Anda tidak bisa menyimpan atau menggunakan rumus.

Menggunakan Beberapa Fungsi dalam Rumus

Anda bisa menumpuk fungsi, yang berarti Anda menggunakan hasil dari satu fungsi sebagai argumen fungsi lain. Anda bisa menumpuk hingga 64 tingkat fungsi dalam kolom terhitung. Namun, bertumpuk bisa menyulitkan untuk membuat atau memecahkan masalah rumus.

Banyak fungsi DAX dirancang untuk digunakan hanya sebagai fungsi bertumpuk. Fungsi ini mengembalikan tabel, yang tidak dapat disimpan secara langsung sebagai hasilnya; harus disediakan sebagai input ke fungsi tabel. Misalnya, fungsi SUMX, AVERAGEX, dan MINX semuanya memerlukan tabel sebagai argumen pertama.

Catatan: Beberapa batasan pada penumpukan fungsi ada dalam pengukuran, untuk memastikan bahwa kinerja tidak terpengaruh oleh banyak penghitungan yang diperlukan oleh dependensi di antara kolom.

Membandingkan Fungsi DAX dan Fungsi Excel

Pustaka fungsi DAX didasarkan pada pustaka fungsi Excel, tetapi pustaka memiliki banyak perbedaan. Bagian ini meringkas perbedaan dan kesamaan antara fungsi Excel dan fungsi DAX.

Banyak fungsi DAX memiliki nama yang sama dan perilaku umum yang sama seperti fungsi Excel tetapi telah dimodifikasi untuk mengambil tipe input yang berbeda, dan dalam beberapa kasus, mungkin mengembalikan tipe data yang berbeda. Umumnya, Anda tidak bisa menggunakan fungsi DAX dalam rumus Excel atau menggunakan rumus Excel dalam Power Pivot tanpa beberapa modifikasi.
Fungsi DAX tidak pernah mengambil referensi sel atau rentang sebagai referensi, tetapi fungsi DAX mengambil kolom atau tabel sebagai referensi.
Fungsi tanggal dan waktu DAX mengembalikan tipe data datetime. Sebaliknya, fungsi tanggal dan waktu Excel mengembalikan bilangan bulat yang menunjukkan tanggal sebagai nomor seri.
Sebagian besar fungsi DAX baru mengembalikan tabel nilai atau membuat penghitungan berdasarkan tabel nilai sebagai input. Sebaliknya, Excel tidak memiliki fungsi yang mengembalikan tabel, tetapi beberapa fungsi bisa bekerja dengan array. Kemampuan untuk mereferensikan tabel dan kolom yang lengkap dengan mudah adalah fitur baru di Power Pivot.
DAX menyediakan fungsi pencarian baru yang mirip dengan fungsi pencarian array dan vektor di Excel. Namun, fungsi DAX mengharuskan hubungan dibuat di antara tabel.
Data dalam kolom diharapkan selalu memiliki tipe data yang sama. Jika data bukan tipe yang sama, DAX mengubah seluruh kolom menjadi tipe data yang paling baik mengakomodasi semua nilai.

Tipe Data DAX

Anda bisa mengimpor data ke model data Power Pivot dari berbagai sumber data yang mungkin mendukung tipe data yang berbeda. Saat Anda mengimpor atau memuat data, lalu menggunakan data dalam perhitungan atau dalam PivotTable, data dikonversi menjadi salah satu Power Pivot tipe data. Untuk daftar tipe data, lihat Tipe data dalam Model Data.

Tipe data tabel adalah tipe data baru di DAX yang digunakan sebagai input atau output ke banyak fungsi baru. Misalnya, fungsi FILTER mengambil tabel sebagai input dan output tabel lain yang hanya berisi baris yang memenuhi kondisi filter. Dengan menggabungkan fungsi tabel dengan fungsi agregasi, Anda dapat melakukan penghitungan kompleks melalui kumpulan data yang ditentukan secara dinamis. Untuk informasi selengkapnya, lihat Agregasi di Power Pivot.

Rumus dan Model Relasi

Jendela Power Pivotadalah area di mana Anda bisa bekerja dengan beberapa tabel data dan menyambungkan tabel dalam model reLASI. Dalam model data ini, tabel tersambung satu sama lain menurut hubungan, yang memungkinkan Anda membuat korelasi dengan kolom di tabel lain dan membuat penghitungan yang lebih menarik. Misalnya, Anda dapat membuat rumus yang menjumlahkan nilai untuk tabel terkait lalu menyimpan nilai tersebut dalam satu sel. Atau, untuk mengontrol baris dari tabel terkait, Anda bisa menerapkan filter ke tabel dan kolom. Untuk informasi selengkapnya, lihat Hubungan antar tabel dalam Model Data.

Karena Anda bisa menautkan tabel dengan menggunakan hubungan, PivotTable Anda juga bisa menyertakan data dari beberapa kolom yang berasal dari tabel yang berbeda.

Namun, karena rumus bisa bekerja dengan seluruh tabel dan kolom, Anda perlu mendesain penghitungan secara berbeda dari yang Anda lakukan di Excel.

Secara umum, rumus DAX dalam kolom selalu diterapkan ke seluruh rangkaian nilai dalam kolom (tidak pernah hanya untuk beberapa baris atau sel).
Tabel di Power Pivot harus selalu memiliki jumlah kolom yang sama di setiap baris, dan semua baris dalam kolom harus berisi tipe data yang sama.
Ketika tabel tersambung oleh hubungan, Anda diharapkan untuk memastikan bahwa dua kolom yang digunakan sebagai kunci memiliki nilai yang cocok, sebagian besar. Karena Power Pivot tidak menerapkan integritas referensial, dimungkinkan untuk memiliki nilai yang tidak cocok dalam kolom kunci dan masih membuat hubungan. Namun, adanya nilai kosong atau tidak cocok mungkin mempengaruhi hasil rumus dan tampilan PivotTable. Untuk informasi selengkapnya, lihat Pencarian dalam Rumus Power Pivot.
Saat Anda menautkan tabel dengan menggunakan hubungan, Anda memperbesar lingkup, atau c ontext di mana rumus Anda dievaluasi. Misalnya, rumus dalam PivotTable bisa dipengaruhi oleh filter atau judul kolom dan baris dalam PivotTable. Anda bisa menulis rumus yang memanipulasi konteks, tetapi konteks juga bisa menyebabkan hasil Anda berubah dengan cara yang mungkin tidak Anda antisipasi. Untuk informasi selengkapnya, lihat Konteks dalam Rumus DAX.

Memperbarui Hasil Rumus

Data r efresh dan perhitungan ulang adalah dua operasi terpisah namun terkait yang harus Anda pahami saat mendesain model data yang berisi rumus kompleks, data berjumlah besar, atau data yang diperoleh dari sumber data eksternal.

Melakukan refresh data adalah proses memperbarui data di buku kerja Anda dengan data baru dari sumber data eksternal. Anda bisa melakukan refresh data secara manual pada interval yang Anda tentukan. Atau, jika Anda telah menerbitkan buku kerja ke situs SharePoint, Anda bisa menjadwalkan refresh otomatis dari sumber eksternal.

Pengkalkulasian ulang adalah proses memperbarui hasil rumus untuk mencerminkan perubahan pada rumus itu sendiri dan untuk mencerminkan perubahan tersebut dalam data yang mendasar. Pengkalkulasian ulang dapat memengaruhi kinerja dengan cara berikut:

Untuk kolom terhitung, hasil rumus harus selalu dihitung ulang untuk seluruh kolom, setiap kali Anda mengubah rumus.
Untuk pengukuran, hasil rumus tidak dihitung hingga pengukuran ditempatkan dalam konteks PivotTable atau PivotChart. Rumus juga akan dihitung ulang saat Anda mengubah judul baris atau kolom apa pun yang memengaruhi filter pada data atau saat Anda melakukan refresh PivotTable secara manual.

Pemecahan Masalah Rumus

Kesalahan saat menulis rumus

Jika Anda mendapatkan kesalahan saat menetapkan rumus, rumus mungkin berisi kesalahan syntactic, kesalahan semantik, atau kesalahan penghitungan.

Kesalahan syntactic adalah yang paling mudah untuk diatasi. Tanda kurung atau koma yang hilang biasanya melibatkan tanda kurung atau koma yang hilang. Untuk bantuan dengan sintaks fungsi individual, lihat Referensi Fungsi DAX.

Tipe kesalahan lainnya terjadi ketika sintaks benar, tetapi nilai atau kolom yang direferensikan tidak masuk akal dalam konteks rumus. Kesalahan semantik dan penghitungan semacam itu mungkin disebabkan oleh salah satu masalah berikut:

Rumus merujuk ke kolom, tabel, atau fungsi yang tidak ada.
Rumus tampak benar, tetapi ketika mesin data mengambil data, rumus menemukan tipe yang tidak cocok, dan menimbulkan kesalahan.
Rumus mengirimkan jumlah atau tipe parameter yang salah ke fungsi.
Rumus merujuk ke kolom lain yang memiliki kesalahan, dan oleh karena itu nilainya tidak valid.
Rumus merujuk ke kolom yang belum diproses, yang berarti rumus memiliki metadata tetapi tidak ada data aktual yang digunakan untuk penghitungan.

Dalam empat kasus pertama, DAX menandai seluruh kolom yang berisi rumus yang tidak valid. Dalam kasus terakhir, DAX akan mengecilkan kolom untuk menunjukkan bahwa kolom berada dalam status yang tidak diproscesikan.

Hasil yang salah atau tidak biasa saat memberi peringkat atau mengurutkan nilai kolom

Saat memberi peringkat atau mengurutkan kolom yang berisi nilai NaN (Bukan Angka), Anda mungkin mendapatkan hasil yang salah atau tidak diharapkan. Misalnya, ketika perhitungan membagi 0 dengan 0, hasil NaN dikembalikan.

Ini karena mesin rumus melakukan urutan dan peringkat dengan membandingkan nilai numerik; namun, NaN tidak dapat dibandingkan dengan angka lain dalam kolom.

Untuk memastikan hasil yang benar, Anda dapat menggunakan pernyataan bersyarat menggunakan fungsi IF untuk menguji nilai NaN dan mengembalikan nilai numerik 0.

Kompatibilitas dengan Model Tabular Analysis Services dan Mode DirectQuery

Secara umum, rumus DAX yang Anda susun di Power Pivot sepenuhnya kompatibel dengan model tabular Analysis Services. Namun, jika Anda melakukan migrasi model Power Pivot ke instans Analysis Services, lalu menyebarkan model dalam mode DirectQuery, ada beberapa batasan.

Beberapa rumus DAX mungkin mengembalikan hasil yang berbeda jika Anda menggunakan model dalam mode DirectQuery.
Beberapa rumus mungkin menyebabkan kesalahan validasi saat Anda menggunakan model ke mode DirectQuery, karena rumus tersebut berisi fungsi DAX yang tidak didukung terhadap sumber data relasiawan.

Untuk informasi selengkapnya, lihat dokumentasi pemodelan tabular Analysis Services di SQL Server 2012 BooksOnline.