Data Filter dalam Rumus DAX

Berlaku Untuk
Excel untuk Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

Bagian ini menjelaskan cara membuat filter dalam rumus Ekspresi Analisis Data (DAX). Anda dapat membuat filter dalam rumus, untuk membatasi nilai dari data sumber yang digunakan dalam penghitungan. Lakukan dengan menentukan tabel sebagai input rumus, lalu menentukan ekspresi filter. Ekspresi filter yang Anda berikan digunakan untuk mengkueri data dan hanya mengembalikan sebagian data sumber. Filter diterapkan secara dinamis setiap kali Anda memperbarui hasil rumus, tergantung pada konteks data Anda saat ini.

Di artikel ini

Membuat Filter pada Tabel yang digunakan dalam Rumus

Anda dapat menerapkan filter dalam rumus yang mengambil tabel sebagai input. Alih-alih memasukkan nama tabel, Anda menggunakan fungsi FILTER untuk menentukan subset baris dari tabel yang ditentukan. Subset tersebut kemudian diteruskan ke fungsi lain, untuk operasi seperti agregasi kustom.

Sebagai contoh, misalnya Anda memiliki tabel data yang berisi informasi pesanan tentang reseller, dan ingin menghitung berapa banyak yang dijual setiap reseller. Namun, Anda ingin menampilkan jumlah penjualan hanya untuk penjual yang menjual beberapa unit produk Anda yang bernilai lebih tinggi. Rumus berikut, berdasarkan buku kerja sampel DAX, memperlihatkan salah satu contoh cara membuat penghitungan ini dengan menggunakan filter:

=SUMX(
     FILTER ('ResellerSales_USD', 'ResellerSales_USD'[Kuantitas] > 5 &&
     'ResellerSales_USD'[ProductStandardCost_USD] > 100),
     'ResellerSales_USD'[SalesAmt]
     )

  • Bagian pertama dari rumus menentukan salah satu fungsi agregasi Power Pivot, yang mengambil tabel sebagai argumen. SUMX menghitung jumlah di atas tabel.

  • Bagian kedua dari rumus, FILTER(table, expression),memberi tahu SUMX data mana yang akan digunakan. SUMX memerlukan tabel atau ekspresi yang menghasilkan tabel. Di sini, alih-alih menggunakan semua data dalam tabel, Anda menggunakan fungsi untuk FILTER menentukan baris mana dari tabel yang digunakan.
    Ekspresi filter terdiri dari dua bagian: bagian pertama memberi nama tabel yang menerapkan filter. Bagian kedua mendefinisikan ekspresi yang akan digunakan sebagai kondisi filter. Dalam hal ini, Anda memfilter pada penjual yang menjual lebih dari 5 unit dan produk yang harganya lebih dari $100. Operator, &&, adalah operator AND logis, yang menunjukkan bahwa kedua bagian kondisi harus benar agar baris termasuk dalam subset yang difilter.

  • Bagian ketiga dari rumus memberi tahu SUMX fungsi nilai mana yang harus dijumlahkan. Dalam hal ini, Anda hanya menggunakan jumlah penjualan.
    Perhatikan bahwa fungsi seperti FILTER, yang mengembalikan tabel, tidak pernah mengembalikan tabel atau baris secara langsung, tetapi selalu disematkan dalam fungsi lain. Untuk informasi selengkapnya tentang FILTER dan fungsi lain yang digunakan untuk pemfilteran, termasuk contoh lainnya, lihat Fungsi Filter (DAX).

    Catatan

    Ekspresi filter dipengaruhi oleh konteks penggunaannya. Misalnya, jika Anda menggunakan filter dalam pengukuran, dan pengukuran digunakan dalam PivotTable atau PivotChart, subset data yang dikembalikan mungkin terpengaruh oleh filter atau Pemotong tambahan yang telah diterapkan pengguna dalam PivotTable. Untuk informasi selengkapnya tentang konteks, lihat Konteks dalam Rumus DAX.

Filter yang Menghapus Duplikat

Selain memfilter untuk nilai tertentu, Anda dapat mengembalikan kumpulan nilai unik dari tabel atau kolom lain. Hal ini dapat membantu jika Anda ingin menghitung jumlah nilai unik dalam kolom, atau menggunakan daftar nilai unik untuk operasi lainnya. DAX menyediakan dua fungsi untuk mengembalikan nilai yang berbeda: Fungsi DISTINCT dan Fungsi VALUES.

  • Fungsi DISTINCT memeriksa satu kolom yang Anda tentukan sebagai argumen fungsi, dan mengembalikan kolom baru yang hanya berisi nilai yang berbeda.
  • Fungsi VALUES juga mengembalikan daftar nilai unik, tetapi juga mengembalikan anggota Tidak Diketahui. Hal ini berguna ketika Anda menggunakan nilai dari dua tabel yang digabungkan oleh suatu hubungan, dan nilai hilang di satu tabel dan ada di tabel lainnya. Untuk informasi selengkapnya tentang anggota Tidak Diketahui, lihat Konteks dalam Rumus DAX.

Kedua fungsi ini mengembalikan seluruh kolom nilai; Oleh karena itu, Anda menggunakan fungsi untuk mendapatkan daftar nilai yang kemudian diteruskan ke fungsi lain. Misalnya, Anda dapat menggunakan rumus berikut untuk mendapatkan daftar produk berbeda yang dijual oleh penjual tertentu, menggunakan kunci produk unik, lalu menghitung produk dalam daftar tersebut menggunakan fungsi COUNTROWS:

=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))

Atas Halaman

Bagaimana konteks memengaruhi filter

Saat Anda menambahkan rumus DAX ke PivotTable atau PivotChart, hasil rumus dapat dipengaruhi oleh konteks. Jika Anda bekerja dalam tabel Power Pivot, konteksnya adalah baris saat ini dan nilainya. Jika Anda bekerja dalam PivotTable atau PivotChart, konteks berarti kumpulan atau subset data yang ditentukan oleh operasi seperti pengiringan, atau pemfilteran. Desain PivotTable atau PivotChart juga memaksakan konteksnya sendiri. Misalnya, jika Anda membuat PivotTable yang mengelompokkan penjualan berdasarkan wilayah dan tahun, hanya data yang berlaku untuk wilayah dan tahun tersebut yang muncul di PivotTable. Oleh karena itu, pengukuran apa pun yang Anda tambahkan ke PivotTable dihitung dalam konteks judul kolom dan baris, ditambah filter apa pun dalam rumus pengukuran.

Untuk informasi selengkapnya, lihat Konteks dalam Rumus DAX.

Atas Halaman

Menghapus filter

Saat bekerja dengan rumus kompleks, Anda mungkin ingin mengetahui dengan tepat apa filter saat ini, atau Anda mungkin ingin mengubah bagian filter dari rumus tersebut. DAX menyediakan beberapa fungsi yang memungkinkan Anda menghapus filter, dan mengontrol kolom mana yang dipertahankan sebagai bagian dari konteks filter saat ini. Bagian ini memberikan gambaran umum tentang pengaruh fungsi ini terhadap hasil dalam rumus.

Menimpa Semua Filter dengan Fungsi ALL

Anda dapat menggunakan fungsi ini ALL untuk menimpa filter yang sebelumnya diterapkan, dan mengembalikan semua baris dalam tabel ke fungsi yang melakukan agregat atau operasi lainnya. Jika Anda menggunakan satu atau beberapa kolom, bukan tabel, sebagai argumen untuk , fungsi ini ALL mengembalikan ALLsemua baris, mengabaikan filter konteks apa pun.

Catatan

Jika Anda terbiasa dengan terminologi database relasional, Anda dapat menganggap ALL sebagai menghasilkan gabungan kiri luar alami dari semua tabel.

Sebagai contoh, misalnya Anda memiliki tabel, Penjualan dan Produk, dan ingin membuat rumus yang akan menghitung jumlah penjualan untuk produk saat ini dibagi dengan penjualan untuk semua produk. Anda harus mempertimbangkan fakta bahwa, jika rumus digunakan dalam suatu pengukuran, pengguna PivotTable mungkin menggunakan Pemotong untuk memfilter produk tertentu, dengan nama produk pada baris. Oleh karena itu, untuk mendapatkan nilai sebenarnya dari penyebut terlepas dari filter atau Pemotong apa pun, Anda harus menambahkan fungsi ALL untuk menimpa filter apa pun. Rumus berikut adalah salah satu contoh cara menggunakan ALL untuk menimpa efek filter sebelumnya:

=SUM (Sales[Amount])/SUMX(Sales[Amount], FILTER(Sales, ALL(Products)))

  • Bagian pertama dari rumus, SUM (Sales[Amount]), menghitung pembilang.
  • Penjumlahan memperhitungkan konteks saat ini, yang berarti bahwa jika Anda menambahkan rumus ke dalam kolom terhitung, konteks baris akan diterapkan, dan jika Anda menambahkan rumus ke dalam PivotTable sebagai pengukuran, semua filter yang diterapkan dalam PivotTable (konteks filter) akan diterapkan.
  • Bagian kedua dari rumus, menghitung penyebutnya. Fungsi ALL menimpa filter apa pun yang mungkin diterapkan ke Products tabel.

Untuk informasi selengkapnya, termasuk contoh mendetail, lihat Fungsi ALL.

Menimpa Filter Tertentu dengan Fungsi ALLEXCEPT

Fungsi ALLEXCEPT juga menimpa filter yang ada, tetapi Anda dapat menentukan bahwa beberapa filter yang ada harus dipertahankan. Kolom yang Anda beri nama sebagai argumen untuk fungsi ALLEXCEPT menentukan kolom mana yang akan terus difilter. Jika Anda ingin menimpa filter dari sebagian besar kolom tetapi tidak semua, ALLEXCEPT lebih nyaman daripada ALL. Fungsi ALLEXCEPT sangat berguna saat Anda membuat PivotTable yang mungkin difilter pada kolom yang berbeda, dan Anda ingin mengontrol nilai yang digunakan dalam rumus. Untuk informasi selengkapnya, termasuk contoh mendetail tentang cara menggunakan ALLEXCEPT dalam PivotTable, lihat Fungsi ALLEXCEPT.

Atas Halaman