Data Filter dalam Rumus DAX

Anda dapat membuat filter dalam rumus, untuk membatasi nilai dari data sumber yang digunakan dalam penghitungan. Anda dapat melakukan hal ini dengan menentukan sebuah tabel sebagai input bagi rumus, lalu menetapkan sebuah ekspresi filter. Ekspresi filter yang Anda berikan digunakan untuk kueri data dan hanya mengembalikan subset data sumber. Filter diterapkan secara dinamis setiap kali Anda memperbarui hasil rumus, bergantung pada konteks data Anda saat ini. Bagian ini menguraikan cara membuat filter dalam rumus Ekspresi Analisis Data (Data Analysis Expressions/DAX).

Di artikel ini


Membuat Filter pada Tabel yang digunakan dalam Rumus

Anda dapat menerapkan filter dalam rumus yang menggunakan tabel sebagai inputnya. Sebagai ganti memasukkan nama tabel, Anda menggunakan fungsi FILTER untuk menetapkan subset baris dari tabel yang ditentukan. Subset itu lalu disalurkan ke fungsi lain, untuk operasi-operasi seperti agregasi kustom.

Misalnya, jika Anda memiliki tabel data berisi informasi pesanan tentang penjual, dan Anda ingin menghitung penjualan oleh masing-masing penjual. Namun, Anda ingin memperlihatkan jumlah penjualan hanya untuk penjual yang menjual sekian unit produk bernilai tinggi. Rumus berikut, berdasarkan contoh buku kerja DAX, memperlihatkan satu contoh tentang cara membuat penghitungan ini dengan menggunakan filter:

        =
        SUMX(
        
        
        
          
         FILTER ('
ResellerSales_USD ', ' ResellerSales_USD '[Quantity] > 5 &&
         '
ResellerSales_USD '[ ProductStandardCost_USD ] > 100),
         '
ResellerSales_USD '[ SalesAmt ]
         )
  • Bagian pertama dari rumus tersebut menentukan salah satu fungsi agregasi Power Pivot, yang menggunakan sebuah tabel sebagai argumen. SUMX menghitung jumlah dari sebuah tabel.
  • Bagian kedua dari rumus, FILTER(table, expression),memberitahu SUMXdata yang digunakan. SUMXmemerlukan sebuah tabel atau ekspresi yang menghasilkan tabel. Di sini, sebagai ganti menggunakan semua data dalam tabel, Anda menggunakan FILTERfungsi untuk menentukan baris dari tabel yang akan digunakan.

Ekspresi filter memiliki dua bagian: bagian pertama menyebutkan tabel yang akan difilter. Bagian kedua menetapkan ekspresi yang akan digunakan sebagai syarat filter. Dalam hal ini, Anda memfilter penjual yang telah menjual lebih dari 5 unit dan produk yang harganya lebih dari $100. Operator, &&, adalah operator AND logis, yang menunjukkan bahwa kedua bagian syarat harus benar untuk baris yang termasuk dalam subset yang telah difilter.

  • Bagian ketiga dari rumus memberitahu fungsi SUMX nilai mana yang harus dijumlahkan. Dalam hal ini, Anda menggunakan jumlah penjualan saja.

Perhatikan bahwa fungsi seperti FILTER, yang mengembalikan sebuah tabel, tidak akan mengembalikan tabel atau baris langsung, tapi selalu disematkan dalam fungsi lain. Untuk informasi selengkapnya tentang FILTER dan fungsi lainnya yang digunakan untuk memfilter, termasuk contoh lainnya, lihat Fungsi Filter (DAX).

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

Filter yang Menghapus Duplikat

Selain untuk memfilter nilai tertentu, Anda dapat mengembalikan seperangkat nilai unik dari tabel atau kolom lain. Ini dapat berguna ketika Anda ingin menghitung jumlah nilai unik dalam sebuah kolom, atau menggunakan daftar nilai unik untuk operasi lain. DAX menyediakan dua fungsi untuk mengembalikan nilai berlainan: Fungsi DISTINCT dan Fungsi VALUES.

  • Fungsi DISTINCT memeriksa satu kolom yang Anda tentukan sebagai argumen untuk fungsi tersebut dan mengembalikan kolom baru yang hanya berisi nilai berlainan.
  • Fungsi VALUES juga mengembalikan daftar nilai unik, tetapi juga mengembalikan Anggota tak dikenal. Ini berguna ketika Anda menggunakan nilai-nilai dari dua tabel yang disatukan oleh sebuah hubungan, serta nilai yang hilang di satu tabel dan yang ada di tabel lain. Untuk informasi selengkapnya tentang Anggota tak dikenal, lihat Konteks dalam Rumus DAX.

Kedua fungsi ini mengembalikan kolom lengkap berisi nilai; oleh karena itu, Anda menggunakan fungsi ini untuk mendapatkan daftar nilai yang akan disalurkan ke fungsi lain. Misalnya, Anda dapat menggunakan rumus berikut untuk mendapatkan daftar produk berlainan yang dijual oleh penjual tertentu, melalui kunci produk unik, lalu menghitung produk dalam daftar itu menggunakan fungsi COUNTROWS:

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

Atas Halaman Atas Halaman

Pengaruh Konteks Terhadap Filter

Ketika Anda menambahkan sebuah rumus DAX ke PivotTable atau PivotChart, hasil rumus tersebut dapat dipengaruhi oleh konteks. Jika Anda sedang menggunakan tabel Power Pivot, konteksnya adalah baris saat ini dan nilainya. Jika Anda sedang menggunakan PivotTable atau PivotChart, konteksnya adalah set atau subset data yang ditetapkan oleh berbagai operasi seperti pemotongan atau pemfilteran. Desain PivotTable atau PivotChart juga menerapkan konteksnya sendiri. Misalnya, jika Anda membuat sebuah PivotTable yang mengelompokkan penjualan menurut wilayah dan tahun, maka hanya data yang berlaku untuk wilayah dan tahun itu yang muncul dalam PivotTable. Oleh karena itu, bidang terhitung yang Anda tambahkan ke PivotTable akan dihitung dalam konteks judul kolom dan baris, ditambah filter yang ada dalam rumus bidang terhitung tersebut.

Untuk informasi selengkapnya, lihat Konteks dalam Rumus DAX.

Atas Halaman Atas Halaman

Menghapus Filter

Ketika bekerja dengan rumus yang kompleks, Anda mungkin perlu tahu secara persis filter yang digunakan, atau Anda mungkin perlu memodifikasi bagian filter untuk rumus tersebut. DAX menyediakan sejumlah fungsi yang memungkinkan Anda menghapus filter, dan mengontrol kolom yang dipertahankan sebagai bagian dari konteks filter saat ini. Bagian ini memberikan gambaran umum tentang pengaruh fungsi-fungsi ini pada hasil dalam rumus.

Mengganti Semua Filter dengan Fungsi ALL

Anda dapat menggunakan fungsi ALLuntuk menggantikan semua filter yang telah diterapkan sebelumnya, dan mengembalikan semua baris dalam tabel ke fungsi yang melakukan agregat atau operasi lainnya. jika Anda menggunakan satu atau beberapa kolom, sebagai ganti tabel, sebagai argumen ke ALL, fungsi ALL akan mengembalikan semua baris, dengan mengabaikan filter konteks.

 Catatan    Jika Anda memahami terminologi database relasional, Anda dapat menganggap ALL sebagai pembuatan gabungan luar alamiah atas semua tabel.

Misalnya, anggaplah Anda memiliki tabel, Penjualan dan Produk, dan Anda ingin membuat rumus yang menghitung jumlah penjualan untuk produk tertentu dibagi dengan penjualan untuk semua produk. Anda harus mempertimbangkan fakta bahwa, jika rumus digunakan dalam bidang terhitung, pengguna PivotTable mungkin menggunakan Pemotong untuk memfilter produk tertentu, dengan nama produk pada barisnya. Oleh karena itu, untuk mendapatkan nilai yang benar atas penyebut dengan mengabaikan setiap filter atau Pemotong, Anda harus menambahkan fungsi ALL untuk mengesampingkan filter. Rumus berikut adalah satu contoh tentang 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 mempertimbangkan konteks saat ini, maksudnya, jika Anda menambahkan rumus ke dalam kolom terhitung, maka konteks baris akan diterapkan, dan jika Anda menambahkan rumus ke PivotTable sebagai bidang terhitung, maka setiap filter yang diterapkan dalam PivotTable (konteks filter) akan diterapkan.
  • Bagian kedua dari rumus, menghitung penyebut. Fungsi ALL menggantikan filter yang mungkin telah diterapkan pada tabel Products.

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

Menggantikan Filter Tertentu dengan Fungsi ALLEXCEPT

Fungsi ALLEXCEPT juga mengganti filter yang ada, tetapi Anda dapat menentukan agar sebagian filter yang ada dipertahankan. Kolom yang Anda tentukan sebagai argumen untuk fungsi ALLEXCEPT akan menentukan kolom yang tetap difilter. Jika Anda ingin mengganti filter dari sebagian besar kolom, tetapi tidak semuanya, maka ALLEXCEPT lebih praktis daripada ALL. Fungsi ALLEXCEPT khususnya berguna ketika Anda membuat PivotTable yang mungkin difilter pada beragam kolom, dan Anda ingin mengontrol nilai-nilai yang digunakan dalam rumus. Untuk informasi selengkapnya, termasuk contoh detail tentang cara menggunakan ALLEXCEPT dalam PivotTable, lihat Fungsi ALLEXCEPT.

Atas Halaman Atas Halaman

 
 
Berlaku pada:
Excel 2013, Power Pivot in Excel 2013