Data Analysis Expressions (DAX) di Power Pivot

Ekspresi Analisis Data (DAX) terdengar sedikit menakutkan pada awalnya, tetapi jangan biarkan namanya membodohi Anda. Dasar-dasar DAX benar-benar cukup mudah dipahami. Hal pertama yang pertama - DAX BUKAN bahasa pemrograman. DAX adalah bahasa rumus. Anda dapat menggunakan DAX untuk menentukan penghitungan kustom untuk Kolom Terhitung dan untuk Pengukuran (juga dikenal sebagai bidang terhitung). DAX mencakup beberapa fungsi yang digunakan dalam rumus Excel, dan fungsi tambahan yang dirancang untuk bekerja dengan data relasional dan melakukan agregasi dinamis.

Memahami rumus DAX

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

Namun, rumus DAX berbeda dalam hal penting berikut:

• Jika Anda ingin menyesuaikan penghitungan baris demi baris, DAX menyertakan fungsi yang memungkinkan Anda menggunakan nilai baris saat ini atau nilai terkait untuk melakukan penghitungan yang bervariasi berdasarkan konteks.
• DAX menyertakan jenis fungsi yang mengembalikan tabel sebagai hasilnya, bukan satu nilai. Fungsi ini dapat digunakan untuk memberikan input ke fungsi lain.
• Fungsi Kecerdasan Waktudi DAX memungkinkan penghitungan menggunakan rentang tanggal, dan membandingkan hasilnya dalam periode paralel.

Tempat menggunakan rumus DAX

Anda dapat membuat rumus di Power Pivot baik di kolom terhitung maupun dalam bidang 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 yang Anda lakukan pada kolom data lainnya.

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

Jika kolom berisi rumus, nilai dihitung untuk setiap baris. Hasil dihitung untuk kolom segera setelah Anda membuat rumus. Nilai kolom hanya dihitung ulang jika data yang mendasari di-refresh atau jika penghitungan ulang manual digunakan.

Anda dapat membuat kolom terhitung yang didasarkan pada pengukuran dan kolom terhitung lainnya. Namun, hindari menggunakan nama yang sama untuk kolom dan pengukuran terhitung, karena hal ini dapat menyebabkan hasil yang membingungkan. Saat merujuk ke kolom, sebaiknya gunakan referensi kolom yang sepenuhnya memenuhi syarat, untuk menghindari pemanggilan pengukuran secara tidak sengaja.

Untuk informasi lebih lengkap, lihat Kolom Terhitung di Power Pivot.

Ukuran

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

Saat Anda menentukan rumus untuk pengukuran eksplisit, tidak akan terjadi apa pun hingga Anda menambahkan pengukuran tersebut ke dalam PivotTable. Saat Anda menambahkan pengukuran, rumus dievaluasi untuk setiap sel di area Nilai pada PivotTable. Karena hasil dibuat untuk setiap kombinasi header baris dan kolom, hasil untuk pengukuran dapat berbeda di setiap sel.

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

Untuk informasi lebih lengkap, lihat Pengukuran di Power Pivot.

Membuat rumus menggunakan bilah rumus

Power Pivot, seperti Excel, menyediakan bilah rumus untuk mempermudah pembuatan dan pengeditan rumus, serta fungsionalitas Pelengkap Otomatis, untuk meminimalkan kesalahan pengetikan dan sintaksis.

Untuk memasukkan nama tabel Mulai mengetikkan nama tabel. Rumus Pelengkap Otomatis menyediakan daftar drop-down yang berisi nama valid yang dimulai dengan huruf tersebut.

Untuk memasukkan nama kolom Ketik 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 menurun Pelengkapan Otomatis.

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

Tips untuk menggunakan Pelengkap Otomatis

Anda dapat menggunakan Pelengkapan Otomatis Rumus 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 tidak berubah.

Nama yang ditentukan yang Anda buat untuk konstanta tidak ditampilkan dalam daftar menurun Pelengkapan Otomatis, tetapi Anda masih dapat mengetikkannya.

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

Menggunakan beberapa fungsi dalam rumus

Anda dapat menumpuk fungsi, yang berarti Anda menggunakan hasil dari satu fungsi sebagai argumen dari fungsi lain. Anda dapat menumpuk hingga 64 tingkat fungsi dalam kolom terhitung. Namun, bersarang dapat mempersulit pembuatan atau pemecahan masalah rumus.

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

Membandingkan Fungsi DAX dan Fungsi Excel

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

• Banyak fungsi DAX memiliki nama yang sama dan perilaku umum yang sama dengan fungsi Excel, tetapi telah diubah untuk mengambil jenis input yang berbeda, dan dalam beberapa kasus, mungkin mengembalikan tipe data yang berbeda. Umumnya, Anda tidak dapat menggunakan fungsi DAX dalam rumus Excel atau menggunakan rumus Excel di Power Pivot tanpa beberapa modifikasi.
• Fungsi DAX tidak pernah mengambil referensi sel atau rentang sebagai referensi, melainkan fungsi DAX mengambil kolom atau tabel sebagai referensi.
• Fungsi tanggal dan waktu DAX mengembalikan tipe data tanggalwaktu. Sebaliknya, fungsi tanggal dan waktu Excel mengembalikan bilangan bulat yang mewakili tanggal sebagai nomor seri.
• Banyak fungsi DAX baru menghasilkan tabel nilai atau membuat penghitungan berdasarkan tabel nilai sebagai input. Sebaliknya, Excel tidak memiliki fungsi yang mengembalikan tabel, tetapi beberapa fungsi dapat berfungsi dengan array. Kemampuan untuk mereferensikan tabel dan kolom lengkap dengan mudah adalah fitur baru di Power Pivot.
• DAX menyediakan fungsi pencarian baru yang serupa dengan fungsi pencarian array dan vektor di Excel. Namun, fungsi DAX mengharuskan hubungan dibuat antar tabel.
• Data dalam kolom diharapkan selalu memiliki tipe data yang sama. Jika data tidak sama, DAX mengubah seluruh kolom menjadi tipe data yang paling mengakomodasi semua nilai.

Tipe data DAX

Anda dapat mengimpor data ke model data Power Pivot dari berbagai sumber data yang mungkin mendukung tipe data yang berbeda. Ketika Anda mengimpor atau memuat data, lalu menggunakan data tersebut dalam penghitungan atau dalam PivotTable, data akan dikonversi ke salah satu tipe data Power Pivot. 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 untuk banyak fungsi baru. Misalnya, fungsi FILTER mengambil tabel sebagai input dan menghasilkan tabel lain yang hanya berisi baris yang memenuhi kondisi filter. Dengan menggabungkan fungsi tabel dengan fungsi agregasi, Anda dapat melakukan penghitungan kompleks atas kumpulan data yang ditentukan secara dinamis. Untuk informasi selengkapnya, lihat Agregasi di Power Pivot.

Rumus dan model relasional

Jendela Power Pivot adalah area tempat Anda dapat bekerja dengan beberapa tabel data dan menghubungkan tabel dalam model relasional. Dalam model data ini, tabel dihubungkan satu sama lain oleh 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 dapat menerapkan filter ke tabel dan kolom. Untuk informasi selengkapnya, lihat Hubungan antar tabel dalam Model Data.

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

Namun, karena rumus dapat berfungsi dengan seluruh tabel dan kolom, Anda perlu mendesain penghitungan dengan cara yang berbeda dari yang Anda lakukan di Excel.

• Secara umum, rumus DAX dalam kolom selalu diterapkan ke seluruh kumpulan nilai dalam kolom (tidak pernah hanya ke 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 dihubungkan oleh suatu hubungan, Anda diharapkan untuk memastikan bahwa dua kolom yang digunakan sebagai kunci memiliki nilai yang sebagian besar cocok. Karena Power Pivot tidak menegakkan integritas referensial, ada kemungkinan untuk memiliki nilai yang tidak cocok dalam kolom kunci dan tetap membuat hubungan. Namun, adanya nilai kosong atau tidak cocok dapat memengaruhi hasil rumus dan tampilan PivotTable. Untuk informasi selengkapnya, lihat Pencarian di Rumus Power Pivot.
• Ketika menautkan tabel menggunakan hubungan, Anda memperbesar cakupan, atau konteks di mana rumus Anda dievaluasi. Misalnya, rumus dalam PivotTable dapat dipengaruhi oleh filter atau judul kolom dan baris apa pun di PivotTable. Anda dapat menulis rumus yang memanipulasi konteks, tetapi konteks juga dapat menyebabkan hasil Anda berubah dengan cara yang mungkin tidak Anda antisipasi. Untuk informasi selengkapnya, lihat Konteks dalam Rumus DAX.

Memperbarui hasil rumus

Refresh dan penghitungan ulang data adalah dua operasi terpisah namun terkait yang harus Anda pahami saat mendesain model data yang berisi rumus kompleks, data dalam jumlah besar, atau data yang diperoleh dari sumber data eksternal.

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

Penghitungan ulang adalah proses memperbarui hasil rumus untuk mencerminkan setiap perubahan pada rumus itu sendiri dan untuk mencerminkan perubahan tersebut dalam data dasar. Penghitungan 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 yang memengaruhi filter pada data, atau saat Anda merefresh PivotTable secara manual.

Memecahkan masalah rumus

Kesalahan saat menulis rumus

Jika Anda mendapatkan kesalahan saat mendefinisikan rumus, rumus tersebut mungkin berisi kesalahan sintaksis, kesalahan semantik, atau kesalahan penghitungan.

Kesalahan sintaksis adalah yang paling mudah untuk diatasi. Mereka biasanya melibatkan tanda kurung atau koma yang hilang. Untuk bantuan terkait sintaks masing-masing fungsi, lihat Referensi Fungsi DAX.

Jenis kesalahan lainnya terjadi jika sintaksnya benar, tetapi nilai atau kolom yang direferensikan tidak masuk akal dalam konteks rumus. Kesalahan semantik dan penghitungan tersebut mungkin disebabkan oleh salah satu masalah berikut:

• Rumus mengacu ke kolom, tabel, atau fungsi yang tidak ada.
• Rumusnya tampaknya benar, tetapi ketika mesin data mengambil data, mesin tersebut menemukan ketidakcocokan jenis, dan memunculkan kesalahan.
• Rumus meneruskan angka atau tipe parameter yang salah ke suatu fungsi.
• Rumus mengacu ke kolom lain yang memiliki kesalahan, dan oleh karena itu nilainya tidak valid.
• Rumus mengacu pada kolom yang belum diproses, artinya memiliki metadata tetapi tidak ada data aktual untuk digunakan untuk penghitungan.

Dalam empat kasus pertama, DAX menandai seluruh kolom yang berisi rumus yang tidak valid. Dalam kasus terakhir, DAX membuat kolom berwarna abu-abu untuk menunjukkan bahwa kolom tersebut dalam status tidak diproses.

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

Ketika memberi peringkat atau mengurutkan kolom yang berisi nilai NaN (Bukan Angka), Anda mungkin mendapatkan hasil yang salah atau tidak terduga. Misalnya, ketika penghitungan membagi 0 dengan 0, hasil NaN akan diberikan.

Ini karena mesin rumus melakukan pengurutan dan peringkat dengan membandingkan nilai numerik; namun, NaN tidak dapat dibandingkan dengan angka lain di 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 Layanan Analisis dan Mode DirectQuery

Secara umum, rumus DAX yang Anda buat di Power Pivot sepenuhnya kompatibel dengan model tabel Layanan Analisis. Namun, jika Anda memigrasikan model Power Pivot ke instans Layanan Analisis, lalu menyebarkan model dalam mode DirectQuery, ada beberapa keterbatasan.

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

Untuk informasi selengkapnya, lihat dokumentasi pemodelan tabel Layanan Analisis di SQL Server 2012 BooksOnline.