Menguasai COUNTIF() di Power BI: Pendekatan Berbasis DAX

Fungsi COUNTIF() populer di kalangan pengguna Excel karena memungkinkan mereka menghitung data berdasarkan suatu kondisi. Namun, saat beralih ke Power BI, banyak pengguna menyadari bahwa Power BI tidak memiliki fungsi COUNTIF() bawaan.

Untungnya, pengguna dapat mereplikasi fungsionalitas COUNTIF() menggunakan berbagai rumus DAX (Data Analysis Expressions). Artikel ini akan memandu Anda untuk mencapai perhitungan Power BI ala COUNTIF() menggunakan fungsi DAX seperti CALCULATE(), COUNTROWS(), COUNTA(), dan FILTER().

Mengapa COUNTIF() Tidak Ada di Power BI

Berbeda dengan Excel yang berbasis rumus per sel, Power BI dibangun dengan perhitungan berbasis tabel dan filter context. Power BI beroperasi pada tabel dan relasinya, menggunakan DAX untuk perhitungan. 

Alih-alih merujuk sel individual, DAX bekerja dengan kolom dan secara dinamis menerapkan perhitungan berdasarkan filter context—yang ditentukan oleh slicer, filter baris/kolom, dan relasi tabel. 

Excel COUNTIF() menggunakan pendekatan berbasis sel, di mana rumus diterapkan baris demi baris dalam rentang tertentu. Pendekatan Power BI berbeda karena menggunakan relasi antar tabel untuk menyempurnakan perhitungan. Alih-alih memindai sel individual, Power BI menerapkan filter berdasarkan konteks laporan menggunakan DAX. 

Implikasi bagi pengguna

Sebagai pengguna Power BI yang berasal dari Excel, Anda perlu beradaptasi dengan pendekatan perhitungan berbasis tabel di Power BI. Pendekatan DAX, yang beroperasi pada kolom data, lebih efisien dibanding iterasi baris demi baris. 

Berbeda dengan Excel, pengguna Power BI harus memilih metode replikasi COUNTIF() yang paling sesuai dengan model data mereka. Jadi, sebagai pengguna, Anda perlu melepaskan kebiasaan menerapkan rumus pada sel individual dan berpikir dalam kerangka tabel serta filter context.

Fungsi DAX Inti untuk Mereplikasi Logika COUNTIF()

Pendekatan Anda dalam mengimplementasikan COUNTIF() di Power BI bergantung pada faktor seperti relasi antar tabel, ada tidaknya nilai hilang pada data, ukuran data, dan sebagainya. Beberapa fungsi DAX lebih dioptimalkan untuk pemrosesan yang lebih cepat dan efisien.  

Tinjauan fungsi kunci

Fungsi DAX digunakan untuk membuat calculated column, measure, dan tabel. Berikut beberapa fungsi DAX yang akan membantu Anda mereplikasi fungsionalitas COUNTIF() di Power BI.

• CALCULATE(): Fungsi ini digunakan untuk memodifikasi filter context bagi suatu ekspresi dan memungkinkan penerapan filter baru atau memodifikasi filter yang ada secara dinamis. Misalnya, Anda dapat menggunakan fungsi ini untuk menghitung total penjualan untuk wilayah tertentu. 

• COUNTROWS(): Menghitung jumlah baris dalam sebuah tabel, dan dapat digabungkan dengan fungsi pemfilteran untuk menghitung baris tertentu. Contohnya menghitung pesanan penjualan di atas $1.000.

• COUNTA(): Menghitung nilai non-kosong dalam sebuah kolom dan berguna saat menangani data yang hilang. Misalnya, Anda dapat menghitung ID pelanggan yang tidak kosong untuk memastikan hanya catatan valid yang dihitung. 

• FILTER(): Biasanya digunakan bersama CALCULATE() dan COUNTROWS(), fungsi ini mengembalikan tabel berdasarkan suatu kondisi. Misalnya, Anda dapat mengembalikan tabel berisi pesanan penjualan yang melebihi $500.

• ALLEXCEPT(): Menghapus semua filter yang diterapkan kecuali yang ditentukan di fungsi; misalnya, Anda dapat mengembalikan tabel total penjualan per wilayah meski telah menerapkan slicer atau filter tabel pada data. Ini penting saat Anda melakukan agregasi di atas hierarki.

• RELATEDTABLE(): Mengembalikan tabel berdasarkan relasi yang ada. Contohnya menghitung jumlah pesanan penjualan untuk tiap pelanggan menggunakan relasi dari data pelanggan.

Cara kerjanya bersama

Karena COUNTROWS() menghitung jumlah baris dalam tabel, Anda bisa mengembalikan tabel dari data yang memenuhi kondisi menggunakan FILTER(). Lalu, gunakan fungsi CALCULATE() untuk memodifikasi filter context, memastikan COUNTROWS() hanya mempertimbangkan baris yang sesuai dengan kondisi. Berikut contoh sintaks DAX yang menunjukkan cara kerjanya.

Sales over 5 quantities = 
CALCULATE(
    COUNTROWS(Sales), 
    FILTER('Sales, 'Sales[Quantity] > 5)
)Didukung oleh

Fungsi CALCULATE() kurang lebih seperti menggabungkan dua ekspresi berbeda. Alih-alih menggunakan COUNTROWS() untuk menghitung semua data, fungsi CALCULATE() akan membatasinya pada filter context yang diturunkan menggunakan fungsi FILTER(). 

Contoh Dasar Padanan COUNTIF() di Power BI

Sebelum saya menunjukkan beberapa contoh cara mengimplementasikan COUNTIF() di Power BI, pastikan Anda memuat data penjualan supermarket. 

Contoh 1: Jumlah pesanan oleh pelanggan perempuan

Sintaks DAX di bawah ini menghitung jumlah pesanan oleh pelanggan perempuan.

Orders by female customers = 
CALCULATE(
    COUNTROWS(Sales), 
    FILTER(Sales, Sales[Gender] = "Female")
)Didukung oleh

Jumlah pesanan oleh pelanggan perempuan. Gambar oleh Penulis.

• FILTER(Sales, Sales[Gender] = "Female") memfilter pesanan penjualan oleh pelanggan perempuan.

• COUNTROWS(Sales), alih-alih menghitung semua data, menghitung tabel yang telah difilter karena pengaruh fungsi CALCULATE().

Contoh 2: Menghitung pesanan per kota

Berikut sintaks DAX untuk menghitung jumlah pesanan untuk setiap kota.

Total Orders by City = CALCULATE(COUNTA(Sales[City]), ALLEXCEPT(Sales, Sales[City]))Didukung oleh

Jumlah pesanan per kota. Gambar oleh Penulis.

• COUNTA(Sales[City]) menghitung jumlah nilai non-kosong di kolom City.

• ALLEXCEPT(Sales, Sales[City]) menghapus semua filter kecuali kolom City dari tabel Sales.

• CALCULATE() memastikan COUNTA() dihitung terpisah untuk setiap kota, bertindak seperti fungsi COUNTIF() di Excel.

Contoh 3: Metode alternatif menggunakan COUNTROWS

Alternatif lain adalah menggunakan sintaks DAX di bawah ini, yang memakai COUNTROWS() alih-alih COUNTA().

Total Orders by City = CALCULATE(COUNTROWS(Sales), ALLEXCEPT(Sales, Sales[City]))Didukung oleh

COUNTROWS() menghitung jumlah baris di tabel Sales terlepas dari ada tidaknya nilai kosong. 

Jika kolom City kosong, kedua pendekatan akan menghasilkan hasil yang sama. Namun, jika ada nilai hilang pada kolom City, COUNTA() mengecualikannya, sedangkan COUNTROWS() menyertakannya.

Gunakan fungsi COUNTROWS() ketika setiap baris merepresentasikan satu record, seperti pesanan penjualan. Namun jika data Anda memiliki beberapa entri tidak valid, Anda dapat menggunakan fungsi COUNTA() yang hanya menghitung record dengan nilai valid. 

Menggunakan Measure untuk Perhitungan Dinamis

Ada situasi di mana Anda harus membuat measure yang berubah secara dinamis berdasarkan filter tabel atau slicer. Bagian ini membahas cara mengimplementasikan COUNTIF() dalam skenario seperti itu.

Skenario: Menghitung pesanan bernilai tinggi

Berikut contoh ketika kita ingin mengetahui pesanan dengan total kuantitas lebih dari lima.

High Value Orders = COUNTROWS(FILTER(Sales, Sales[Quantity] > 5))Didukung oleh

Jumlah Pesanan Bernilai Tinggi. Gambar oleh Penulis.

• FILTER(Sales, Sales[Quantity] > 5) mengembalikan pesanan penjualan dengan kuantitas pesanan lebih dari lima.

• COUNTROWS()  menghitung baris pada tabel yang telah difilter dengan pesanan lebih dari lima.

Berbeda dengan calculated column yang, ketika dibuat, menambah penggunaan penyimpanan dan ukuran model, measure hanya dihitung saat diperlukan sehingga lebih efisien. Sintaks DAX untuk membuat calculated column akan seperti ini:

 High Value = IF(Sales[Quantity] > 5, 1, 0)Didukung oleh

Calculated column High Value. Gambar oleh Penulis.

Ini akan menyimpan nilai statis 1 atau 0 di setiap baris. Untuk menghitung pesanan bernilai tinggi, Anda harus membuat measure menggunakan SUM(Sales[High Value]). 

Bayangkan membuat calculated column untuk setiap filter. Ini akan meningkatkan penggunaan penyimpanan dan mengurangi fleksibilitas. 

Berikut tabel yang menunjukkan mengapa Anda sebaiknya mempertimbangkan penggunaan measure dibanding calculated column.

Teknik Lanjutan dan Multi-Kondisi

Excel COUNTIFS() mendukung banyak kondisi, yang juga dapat Anda replikasi di Power BI.

Kriteria ganda / logika COUNTIFS()

Berikut sintaks DAX untuk menghitung pesanan dengan kuantitas pesanan lebih dari lima, dibayar melalui kartu kredit. 

Quantity Above 5 and Credit Card = 
COUNTROWS(
    FILTER(
        Sales, 
        Sales[Quantity] > 5 && Sales[Payment] = "Credit card"
    )
)Didukung oleh

Jumlah pesanan dengan kuantitas di atas lima dan dibayar melalui kartu kredit. Gambar oleh Penulis.

Rumus di atas memfilter tabel Sales di mana kuantitas pesanan melebihi 5 dan metode pembayaran adalah kartu kredit. && merepresentasikan operator AND, memastikan hanya pesanan yang memenuhi kedua kondisi yang dihitung.

Ketika Anda hanya tertarik pada pesanan yang memenuhi salah satu kondisi, gunakan operator OR yang direpresentasikan oleh || di DAX.

Quantity Above 5 or Credit Card = 
COUNTROWS(
    FILTER(
        Sales, 
        Sales[Quantity] > 5 || Sales[Payment] = "Credit card"
    )
)Didukung oleh

Jumlah pesanan dengan kuantitas di atas lima atau dibayar melalui kartu kredit. Gambar oleh Penulis.

Menggunakan operator logika dan variabel

Sintaks DAX yang mereplikasi COUNTIF() kadang bisa menjadi kompleks. Menggunakan variabel membuatnya mudah dipahami dan memastikan kondisi dievaluasi dalam urutan yang diinginkan. Ini juga menghindari perhitungan berulang dengan menyimpan nilai sekali saja. 

Berikut contoh lain yang menunjukkan cara menghitung jumlah pesanan oleh pelanggan perempuan, yang dibayar melalui kartu kredit atau dengan kuantitas di atas lima, menggunakan variabel.

CreditCard Or Above 5 By Female = 
VAR FilteredSales = 
    FILTER(
        Sales,
        (Sales[Quantity] > 5 || Sales[Payment] = "Credit card") 
        && Sales[Gender] = "Female"
    )
RETURN 
    COUNTROWS(FilteredSales)Didukung oleh

Jumlah pesanan oleh pelanggan perempuan yang dibayar melalui kartu kredit atau dengan kuantitas di atas lima. Gambar oleh Penulis.

Filter disimpan dalam variabel yang digunakan pada fungsi COUNTROWS(). Berikut cara lain menulis ekspresi di atas

CreditCard Or Above 5 By Female = 
VAR MinQuantity = 5
VAR FilteredSales = 
    FILTER(
        Sales,
        (Sales[Quantity] > MinQuantity || Sales[Payment] = "Credit card") 
        && Sales[Gender] = "Female"
    )
VAR Result = COUNTROWS(FilteredSales)
RETURN 
    ResultDidukung oleh

Kali ini, baik kuantitas minimum, filter, maupun hasil disimpan sebagai variabel. Seperti prinsip BODMAS yang terkenal dalam matematika, yang memprioritaskan operasi di dalam tanda kurung terlebih dahulu, DAX juga memprioritaskan kondisi di dalam tanda kurung terlebih dahulu. 

Pada contoh di atas, operator OR || dievaluasi terlebih dahulu sebelum operator AND && karena berada di dalam tanda kurung. Ini berarti pesanan penjualan dengan kuantitas di atas 5 atau dibayar dengan kartu kredit difilter terlebih dahulu sebelum memfilter pesanan oleh pelanggan perempuan. 

COUNTIF() lintas tabel terkait

Jika Anda bekerja dengan lebih dari satu tabel, Anda juga dapat menggunakan COUNTROWS() atau COUNTA() untuk menghitung jumlah record berdasarkan kondisi yang terpenuhi di tabel lain menggunakan fungsi RELATEDTABLE(). 

Fungsi RELATEDTABLE() mempertimbangkan relasi model antara dua tabel, yang dapat one-to-one atau one-to-many.

Misalkan kita memiliki tabel penjualan lain bernama Sales2, yang masih menggunakan data penjualan supermarket yang sama. Dan Sales serta Sales2 berelasi melalui Invoice ID. 

Relasi antara tabel Sales dan Sales2. Gambar oleh Penulis.

Anda dapat menggunakan sintaks DAX di bawah ini untuk melihat berapa banyak pelanggan yang merupakan member, dengan asumsi Sales tidak memiliki kolom Customer type tetapi Sales2 memiliki.

Orders by members = 
COUNTROWS(FILTER(RELATEDTABLE(Sales2), Sales2[Customer Type] = "Member"))Didukung oleh

Jumlah pesanan oleh pelanggan yang merupakan member. Gambar oleh Penulis.

• RELATEDTABLE(Sales2)  secara otomatis memfilter Sales2 untuk hanya menyertakan baris dengan Invoice ID yang sama pada data Sales.

• Fungsi FILTER() semakin mempersempitnya hanya pada pesanan "Member", sementara COUNTROWS() menghitung tabel yang telah difilter. 

Kasus Penggunaan Praktis dan Kiat Integrasi

Berikut beberapa kasus penggunaan praktis dan kiat integrasi untuk mengimplementasikan COUNTIF() di Power BI.

Skenario dunia nyata

Jika Anda memiliki tabel hasil ujian siswa atau pesanan, Anda dapat menggunakan COUNTROWS() dan FILTER() untuk menghitung jumlah record yang berulang. Menggunakan kolom nilai mereka, Anda juga bisa menghitung berapa banyak siswa yang lulus atau gagal. Padanan COUNTIF() penting lainnya adalah SUMX(), yang dapat Anda gunakan untuk menjumlahkan nilai siswa berdasarkan suatu kondisi. 

Alat Integrasi Data

Alih-alih selalu mengunggah file spreadsheet dari Excel atau Google Sheets untuk analisis, Anda dapat menggunakan alat seperti Coupler.io untuk mengotomatiskan ekstraksi data. 

Setelah spreadsheet Anda diperbarui, perubahan tersebut otomatis tercermin pada dasbor Power BI tanpa perlu Anda unggah secara manual. 

Ini memastikan Power BI selalu mencerminkan data terbaru tanpa mengunggah file secara manual, sehingga Anda sebagai pengguna dapat fokus menggali insight dan membuat keputusan bisnis. Anda juga dapat menggunakan alat eksternal ini untuk menghubungkan banyak sumber ke dalam satu dasbor. 

Kiat dari pengalaman untuk keterbacaan dan performa

Selalu gunakan calculated column ketika perhitungan harus dilakukan pada tingkat baris sebelum agregasi. Gunakan measure ketika perhitungan bergantung pada filter atau slicer yang dipilih pengguna, atau ketika Anda memerlukan nilai agregat alih-alih perhitungan tingkat baris. 

Memahami filter context adalah kunci untuk mengetahui kapan harus menggunakan measure atau calculated column. Filter context berlaku ketika slicer, filter, atau pengelompokan diterapkan, berbeda dengan row context yang berlaku dalam baris tabel. Calculated column menggunakan row context dan dapat membuat kolom yang tidak perlu sehingga meningkatkan penggunaan memori. 

Penting juga untuk mempertimbangkan filter context saat membuat measure. Misalnya, alih-alih menggunakan SUM(Sales[Amount]), gunakan CALCULATE(SUM(Sales[Amount])) untuk mengendalikan pemfilteran. Jika Anda sudah memiliki filter yang diterapkan pada data, Anda tidak akan mendapatkan nilai yang tepat menggunakan yang pertama, tetapi semua filter yang diterapkan pada data diabaikan dengan menggunakan yang kedua. 

Selain itu, hindari menggunakan terlalu banyak iterator seperti FILTER() dalam satu sintaks DAX, karena akan memperlambat dataset besar sebab ia melakukan iterasi baris demi baris.

Kesimpulan

Meskipun COUNTIF() bawaan di Excel tidak ada di Power BI, fungsi DAX seperti CALCULATE(), COUNTROWS(), COUNTA(), dan FILTER() memungkinkan Anda mereplikasi fungsionalitas ini.

Pendekatan yang Anda pilih bergantung pada sifat data Anda. Jika ada nilai hilang, pertimbangkan menggunakan COUNTA(), tetapi jika data Anda lengkap, Anda dapat menggunakan COUNTROWS(). Untuk mengecualikan filter eksternal yang diterapkan pada data, pastikan Anda menggunakan ALLEXCEPT() di dalam fungsi CALCULATE().

Selalu penting untuk dicatat bahwa jenis sintaks DAX yang Anda tulis menentukan seberapa cepat visual dimuat di dasbor Anda. Usahakan menulis sintaks DAX yang dioptimalkan dan pahami kapan harus menggunakan measure atau calculated column. Berikut beberapa sumber DataCamp untuk menjelajahi teknik DAX tingkat lanjut lainnya. 

• Tutorial Power BI Calculate
• Fungsi DAX RELATED di Power BI: Pengenalan dan Kasus Penggunaan
• Menguasai SWITCH di DAX untuk Power BI: Panduan Komprehensif
• Cara Menggunakan Fungsi SUMX di Power BI
• DAX SUMMARIZE(): Panduan untuk Pengelompokan dan Perangkuman Data
• Panduan Komprehensif DAX LOOKUPVALUE