[Excel Tutorial 5] Cách sử dụng DAX trong Excel

Trong kỷ nguyên số hóa, việc xử lý dữ liệu lớn không còn là đặc quyền của riêng các kỹ sư dữ liệu. Ngay cả với những người dùng văn phòng quen thuộc với Microsoft Excel, nhu cầu phân tích hàng triệu dòng dữ liệu một cách nhanh chóng và chính xác ngày càng trở nên cấp thiết.

Đó là lý do bạn cần biết đến DAX trong Excel – chìa khóa vàng giúp nâng tầm kỹ năng phân tích dữ liệu của bạn lên một đẳng cấp hoàn toàn mới. Bài viết này sẽ giúp bạn hiểu rõ bản chất của ngôn ngữ DAX, cách áp dụng các hàm DAX cơ bản, vai trò thực tế của nó và so sánh chi tiết giữa DAX trong Excel và Power BI.

DAX trong Excel là gì?

Về cơ bản, DAX trong Excel là một công cụ cực mạnh giúp bạn thực hiện các phép tính toán phức tạp trực tiếp trên mô hình dữ liệu (Data Model) của Excel, vượt xa giới hạn của các công thức Excel truyền thống. Bạn sử dụng DAX để định nghĩa các phép tính toán tùy chỉnh cho các Calculated Columns và các Measures. Ngôn ngữ này kế thừa rất nhiều hàm quen thuộc từ công thức Excel truyền thống nhưng được bổ sung thêm các hàm đặc biệt được thiết kế riêng để làm việc với dữ liệu quan hệ và thực hiện các phép tổng hợp động.

Cú pháp của DAX cực kỳ giống với công thức Excel. Để tạo một công thức DAX, bạn chỉ cần gõ dấu bằng (=), theo sau là tên hàm hoặc biểu thức tính toán, và các đối số hoặc giá trị bắt buộc.

Tuy nhiên, công thức DAX sở hữu 3 điểm khác biệt mang tính cách mạng so với Excel thông thường:

Tính toán linh hoạt theo ngữ cảnh dòng (Row Context): Nếu bạn muốn tùy chỉnh các phép tính toán trên từng dòng dữ liệu, DAX cung cấp các hàm cho phép bạn sử dụng giá trị của dòng hiện tại hoặc các giá trị có liên quan ở bảng khác để thực hiện phép tính tự động thay đổi theo ngữ cảnh.
Trả về kết quả dạng Bảng (Table-valued functions): Trong khi công thức Excel thường chỉ trả về một giá trị đơn lẻ, nhiều hàm trong DAX có khả năng trả về cả một bảng dữ liệu làm kết quả. Bảng này sau đó có thể tiếp tục làm đầu vào đầu dữ liệu cho các hàm tính toán khác.
Hàm thời gian thông minh (Time Intelligence): DAX sở hữu bộ hàm thời gian ưu việt, cho phép bạn thực hiện các phép tính toán trên nhiều phạm vi ngày tháng khác nhau và so sánh trực tiếp kết quả giữa các chu kỳ song song.

Ngôn ngữ DAX được phát triển để xử lý các mô hình dữ liệu quan hệ một cách tối ưu nhất. Vì thế, nó được sử dụng đồng thời trong nhiều sản phẩm phân tích dữ liệu hàng đầu của Microsoft bao gồm: Microsoft Power BI, Microsoft Analysis Services và Microsoft Power Pivot trong Excel.

Các khái niệm cơ bản về ngôn ngữ DAX trong Excel

Ngôn ngữ DAX trong Excel được áp dụng chủ yếu bên trong công cụ Power Pivot. Khi bạn nạp dữ liệu từ nhiều nguồn khác nhau vào mô hình dữ liệu (Data Model), bạn sẽ viết các công thức DAX tại hai khu vực chính:

Calculated Columns

Cột tính toán là cột mới mà bạn chủ động thêm vào một bảng dữ liệu sẵn có trong Power Pivot. Thay vì dán hoặc nhập thủ công các giá trị, bạn viết một công thức DAX để định nghĩa giá trị cho cột đó.

Đặc điểm: Công thức DAX được áp dụng tự động cho toàn bộ cột. Bạn không thể viết các công thức khác nhau cho các dòng khác nhau trong cùng một bảng như cách làm trên bảng tính Excel thông thường.
Giá trị của cột tính toán được tính toán ngay lập tức khi bạn nhập xong công thức và lưu trữ vật lý trong bộ nhớ. Nó chỉ tính toán lại khi dữ liệu nguồn được làm mới (refresh) hoặc khi bạn yêu cầu tính toán lại thủ công.
Kinh nghiệm thực tế: Khi viết công thức cho cột tính toán, tốt nhất bạn nên sử dụng tham chiếu đầy đủ bao gồm cả tên bảng và tên cột (ví dụ: Ten_Bang[Ten_Cot]) để tránh việc Excel hiểu nhầm cột tính toán này với một Measure. Bạn có thể kéo thả cột tính toán này vào các vùng Rows, Columns của PivotTable giống như mọi cột dữ liệu thông thường khác.

Measures hay Calculated Fields

Measure là một công thức tính toán được tạo ra để sử dụng riêng trong vùng giá trị (Values area) của PivotTable hoặc PivotChart. Measure có thể dựa trên các phép tổng hợp tiêu chuẩn (như COUNT, SUM) hoặc các biểu thức tùy chỉnh phức tạp do bạn tự định nghĩa bằng DAX.

Đặc điểm: Không giống cột tính toán, khi bạn tạo một Measure, sẽ chưa có bất kỳ phép tính vật lý nào xảy ra trên bảng dữ liệu.
Công thức của Measure chỉ thực sự được đánh giá và tính toán khi bạn kéo thả nó vào vùng Values của PivotTable. Lúc này, phép tính sẽ chạy độc lập và trả về kết quả riêng cho từng ô dựa trên sự kết hợp của các nhãn dòng và nhãn cột (Filter Context) tại ô đó. Do đó, cùng một Measure nhưng giá trị hiển thị ở mỗi ô trên PivotTable có thể hoàn toàn khác nhau.
Công thức định nghĩa của Measure sẽ được lưu trữ cùng với bảng dữ liệu nguồn của nó và xuất hiện trong danh sách trường dữ liệu để mọi người dùng file đều có thể dễ dàng tái sử dụng.

Một số hàm DAX trong Excel cơ bản

Hàm SUM

Hàm SUM trong DAX được sử dụng để tính tổng tất cả các giá trị số trong một cột cụ thể của bảng dữ liệu.

Cú pháp: SUM(Ten_Cot)

Ten_Cot là tên của cột chứa các giá trị số mà bạn muốn tính tổng. Hàm SUM chỉ chấp nhận tham số là một cột dữ liệu thực tế trong mô hình, bạn không thể quét chọn một dải ô tự do như trong Excel truyền thống.

Hàm AVERAGE

Hàm AVERAGE dùng để tính giá trị trung bình cộng của toàn bộ các số trong một cột cụ thể.

Cú pháp: AVERAGE(Ten_Cot)

Ten_Cot là tên cột chứa các giá trị cần tính trung bình. Hàm sẽ tự động bỏ qua các ô trống (Blank) hoặc các ô chứa dữ liệu dạng văn bản không thể tính toán.

Hàm MIN

Hàm MIN giúp tìm kiếm và trả về giá trị nhỏ nhất trong một cột dữ liệu hoặc so sánh giữa hai biểu thức để lấy giá trị nhỏ hơn.

MIN(Ten_Cot) hoặc MIN(Bieu_Thuc1, Bieu_Thuc2)

Ten_Cot: Trả về giá trị nhỏ nhất của cột đó.
Bieu_Thuc1, Bieu_Thuc2: So sánh trực tiếp hai biểu thức hoặc hai giá trị số và trả về số nhỏ nhất. Hàm này bỏ qua các giá trị logic (True/False) và các ô trống.

Hàm MAX

Tương tự như hàm MIN, hàm MAX được sử dụng để tìm và trả về giá trị lớn nhất trong một cột dữ liệu hoặc giữa hai biểu thức tính toán.

MAX(Ten_Cot) hoặc MAX(Bieu_Thuc1, Bieu_Thuc2)

Ten_Cot: Cột chứa dữ liệu mà bạn muốn tìm giá trị lớn nhất.
Bieu_Thuc1, Bieu_Thuc2: Hai biểu thức hoặc hai giá trị cần so sánh để lấy giá trị lớn hơn.

Hàm CALCULATE

Đây được mệnh danh là hàm quan trọng nhất và mạnh mẽ nhất trong ngôn ngữ DAX. Hàm CALCULATE cho phép bạn thay đổi ngữ cảnh lọc (Filter Context) để thực hiện một phép tính toán theo các điều kiện cụ thể.

CALCULATE(Bieu_Thuc, Bo_Loc1, Bo_Loc2, …)

Bieu_Thuc: Là một phép tính toán hoặc một Measure đã được tạo sẵn (ví dụ: Tổng doanh thu, Trung bình số lượng).
Bo_Loc1, Bo_Loc2, …: Là một hoặc nhiều điều kiện lọc để giới hạn dữ liệu trước khi biểu thức được tính toán. Các bộ lọc này sẽ ghi đè lên các bộ lọc hiện có trên PivotTable.

Hàm DIVIDE

Hàm DIVIDE được thiết kế riêng để thực hiện phép chia an toàn giữa tử số và mẫu số, giúp bạn tránh hoàn toàn lỗi chia cho 0 (lỗi #DIV/0! thường gặp trong Excel).

DIVIDE(Tu_So, Mau_So, [Ket_Qua_Thay_The])

Tu_So: Giá trị hoặc cột chứa số bị chia.
Mau_So: Giá trị hoặc cột chứa số chia.
[Ket_Qua_Thay_The] (Tùy chọn): Giá trị sẽ được trả về nếu Mau_So bằng 0 hoặc trống. Nếu bạn bỏ qua tham số này, hàm sẽ tự động trả về giá trị trống (Blank).

Hàm RANKX

Hàm RANKX được sử dụng để xếp hạng (đánh số thứ tự thứ hạng) của một giá trị cụ thể hoặc một biểu thức trong một danh sách các giá trị từ một bảng dữ liệu.

RANKX(Bang, Bieu_Thuc, [Gia_Tri], [Thu_Tu], [Cach_Xu_Ly_Trung_Lap])

Bang: Bảng dữ liệu chứa danh sách các hàng mà bạn muốn thực hiện xếp hạng.
Bieu_Thuc: Phép tính dùng để làm căn cứ xếp hạng.
[Gia_Tri] (Tùy chọn): Giá trị cụ thể muốn tìm thứ hạng. Thông thường phần này sẽ được để trống.
[Thu_Tu] (Tùy chọn): Xác định thứ tự xếp hạng. Nhận giá trị ASC (tăng dần – số nhỏ nhất xếp hạng 1) hoặc DESC (giảm dần – số lớn nhất xếp hạng 1. Đây là giá trị mặc định).
[Cach_Xu_Ly_Trung_Lap] (Tùy chọn): Cách xử lý khi có hai giá trị bằng nhau. Nhận giá trị Skip (bỏ qua thứ hạng tiếp theo, ví dụ: đồng hạng 2 thì hạng tiếp theo là 4) hoặc Dense (liên tục, ví dụ: đồng hạng 2 thì hạng tiếp theo vẫn là 3).

Vai trò của DAX trong Excel

Vượt qua giới hạn hiệu năng và dung lượng của Excel truyền thống

Excel thông thường giới hạn số lượng dòng tối đa là khoảng 1,04 triệu dòng trên mỗi trang tính (Sheet). Hơn thế nữa, khi file Excel của bạn vượt quá 100.000 dòng chứa các công thức phức tạp, máy tính sẽ bắt đầu có hiện tượng giật lag, đơ máy do Excel phải tính toán lại từng ô (Grid-based).

Khi bạn sử dụng Power Pivot và viết công thức bằng DAX, dữ liệu sẽ được lưu trữ trực tiếp trong mô hình dữ liệu hoạt động trên động cơ Tabular. Động cơ này sử dụng các thuật toán nén dữ liệu theo cột tiên tiến, giúp giảm dung lượng tệp tin từ 5 đến 10 lần và cho phép bạn thực hiện các phép tính toán mượt mà trên hàng triệu dòng dữ liệu mà không sợ treo máy.

Liên kết dữ liệu đa nguồn không cần đến các hàm dò tìm phức tạp

Trước đây, để phân tích dữ liệu từ nhiều bảng khác nhau, bạn bắt buộc phải dùng các hàm dò tìm nặng nề như VLOOKUP, INDEX-MATCH hay XLOOKUP để kéo dữ liệu về chung một bảng lớn. Việc này tạo ra các bảng dữ liệu phẳng khổng lồ, làm tăng dung lượng file và cực kỳ dễ xảy ra sai sót.

Với DAX, bạn chỉ cần thiết lập mối quan hệ giữa các bảng một lần duy nhất trong Data Model. Các công thức DAX sẽ tự động nhìn thấy mối quan hệ này và tính toán xuyên suốt giữa các bảng một cách tự nhiên. Bạn hoàn toàn có thể loại bỏ các cột phụ vắt kiệt bộ nhớ máy tính, giữ cho cấu trúc tệp báo cáo luôn tinh gọn, sạch sẽ.

Khả năng tính toán động linh hoạt theo ngữ cảnh bộ lọc

Trong Excel truyền thống, nếu muốn tính tổng doanh thu theo từng tháng hoặc từng khu vực, bạn thường phải tạo ra các bảng phụ và sử dụng hàm SUMIFS với các điều kiện cố định cứng nhắc.

Ngược lại, các Measures được tạo ra bằng ngôn ngữ DAX hoạt động theo cơ chế tính toán động theo thời gian thực. Khi người dùng cuối tương tác với báo cáo (chọn một nút lọc Slicer, nhấp vào một mốc thời gian hay thay đổi cấu trúc hàng/cột của PivotTable), công thức DAX sẽ tự động bắt lấy ngữ cảnh lọc tại đúng ô đó và trả về kết quả chính xác tức thì. Điều này giúp bạn tạo ra những hệ thống báo cáo có tính tương tác cực kỳ cao mà không cần viết hàng trăm công thức tĩnh phức tạp.

DAX trong Excel với Power BI có khác gì nhau không?

Câu trả lời ngắn gọn là: Về bản chất ngôn ngữ và cú pháp, DAX trong Excel và Power BI hoàn toàn giống nhau.

Tuy nhiên, giữa chúng vẫn có một số điểm khác biệt về môi trường ứng dụng và tính năng hỗ trợ:

Đặc điểm so sánh	DAX trong Excel	DAX trong Power BI
Động cơ xử lý (Engine)	Sử dụng chung động cơ lưu trữ VertiPaq nén dữ liệu trong bộ nhớ trong (In-memory).	Sử dụng chung động cơ lưu trữ VertiPaq như Excel.
Giao diện làm việc	Viết công thức trong cửa sổ quản lý Power Pivot của Excel.	Viết công thức trực tiếp trong thanh công thức (Formula Bar) của Power BI Desktop.
Tần suất cập nhật	Cập nhật chậm hơn, phụ thuộc vào các phiên bản cập nhật lớn của Microsoft Office.	Được cập nhật liên tục hàng tháng với các hàm mới và tối ưu hóa hiệu năng tốt hơn.
Trực quan hóa dữ liệu	Kết quả tính toán của DAX được hiển thị chủ yếu thông qua PivotTable hoặc PivotChart truyền thống.	Kết quả tính toán DAX được kéo thả vào hàng loạt các biểu đồ tương tác hiện đại, thông minh của Power BI.
Mục tiêu sử dụng	Phù hợp cho những ai muốn lập báo cáo tài chính, báo cáo quản trị nhanh trên nền tảng Excel quen thuộc.	Phù hợp cho việc xây dựng các Dashboard động, chia sẻ báo cáo trực tuyến cho toàn doanh nghiệp.

Tổng kết

Việc làm chủ DAX trong Excel không chỉ giúp bạn bứt phá khỏi những giới hạn chật hẹp của bảng tính truyền thống, mà còn là bước chuẩn bị hoàn hảo để bước vào thế giới phân tích dữ liệu chuyên nghiệp với Power BI. Dù ban đầu có vẻ thách thức, nhưng một khi đã nắm vững nguyên lý hoạt động của ngữ cảnh lọc và các hàm cơ bản như CALCULATE, SUM hay DIVIDE, bạn sẽ thấy việc xử lý, liên kết hàng triệu dòng dữ liệu trở nên cực kỳ đơn giản và nhanh chóng.