Làm chủ COUNTIF() trong Power BI: Cách tiếp cận dựa trên DAX

Hàm COUNTIF() rất phổ biến với người dùng Excel vì cho phép đếm dữ liệu theo điều kiện. Tuy nhiên, khi chuyển sang Power BI, nhiều người nhận thấy Power BI không có hàm COUNTIF() gốc.

May mắn là người dùng có thể mô phỏng chức năng COUNTIF() bằng nhiều công thức DAX (Data Analysis Expressions). Bài viết này sẽ hướng dẫn bạn thực hiện các phép tính trong Power BI tương tự COUNTIF() bằng các hàm DAX như CALCULATE(), COUNTROWS(), COUNTA() và FILTER().

Vì sao COUNTIF() không tồn tại trong Power BI

Không giống Excel dựa trên công thức theo ô, Power BI được xây dựng xoay quanh các phép tính theo bảng và ngữ cảnh bộ lọc. Nó hoạt động trên các bảng và mối quan hệ giữa chúng, sử dụng DAX để tính toán. 

Thay vì tham chiếu từng ô riêng lẻ, DAX làm việc với các cột và áp dụng phép tính một cách linh hoạt dựa trên ngữ cảnh bộ lọc—được xác định bởi slicer, bộ lọc hàng/cột và mối quan hệ giữa các bảng. 

Excel COUNTIF() dùng cách tiếp cận theo ô, nơi công thức được áp dụng theo từng hàng trong một phạm vi xác định. Cách làm của Power BI khác ở chỗ dùng mối quan hệ giữa các bảng để tinh chỉnh phép tính. Thay vì quét từng ô, nó áp dụng các bộ lọc dựa trên ngữ cảnh báo cáo bằng DAX. 

Hàm ý đối với người dùng

Là người dùng Power BI xuất phát từ Excel, bạn cần thích nghi với cách tính toán theo bảng của Power BI. Các phương pháp DAX, vốn vận hành trên các cột dữ liệu, hiệu quả hơn so với việc lặp theo từng hàng. 

Khác với Excel, người dùng Power BI phải chọn phương pháp mô phỏng COUNTIF() phù hợp nhất với mô hình dữ liệu của mình. Vì vậy, bạn cần bỏ thói quen áp dụng công thức cho từng ô và tư duy theo bảng và ngữ cảnh bộ lọc.

Các hàm DAX cốt lõi để mô phỏng logic COUNTIF()

Cách bạn triển khai COUNTIF() trong Power BI phụ thuộc vào các yếu tố như mối quan hệ giữa các bảng, có hay không giá trị thiếu trong dữ liệu, kích thước dữ liệu, v.v. Một số hàm DAX được tối ưu tốt hơn cho xử lý nhanh và hiệu quả.  

Tổng quan các hàm chính

Các hàm DAX được dùng để tạo cột tính toán, measure và bảng. Dưới đây là một số hàm DAX giúp bạn mô phỏng chức năng COUNTIF() trong Power BI.

• CALCULATE(): Hàm này dùng để thay đổi ngữ cảnh bộ lọc cho một biểu thức và cho phép áp dụng bộ lọc mới hoặc điều chỉnh các bộ lọc hiện có một cách linh hoạt. Ví dụ, bạn có thể dùng hàm này để tính tổng doanh số cho một khu vực cụ thể. 

• COUNTROWS(): Đếm số lượng hàng trong một bảng cho trước, và bạn có thể kết hợp với các hàm lọc để đếm các hàng cụ thể. Ví dụ là đếm số đơn hàng có giá trị lớn hơn $1.000.

• COUNTA(): Đếm số giá trị không trống trong một cột, hữu ích khi xử lý dữ liệu thiếu. Ví dụ, bạn có thể đếm các mã khách hàng không trống để đảm bảo chỉ tính các bản ghi hợp lệ. 

• FILTER(): Thường dùng cùng với CALCULATE() và COUNTROWS(), hàm này trả về một bảng dựa trên điều kiện. Ví dụ, bạn có thể trả về bảng các đơn hàng có giá trị vượt $500.

• ALLEXCEPT(): Loại bỏ tất cả các bộ lọc đã áp dụng trừ những bộ lọc được chỉ định; ví dụ, bạn có thể trả về bảng tổng doanh số theo khu vực ngay cả khi đã áp dụng slicer hoặc bộ lọc lên dữ liệu. Điều này quan trọng khi bạn tổng hợp theo phân cấp.

• RELATEDTABLE(): Trả về một bảng dựa trên các mối quan hệ hiện có. Ví dụ là đếm số đơn hàng của mỗi khách hàng dựa trên mối quan hệ từ dữ liệu khách hàng.

Cách chúng phối hợp với nhau

Vì COUNTROWS() đếm số hàng trong một bảng, bạn có thể trả về một bảng thỏa điều kiện từ dữ liệu bằng FILTER(). Sau đó dùng hàm CALCULATE() để điều chỉnh ngữ cảnh bộ lọc, đảm bảo COUNTROWS() chỉ xét các hàng đáp ứng điều kiện. Dưới đây là ví dụ cú pháp DAX thể hiện cách hoạt động này.

Sales over 5 quantities = 
CALCULATE(
    COUNTROWS(Sales), 
    FILTER('Sales, 'Sales[Quantity] > 5)
)Được hỗ trợ bởi

Hàm CALCULATE() về cơ bản giống như kết hợp hai biểu thức khác nhau. Thay vì dùng COUNTROWS() để đếm toàn bộ dữ liệu, CALCULATE() sẽ giới hạn nó trong ngữ cảnh bộ lọc được tạo bởi hàm FILTER(). 

Ví dụ cơ bản về các phép tương đương COUNTIF() trong Power BI

Trước khi xem một số ví dụ cách triển khai COUNTIF() trong Power BI, hãy tải dữ liệu bán hàng siêu thị. 

Ví dụ 1: Số đơn hàng từ khách hàng nữ

Cú pháp DAX dưới đây tính số đơn hàng từ khách hàng nữ.

Orders by female customers = 
CALCULATE(
    COUNTROWS(Sales), 
    FILTER(Sales, Sales[Gender] = "Female")
)Được hỗ trợ bởi

Số đơn hàng từ khách hàng nữ. Ảnh: Tác giả.

• FILTER(Sales, Sales[Gender] = "Female") lọc các đơn hàng từ khách hàng nữ.

• COUNTROWS(Sales), thay vì đếm toàn bộ dữ liệu, sẽ đếm bảng đã được lọc nhờ ảnh hưởng của hàm CALCULATE().

Ví dụ 2: Đếm đơn hàng theo thành phố

Dưới đây là cú pháp DAX để đếm số đơn hàng cho mỗi thành phố.

Total Orders by City = CALCULATE(COUNTA(Sales[City]), ALLEXCEPT(Sales, Sales[City]))Được hỗ trợ bởi

Số đơn hàng theo thành phố. Ảnh: Tác giả.

• COUNTA(Sales[City]) đếm số giá trị không trống trong cột City.

• ALLEXCEPT(Sales, Sales[City]) loại bỏ mọi bộ lọc trừ cột City của bảng Sales.

• CALCULATE() đảm bảo COUNTA() được tính riêng cho từng thành phố, tương tự như hàm COUNTIF() trong Excel.

Ví dụ 3: Phương án khác dùng COUNTROWS

Một cách khác là dùng cú pháp DAX dưới đây, sử dụng COUNTROWS() thay cho COUNTA().

Total Orders by City = CALCULATE(COUNTROWS(Sales), ALLEXCEPT(Sales, Sales[City]))Được hỗ trợ bởi

COUNTROWS() đếm số hàng trong bảng Sales bất kể có giá trị trống.

Nếu cột City không trống, cả hai cách tiếp cận sẽ cho kết quả giống nhau. Tuy nhiên, nếu cột City có giá trị thiếu, COUNTA() sẽ loại chúng ra, còn COUNTROWS() sẽ tính cả.

Bạn nên dùng COUNTROWS() khi mỗi hàng đại diện cho một bản ghi, như một đơn hàng. Nhưng nếu dữ liệu có một số bản ghi không hợp lệ, bạn có thể dùng COUNTA(), vốn chỉ đếm các bản ghi có giá trị hợp lệ. 

Dùng measures cho các phép tính động

Có những tình huống bạn cần tạo measures thay đổi linh hoạt theo bộ lọc bảng hoặc slicer. Phần này bàn về cách triển khai COUNTIF() trong các trường hợp như vậy.

Kịch bản: Đếm đơn hàng giá trị cao

Ví dụ dưới đây quan tâm đến các đơn hàng có tổng số lượng lớn hơn năm.

High Value Orders = COUNTROWS(FILTER(Sales, Sales[Quantity] > 5))Được hỗ trợ bởi

Số đơn hàng giá trị cao. Ảnh: Tác giả.

• FILTER(Sales, Sales[Quantity] > 5) trả về các đơn hàng có số lượng lớn hơn năm.

• COUNTROWS() đếm số hàng trong bảng đã lọc với đơn hàng lớn hơn năm.

Khác với cột tính toán, khi tạo sẽ làm tăng dung lượng lưu trữ và kích thước mô hình, measures chỉ được tính khi cần, nên hiệu quả hơn. Cú pháp DAX để tạo một cột tính toán sẽ như sau:

 High Value = IF(Sales[Quantity] > 5, 1, 0)Được hỗ trợ bởi

Cột tính toán High Value. Ảnh: Tác giả.

Cách này sẽ lưu giá trị tĩnh 1 hoặc 0 ở mỗi hàng. Để đếm đơn hàng giá trị cao, bạn cần tạo một measure dùng SUM(Sales[High Value]). 

Hãy tưởng tượng tạo cột tính toán cho mọi bộ lọc. Điều này sẽ tăng sử dụng lưu trữ và giảm tính linh hoạt. 

Dưới đây là bảng cho thấy vì sao bạn nên ưu tiên dùng measures thay vì cột tính toán.

Kỹ thuật nâng cao và nhiều điều kiện

Excel COUNTIFS() hỗ trợ nhiều điều kiện, và bạn cũng có thể mô phỏng trong Power BI.

Nhiều tiêu chí / logic COUNTIFS()

Dưới đây là cú pháp DAX để đếm các đơn hàng có số lượng lớn hơn năm và thanh toán bằng thẻ tín dụng. 

Quantity Above 5 and Credit Card = 
COUNTROWS(
    FILTER(
        Sales, 
        Sales[Quantity] > 5 && Sales[Payment] = "Credit card"
    )
)Được hỗ trợ bởi

Số đơn hàng có số lượng lớn hơn năm và thanh toán bằng thẻ tín dụng. Ảnh: Tác giả.

Công thức trên lọc bảng Sales nơi số lượng đơn hàng vượt 5 và phương thức thanh toán là thẻ tín dụng. && đại diện cho toán tử AND, đảm bảo chỉ đếm các đơn hàng thỏa cả hai điều kiện.

Khi bạn chỉ quan tâm đến các đơn hàng thỏa bất kỳ điều kiện nào, dùng toán tử OR, được biểu diễn bằng || trong DAX.

Quantity Above 5 or Credit Card = 
COUNTROWS(
    FILTER(
        Sales, 
        Sales[Quantity] > 5 || Sales[Payment] = "Credit card"
    )
)Được hỗ trợ bởi

Số đơn hàng có số lượng lớn hơn năm hoặc thanh toán bằng thẻ tín dụng. Ảnh: Tác giả.

Dùng toán tử logic và biến

Cú pháp DAX mô phỏng COUNTIF() đôi khi có thể phức tạp. Việc dùng biến giúp dễ hiểu hơn và đảm bảo các điều kiện được đánh giá theo thứ tự mong muốn. Nó cũng tránh tính toán lặp lại bằng cách lưu trữ giá trị một lần. 

Dưới đây là ví dụ khác cho thấy cách đếm số đơn hàng từ khách hàng nữ, hoặc thanh toán bằng thẻ tín dụng, hoặc có số lượng trên năm, với việc dùng biến.

CreditCard Or Above 5 By Female = 
VAR FilteredSales = 
    FILTER(
        Sales,
        (Sales[Quantity] > 5 || Sales[Payment] = "Credit card") 
        && Sales[Gender] = "Female"
    )
RETURN 
    COUNTROWS(FilteredSales)Được hỗ trợ bởi

Số đơn hàng từ khách hàng nữ hoặc thanh toán bằng thẻ tín dụng hoặc có số lượng trên năm. Ảnh: Tác giả.

Bộ lọc được lưu vào một biến và dùng trong hàm COUNTROWS(). Dưới đây là một cách viết khác cho biểu thức trên

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 
    ResultĐược hỗ trợ bởi

Lần này, cả ngưỡng số lượng tối thiểu, bộ lọc và kết quả đều được lưu dưới dạng biến. Tương tự nguyên tắc BODMAS nổi tiếng trong toán học, ưu tiên các phép toán trong ngoặc trước, DAX cũng ưu tiên các điều kiện trong ngoặc trước. 

Trong ví dụ trên, toán tử OR || được đánh giá trước toán tử AND && vì nằm trong ngoặc. Điều này có nghĩa là các đơn hàng có số lượng trên 5 hoặc thanh toán bằng thẻ tín dụng được lọc trước, rồi mới lọc tiếp theo khách hàng nữ. 

COUNTIF() trên các bảng liên quan

Nếu bạn làm việc với nhiều bảng, bạn cũng có thể dùng COUNTROWS() hoặc COUNTA() để đếm số bản ghi dựa trên điều kiện thỏa ở bảng khác bằng hàm RELATEDTABLE(). 

Hàm RELATEDTABLE() xét đến mối quan hệ mô hình giữa hai bảng, có thể là một-một hoặc một-nhiều.

Giả sử ta có thêm một bảng bán hàng tên Sales2, vẫn sử dụng dữ liệu bán hàng siêu thị. Và Sales và Sales2 liên hệ với nhau qua Invoice ID. 

Mối quan hệ giữa bảng Sales và Sales2. Ảnh: Tác giả.

Bạn có thể dùng cú pháp DAX dưới đây để biết có bao nhiêu khách hàng là hội viên, giả sử Sales không có cột Customer type nhưng Sales2 có.

Orders by members = 
COUNTROWS(FILTER(RELATEDTABLE(Sales2), Sales2[Customer Type] = "Member"))Được hỗ trợ bởi

Số đơn hàng từ khách hàng là hội viên. Ảnh: Tác giả.

• RELATEDTABLE(Sales2) tự động lọc Sales2 chỉ bao gồm các hàng có cùng Invoice ID trong dữ liệu Sales.

• Hàm FILTER() tiếp tục thu hẹp còn các đơn hàng ”Member”, trong khi COUNTROWS() đếm bảng đã lọc. 

Tình huống thực tế và mẹo tích hợp

Dưới đây là một số tình huống thực tế và mẹo tích hợp khi triển khai COUNTIF() trong Power BI.

Kịch bản thế giới thực

Nếu bạn có một bảng điểm kiểm tra của học sinh hoặc đơn hàng, bạn có thể dùng COUNTROWS() và FILTER() để đếm số bản ghi lặp. Dựa trên cột điểm, bạn cũng có thể đếm bao nhiêu học sinh đậu hoặc rớt. Một phép tương đương COUNTIF() quan trọng khác là SUMX(), có thể dùng để cộng điểm học sinh theo điều kiện. 

Công cụ tích hợp dữ liệu

Thay vì lúc nào cũng tải tệp bảng tính từ Excel hoặc Google Sheets để phân tích, bạn có thể dùng các công cụ như Coupler.io để tự động trích xuất dữ liệu. 

Khi bảng tính của bạn được cập nhật, bảng điều khiển Power BI cũng tự động phản ánh thay đổi mà bạn không cần tải thủ công. 

Điều này đảm bảo Power BI luôn phản ánh dữ liệu mới nhất mà không cần tải tệp thủ công, cho phép bạn tập trung rút ra insight và đưa ra quyết định kinh doanh. Bạn cũng có thể dùng các công cụ bên ngoài này để kết nối nhiều nguồn vào một bảng điều khiển. 

Mẹo từ kinh nghiệm cho tính dễ đọc và hiệu năng

Luôn dùng cột tính toán khi cần tính ở cấp hàng trước khi tổng hợp. Bạn có thể dùng measure khi phép tính phụ thuộc vào bộ lọc hoặc slicer do người dùng chọn, hoặc khi bạn cần một giá trị tổng hợp thay vì tính ở cấp hàng. 

Hiểu ngữ cảnh bộ lọc là chìa khóa để biết khi nào dùng measure hay cột tính toán. Ngữ cảnh bộ lọc áp dụng khi có slicer, bộ lọc hoặc phân nhóm, khác với ngữ cảnh hàng vốn áp dụng trong phạm vi một hàng. Cột tính toán dùng ngữ cảnh hàng và có thể tạo các cột không cần thiết, làm tăng tiêu thụ bộ nhớ. 

Cũng cần cân nhắc ngữ cảnh bộ lọc khi tạo measures. Ví dụ, thay vì dùng SUM(Sales[Amount]), hãy dùng CALCULATE(SUM(Sales[Amount])) để kiểm soát việc lọc. Nếu bạn đã có bộ lọc áp dụng cho dữ liệu, bạn sẽ không nhận được giá trị đúng khi dùng cách thứ nhất, nhưng bằng cách thứ hai, tất cả bộ lọc áp dụng cho dữ liệu sẽ bị bỏ qua. 

Ngoài ra, tránh dùng quá nhiều iterator như FILTER() trong một cú pháp DAX, vì chúng làm chậm tập dữ liệu lớn do phải lặp qua dữ liệu theo từng hàng.

Kết luận

Mặc dù hàm COUNTIF() gốc trong Excel không có trong Power BI, các hàm DAX như CALCULATE(), COUNTROWS(), COUNTA() và FILTER() cho phép bạn mô phỏng chức năng này.

Cách tiếp cận bạn chọn phụ thuộc vào đặc thù dữ liệu. Nếu có giá trị thiếu, hãy cân nhắc dùng COUNTA(); còn nếu dữ liệu đầy đủ, bạn có thể dùng COUNTROWS(). Để loại trừ các bộ lọc bên ngoài áp dụng lên dữ liệu, hãy dùng ALLEXCEPT() bên trong hàm CALCULATE().

Luôn lưu ý rằng kiểu cú pháp DAX bạn viết ảnh hưởng đến tốc độ tải trực quan trên bảng điều khiển. Hãy viết DAX tối ưu và biết khi nào dùng measures hay cột tính toán. Dưới đây là một số tài nguyên DataCamp để khám phá các kỹ thuật DAX nâng cao khác. 

• Hướng dẫn Power BI Calculate
• Hàm DAX RELATED trong Power BI: Giới thiệu và trường hợp sử dụng
• Làm chủ SWITCH trong DAX cho Power BI: Hướng dẫn toàn diện
• Cách sử dụng các hàm SUMX trong Power BI
• DAX SUMMARIZE(): Hướng dẫn nhóm và tổng hợp dữ liệu
• Hướng dẫn toàn diện về DAX LOOKUPVALUE