Bagian ini menyediakan tautan ke contoh yang menunjukkan penggunaan rumus DAX dalam skenario berikut.
- Melakukan penghitungan yang kompleks
- Bekerja dengan teks dan tanggal
- Nilai bersyarat dan pengujian kesalahan
- Menggunakan kecerdasan waktu
- Memberi peringkat dan membandingkan nilai
Di artikel ini
Memulai
Kunjungi Wiki Pusat Sumber Daya DAX di mana Anda dapat menemukan segala macam informasi tentang DAX termasuk blog, sampel, laporan resmi, dan video yang disediakan oleh para profesional terkemuka di industri dan Microsoft.
Skenario: Melakukan penghitungan kompleks
Rumus DAX dapat melakukan penghitungan kompleks yang melibatkan agregasi kustom, pemfilteran, dan penggunaan nilai bersyarat. Bagian ini memberikan contoh cara memulai penghitungan kustom.
Membuat penghitungan kustom untuk PivotTable
CALCULATE dan CALCULATETABLE adalah fungsi canggih dan fleksibel yang berguna untuk menentukan bidang terhitung. Fungsi ini memungkinkan Anda mengubah konteks di mana penghitungan akan dilakukan. Anda juga dapat menyesuaikan jenis operasi agregasi atau matematika yang akan dilakukan. Lihat topik berikut ini sebagai contoh.
Menerapkan filter ke rumus
Di sebagian besar tempat di mana fungsi DAX mengambil tabel sebagai argumen, Anda biasanya dapat meneruskan tabel yang difilter, baik dengan menggunakan fungsi FILTER sebagai ganti nama tabel, maupun dengan menentukan ekspresi filter sebagai salah satu argumen fungsi. Topik berikut memberikan contoh tentang cara membuat filter dan bagaimana filter memengaruhi hasil rumus. Untuk informasi selengkapnya, lihat Memfilter Data dalam Rumus DAX.
Fungsi FILTER memungkinkan Anda menentukan kriteria filter menggunakan ekspresi, sementara fungsi lainnya dirancang khusus untuk memfilter nilai kosong.
Hapus filter secara selektif untuk membuat rasio dinamis
Dengan membuat filter dinamis dalam rumus, Anda dapat dengan mudah menjawab pertanyaan seperti berikut:
- Apa kontribusi penjualan produk saat ini terhadap total penjualan tahun ini?
- Berapa banyak divisi ini berkontribusi terhadap total laba untuk semua tahun operasional, dibandingkan dengan divisi lainnya?
Rumus yang Anda gunakan dalam PivotTable dapat dipengaruhi oleh konteks PivotTable, tetapi Anda dapat mengubah konteks secara selektif dengan menambahkan atau menghapus filter. Contoh dalam topik SEMUA menunjukkan cara melakukannya. Untuk menemukan rasio penjualan reseller tertentu atas penjualan untuk semua reseller, Anda membuat pengukuran yang menghitung nilai untuk konteks saat ini dibagi dengan nilai untuk konteks ALL.
Topik ALLEXCEPT memberikan contoh cara menghapus filter pada rumus secara selektif. Kedua contoh tersebut memandu Anda melalui bagaimana hasil berubah tergantung pada desain PivotTable.
Untuk contoh lain tentang cara menghitung rasio dan persentase, lihat topik berikut:
Menggunakan nilai dari loop luar
Selain menggunakan nilai dari konteks saat ini dalam penghitungan, DAX dapat menggunakan nilai dari loop sebelumnya dalam membuat sekumpulan penghitungan terkait. Topik berikut memberikan panduan tentang cara membuat rumus yang mereferensikan nilai dari loop luar. Fungsi EARLIER mendukung hingga dua tingkat loop bertumpuk.
Untuk mempelajari selengkapnya tentang konteks baris dan tabel terkait, serta cara menggunakan konsep ini dalam rumus, lihat Konteks dalam Rumus DAX.
Skenario: Bekerja dengan Teks dan Tanggal
Bagian ini menyediakan tautan ke topik referensi DAX yang berisi contoh skenario umum yang melibatkan bekerja dengan teks, mengekstrak dan menulis nilai tanggal dan waktu, atau membuat nilai berdasarkan suatu kondisi.
Membuat kolom utama dengan penggabungan
Power Pivot tidak mengizinkan tombol komposit; Oleh karena itu, jika memiliki kunci komposit di sumber data, Anda mungkin perlu menggabungkannya menjadi satu kolom kunci. Topik berikut memberikan salah satu contoh cara membuat kolom terhitung berdasarkan kunci komposit.
Compose tanggal berdasarkan bagian tanggal yang diekstrak dari tanggal teks
Power Pivot menggunakan tipe data tanggal/waktu SQL Server untuk bekerja dengan tanggal; oleh karena itu, jika data eksternal Anda berisi tanggal yang diformat secara berbeda -- misalnya, jika tanggal Anda ditulis dalam format tanggal regional yang tidak dikenali oleh mesin data Power Pivot, atau jika data Anda menggunakan kunci pengganti bilangan bulat -- Anda mungkin perlu menggunakan rumus DAX untuk mengekstrak bagian tanggal lalu menyusun bagian-bagian tersebut menjadi tanggal/ representasi waktu.
Misalnya, jika Anda memiliki kolom tanggal yang telah direpresentasikan sebagai bilangan bulat lalu diimpor sebagai string teks, Anda dapat mengonversi string tersebut menjadi nilai tanggal/waktu dengan menggunakan rumus berikut:
=TANGGAL(KANAN([nilai1],4),KIRI([nilai1],2),TENGAH([nilai1],2))
| Value1 | Hasil |
|---|---|
| 01032009 | 1/3/2009 |
| 12132008 | 12/13/2008 |
| 06252007 | 6/25/2007 |
Topik berikut menyediakan informasi selengkapnya tentang fungsi yang digunakan untuk mengekstrak dan menulis tanggal.
Menentukan format tanggal atau angka kustom
Jika data Anda berisi tanggal atau angka yang tidak direpresentasikan dalam salah satu format teks standar Windows, Anda dapat menentukan format kustom untuk memastikan bahwa nilai ditangani dengan benar. Format ini digunakan saat mengonversi nilai ke string, atau dari string. Topik berikut juga menyediakan daftar mendetail tentang format yang telah ditentukan sebelumnya yang tersedia untuk bekerja dengan tanggal dan angka.
- Format numerik yang telah ditentukan sebelumnya untuk fungsi FORMAT
- Format numerik kustom untuk fungsi FORMAT
- Format Tanggal dan Waktu yang telah ditentukan sebelumnya untuk Fungsi FORMAT
- Format Tanggal dan Waktu Kustom untuk Fungsi FORMAT
Mengubah tipe data menggunakan rumus
Di Power Pivot, tipe data output ditentukan oleh kolom sumber, dan Anda tidak dapat menentukan tipe data hasil secara eksplisit, karena tipe data optimal ditentukan oleh Power Pivot. Namun, Anda dapat menggunakan konversi tipe data implisit yang dilakukan oleh Power Pivot untuk memanipulasi tipe data output.
- Untuk mengonversi tanggal atau string angka menjadi angka, kalikan dengan 1,0. Misalnya, rumus berikut menghitung tanggal saat ini dikurangi 3 hari, lalu menampilkan nilai bilangan bulat yang sesuai.
=(HARI INI()-3)*1,0 - Untuk mengonversi tanggal, angka, atau nilai mata uang menjadi string, gabungkan nilai tersebut dengan string kosong. Misalnya, rumus berikut mengembalikan tanggal saat ini sebagai string.
=""& TODAY()
Fungsi berikut juga dapat digunakan untuk memastikan bahwa tipe data tertentu dikembalikan:
Mengonversi bilangan real menjadi bilangan bulat
- Fungsi ROUND
- Fungsi CEILING
-
Fungsi FLOOR
Mengonversi angka real, bilangan bulat, atau tanggal menjadi string - Fungsi FIXED
-
Fungsi FORMAT
Mengonversi string menjadi angka nyata atau tanggal - Fungsi VALUE
- Fungsi DATEVALUE
- Fungsi TIMEVALUE
Skenario: Nilai Bersyarat dan Pengujian Kesalahan
Seperti Excel, DAX memiliki fungsi yang memungkinkan Anda menguji nilai dalam data dan mengembalikan nilai yang berbeda berdasarkan suatu kondisi. Misalnya, Anda dapat membuat kolom terhitung yang memberi label pengecer sebagai Pilihan atau Nilai tergantung pada jumlah penjualan tahunan. Fungsi yang menguji nilai juga berguna untuk memeriksa rentang atau jenis nilai, untuk mencegah kesalahan data yang tidak terduga agar tidak merusak penghitungan.
Membuat nilai berdasarkan kondisi
Anda dapat menggunakan kondisi IF bertumpuk untuk menguji nilai dan membuat nilai baru secara kondisional. Topik berikut berisi beberapa contoh sederhana pemrosesan bersyarat dan nilai bersyarat:
Menguji kesalahan dalam rumus
Tidak seperti Excel, Anda tidak dapat memiliki nilai yang valid dalam satu baris kolom terhitung dan nilai yang tidak valid di baris lain. Artinya, jika ada kesalahan di bagian mana pun dari kolom Power Pivot, seluruh kolom ditandai dengan kesalahan, sehingga Anda harus selalu mengoreksi kesalahan rumus yang menghasilkan nilai yang tidak valid.
Misalnya, jika membuat rumus yang membagi dengan nol, Anda mungkin mendapatkan hasil tak terhingga atau kesalahan. Beberapa rumus juga akan gagal jika fungsi menemukan nilai kosong saat mengharapkan nilai numerik. Saat Anda mengembangkan model data, yang terbaik adalah membiarkan kesalahan muncul sehingga Anda dapat mengklik pesan dan memecahkan masalah. Namun, ketika menerbitkan buku kerja, Anda harus menyertakan penanganan kesalahan untuk mencegah nilai yang tidak terduga menyebabkan penghitungan gagal.
Untuk menghindari mengembalikan kesalahan dalam kolom terhitung, Anda menggunakan kombinasi fungsi logika dan informasi untuk menguji kesalahan dan selalu mengembalikan nilai yang valid. Topik berikut memberikan beberapa contoh sederhana tentang cara melakukannya di DAX:
Skenario: Menggunakan Kecerdasan Waktu
Fungsi kecerdasan waktu DAX menyertakan fungsi untuk membantu Anda mengambil tanggal atau rentang tanggal dari data Anda. Anda kemudian dapat menggunakan tanggal atau rentang tanggal tersebut untuk menghitung nilai dalam periode yang sama. Fungsi kecerdasan waktu juga menyertakan fungsi yang bekerja dengan interval tanggal standar, untuk memungkinkan Anda membandingkan nilai di seluruh bulan, tahun, atau kuartal. Anda juga dapat membuat rumus yang membandingkan nilai untuk tanggal pertama dan terakhir dari periode tertentu.
Untuk daftar semua fungsi kecerdasan waktu, lihat Fungsi Kecerdasan Waktu (DAX). Untuk tips tentang cara menggunakan tanggal dan waktu secara efektif dalam analisis Power Pivot, lihat Tanggal di Power Pivot.
Menghitung penjualan kumulatif
Topik berikut berisi contoh cara menghitung saldo penutupan dan pembukaan. Contoh ini memungkinkan Anda membuat saldo berjalan dalam interval yang berbeda seperti hari, bulan, kuartal, atau tahun.
- Fungsi CLOSINGBALANCEMONTH, fungsi CLOSINGBALANCEQUARTER, fungsi CLOSINGBALANCEYEAR
- Fungsi OPENINGBALANCEMONTH, fungsi OPENINGBALANCEQUARTER, fungsi OPENINGBALANCEYEAR
Membandingkan nilai dari waktu ke waktu
Topik berikut berisi contoh cara membandingkan jumlah di berbagai periode. Periode waktu default yang didukung oleh DAX adalah bulan, kuartal, dan tahun.
- Fungsi PREVIOUSMONTH, PREVIOUSQUARTER, Fungsi PREVIOUSYEAR
- Fungsi TOTALMTD, fungsi TOTALQTD, fungsi TOTALYTD
- Fungsi PARALLELPERIOD
Menghitung nilai selama rentang tanggal kustom
Lihat topik berikut untuk contoh cara mengambil rentang tanggal kustom, seperti 15 hari pertama setelah dimulainya promosi penjualan.
Jika menggunakan fungsi kecerdasan waktu untuk mengambil kumpulan tanggal kustom, Anda dapat menggunakan kumpulan tanggal tersebut sebagai input ke fungsi yang melakukan penghitungan, untuk membuat agregat kustom di seluruh periode waktu. Lihat topik berikut untuk contoh cara melakukannya:
-
Catatan
Jika Anda tidak perlu menentukan rentang tanggal kustom, tetapi bekerja dengan unit akuntansi standar seperti bulan, kuartal, atau tahun, kami menyarankan Anda melakukan penghitungan menggunakan fungsi kecerdasan waktu yang dirancang untuk tujuan ini, seperti TOTALQTD, TOTALMTD, TOTALQTD, dll.
Skenario: Peringkat dan Membandingkan Nilai
Untuk hanya memperlihatkan n item teratas dalam kolom atau PivotTable, Anda memiliki beberapa opsi:
- Anda dapat menggunakan fitur di Excel untuk membuat filter Teratas. Anda juga dapat memilih sejumlah nilai atas atau bawah dalam PivotTable. Bagian pertama dari bagian ini menjelaskan cara memfilter 10 item teratas dalam PivotTable. Untuk informasi selengkapnya, lihat dokumentasi Excel.
- Anda dapat membuat rumus yang memberi peringkat nilai secara dinamis, lalu memfilter menurut nilai peringkat, atau menggunakan nilai peringkat sebagai Pemotong. Bagian kedua dari bagian ini menjelaskan cara membuat rumus ini lalu menggunakan peringkat tersebut di Pemotong.
Ada kelebihan dan kekurangan untuk setiap metode.
- Filter Excel Top mudah digunakan, tetapi filternya hanya untuk tujuan tampilan. Jika data yang mendasari PivotTable berubah, Anda harus me-refresh PivotTable secara manual untuk melihat perubahannya. Jika perlu bekerja dengan peringkat secara dinamis, Anda dapat menggunakan DAX untuk membuat rumus yang membandingkan nilai dengan nilai lain dalam kolom.
- Rumus DAX lebih kuat; selain itu, dengan menambahkan nilai peringkat ke Slicer, Anda cukup mengklik Slicer untuk mengubah jumlah nilai teratas yang ditampilkan. Namun, perhitungannya mahal secara komputasi dan metode ini mungkin tidak cocok untuk tabel dengan banyak baris.
Hanya tampilkan sepuluh item teratas dalam PivotTable
Untuk memperlihatkan nilai atas atau bawah dalam PivotTable
|
|---|
Urutkan item secara dinamis menggunakan rumus
Topik berikut berisi contoh cara menggunakan DAX untuk membuat peringkat yang disimpan dalam kolom terhitung. Karena rumus DAX dihitung secara dinamis, Anda selalu dapat yakin bahwa peringkatnya benar meskipun data dasar telah berubah. Selain itu, karena rumus digunakan dalam kolom terhitung, Anda dapat menggunakan peringkat dalam Pemotong lalu memilih nilai 5, 10 teratas, atau bahkan 100 teratas.