Bảng tính 5 — Data Model & DAX trong Excel

Khi một sheet phẳng không còn đủ

Trong Bảng tính 3 — PivotTable và Bảng tính 4 — Power Query, ta đã tổng hợp và làm sạch dữ liệu trên một bảng phẳng. Nhưng nghiệp vụ thật hiếm khi gói gọn trong một bảng. Một báo cáo dư nợ tử tế cần giao dịch (hàng triệu dòng), nối với khách hàng (phân khúc, tỉnh/thành), nối với sản phẩm (loại vay, kỳ hạn), nối với lịch (tháng, quý, năm tài chính). Cách "gộp tay" quen thuộc là kéo cả rừng VLOOKUP để nhét tên khách, phân khúc, tên sản phẩm vào từng dòng giao dịch — file phình gấp ba, tính lại chậm rề, và mỗi lần thêm cột lại phải sửa hàng loạt công thức.

Excel giải bài này bằng Data Model (còn gọi là Power Pivot): một cơ sở dữ liệu dạng cột nén, nằm ngay trong file .xlsx, cho phép nạp nhiều bảng, nối chúng bằng quan hệ (relationship), rồi tính bằng ngôn ngữ đo lường DAX. Đây chính là engine mà Power BI dùng — học Data Model trong Excel là học nửa đầu của Power BI mà không cần cài thêm gì.

Một quy ước xuyên suốt: DAX không phải SQL và công thức bảng tính không phải DAX. Các khối DAX/công thức trong bài chỉ để minh hoạ, không chạy được trên SQL Builder. Cuối bài có đúng một câu SQL nguồn (theo schema sandbox) đánh dấu ▶ để thấy dữ liệu thô thường được kéo ra từ đâu trước khi nạp vào mô hình.

Data Model là gì — và vì sao nó nhanh

Bật Data Model qua tab Power Pivot (cần kích hoạt add-in: File → Options → Add-ins → COM Add-ins → Microsoft Power Pivot for Excel). Từ Power Query hay từ một bảng, chọn Add to Data Model để nạp bảng vào mô hình.

Điểm khác biệt cốt lõi với sheet thường:

• Lưu trữ dạng cột, nén mạnh (VertiPaq / xVelocity). Thay vì lưu theo dòng, engine lưu theo cột và nén trùng lặp. Một cột city chỉ có ~60 giá trị tỉnh/thành sẽ nén cực gọn dù có 5 triệu dòng. Nhờ vậy Data Model vượt giới hạn ~1.048.576 dòng hiển thị của một worksheet: mô hình chứa được hàng triệu đến vài chục triệu dòng trong bộ nhớ, trong khi sheet chỉ dùng để hiển thị kết quả tổng hợp.
• Tính bằng in-memory engine, không phải tính từng ô công thức. Một measure tổng hợp trên chục triệu dòng thường trả kết quả trong tích tắc.
• Nhiều bảng cùng tồn tại, mỗi bảng giữ đúng độ hạt (grain) của nó — không cần "làm phẳng" mọi thứ về một bảng khổng lồ.

Nói ngắn gọn: sheet là nơi nhìn, Data Model là nơi chứa và tính.

Quan hệ (relationship) — thay cho VLOOKUP gộp bảng

Thay vì kéo VLOOKUP để nhét phân khúc khách vào từng dòng giao dịch, bạn tạo quan hệ giữa bảng giao dịch và bảng khách theo khoá chung (ví dụ customer_id). Trong Diagram View của Power Pivot, kéo từ cột khoá bảng này sang cột khoá bảng kia là xong.

Quan hệ điển hình là một-nhiều (one-to-many): một khách có nhiều giao dịch; một sản phẩm có nhiều hợp đồng. Đầu "một" là bảng dimension (chiều), đầu "nhiều" là bảng fact (sự kiện). Chiều lọc mặc định chảy từ dimension sang fact: khi bạn lọc Khách.PhanKhuc = "Priority", các measure trên bảng giao dịch tự động chỉ tính những dòng thuộc khách Priority — đúng cái mà VLOOKUP + SUMIFS phải làm thủ công.

Star schema — mô hình sao

Cách sắp xếp chuẩn là star schema (mô hình sao): một bảng fact ở trung tâm (giao dịch/hợp đồng, chứa số đo và các khoá), các bảng dimension bao quanh (khách, sản phẩm, lịch, chi nhánh). Mỗi dimension nối trực tiếp với fact bằng một quan hệ một-nhiều. Ý cốt lõi: star schema làm mô hình dễ hiểu, lọc đúng chiều, và tính nhanh.

Nguyên tắc thực chiến: fact chứa số đo + khoá, không chứa thuộc tính mô tả; mọi thuộc tính để mô tả/lọc đều đặt ở dimension. Tên khách, phân khúc, tên sản phẩm không nằm trong bảng giao dịch — chúng ở dimension và được kéo vào PivotTable qua quan hệ.

DAX — ngôn ngữ đo lường

DAX (Data Analysis Expressions) là ngôn ngữ để định nghĩa phép tính trên Data Model. Nó trông giống công thức Excel (SUM, IF, DIVIDE) nhưng làm việc trên bảng và cột, không trên ô lẻ.

Measure vs Calculated Column — khác biệt cốt tử

Đây là ngã rẽ quan trọng nhất khi mới học DAX.

Quy tắc chọn: cần một thuộc tính để đưa lên hàng/cột/slicer của pivot → calculated column (ví dụ cột "Bậc số dư" phân nhóm khách). Cần một con số tổng hợp thay đổi theo bối cảnh → measure (ví dụ "Tổng dư nợ", "CASA ratio"). Mặc định ưu tiên measure: nó không phình RAM và tự đúng theo mọi lát cắt.

// Calculated column: phân bậc số dư trên bảng Khách (row context)
Bac So Du =
SWITCH(
    TRUE(),
    Khach[SoDu] >= 1000000000, "Tỷ phú",
    Khach[SoDu] >= 100000000,  "Cao",
    "Thường"
)

// Measure: tổng dư nợ, tự đúng theo mọi lát cắt pivot (filter context)
Tong Du No = SUM( GiaoDich[amount] )

CALCULATE — trái tim của DAX

CALCULATE là hàm quan trọng nhất: nó tính một biểu thức trong một ngữ cảnh lọc đã bị thay đổi. Cú pháp: CALCULATE( <biểu_thức>, <điều_kiện_lọc_1>, <điều_kiện_lọc_2>, ... ).

// Dư nợ chỉ của sản phẩm vay tín chấp, bất kể pivot đang lọc gì
Du No Tin Chap =
CALCULATE( [Tong Du No], SanPham[NhomSP] = "Tín chấp" )

// Dư nợ toàn hệ thống (bỏ mọi lọc trên bảng Khách) — để tính tỷ trọng
Du No Toan He =
CALCULATE( [Tong Du No], ALL(Khach) )

// Tỷ trọng dư nợ của lát cắt hiện tại trên toàn hệ thống
Ty Trong Du No =
DIVIDE( [Tong Du No], [Du No Toan He] )

CALCULATE cho phép ghi đè bộ lọc (thêm điều kiện), gỡ bộ lọc (ALL), hoặc giữ một phần (ALLEXCEPT). Hầu hết phép tính "nâng cao" trong DAX chỉ là CALCULATE với một bộ lọc khéo.

Iterator: SUMX / AVERAGEX

Các hàm X (iterator) duyệt từng dòng của một bảng, tính một biểu thức trên mỗi dòng, rồi tổng hợp. Dùng khi phép tính cần chạy theo dòng trước rồi mới cộng — thứ mà SUM một cột không làm được.

// Doanh thu = số lượng × đơn giá, tính theo TỪNG dòng rồi cộng
Doanh Thu = SUMX( GiaoDich, GiaoDich[so_luong] * GiaoDich[don_gia] )

// Số dư trung bình theo tài khoản (mỗi dòng một tài khoản)
So Du TB Tai Khoan = AVERAGEX( TaiKhoan, TaiKhoan[balance] )

Khác biệt: SUM(A) * SUM(B) nhân hai tổng (thường SAI cho doanh thu), còn SUMX(bảng, A*B) nhân trong từng dòng rồi mới cộng (ĐÚNG).

FILTER, ALL, ALLEXCEPT, DIVIDE

• FILTER( bảng, điều_kiện ) trả về một bảng con — thường lồng trong CALCULATE cho điều kiện phức tạp: CALCULATE([Tong Du No], FILTER(GiaoDich, GiaoDich[amount] > 5e8)).
• ALL(bảng/cột) gỡ bộ lọc — dùng cho mẫu số của tỷ trọng, hoặc "tổng toàn phần".
• ALLEXCEPT(bảng, cột_giữ) gỡ mọi lọc trừ vài cột — hữu ích khi tính tỷ lệ trong nhóm.
• DIVIDE(tử, mẫu, [giá_trị_khi_chia_0]) chia an toàn: mẫu bằng 0 trả về giá trị mặc định (thường BLANK/0) thay vì lỗi. Luôn dùng DIVIDE thay cho / trong DAX.

Ngữ cảnh: row context vs filter context

Đây là khái niệm khiến người mới bối rối nhất, nên tiếp cận bằng trực giác:

• Row context (ngữ cảnh dòng): "đang đứng ở một dòng cụ thể". Calculated column và iterator (SUMX) chạy trong row context — mỗi phép tính biết mình đang ở dòng nào, đọc được giá trị các cột của chính dòng đó.
• Filter context (ngữ cảnh lọc): "tập dòng đang được cho phép nhìn thấy". Do bộ lọc từ pivot (hàng, cột, slicer, filter) tạo ra. Measure chạy trong filter context.

Câu hỏi hay gặp: vì sao cùng một measure "Tổng dư nợ" lại ra số khác nhau ở mỗi ô pivot? Vì mỗi ô áp một filter context riêng: ô ở dòng "Hà Nội", cột "Q1/2026" ngầm lọc city = "Hà Nội" AND quý = 1, năm = 2026, nên SUM(amount) chỉ cộng các giao dịch thoả cả hai. Measure không có giá trị cố định — nó là một công thức được đánh giá lại trong ngữ cảnh của từng ô. Hiểu điều này là hiểu 80% DAX.

CALCULATE chính là công cụ sửa filter context một cách chủ động, và nó còn gây context transition (biến row context thành filter context) — chủ đề chuyên sâu được trình bày kỹ trong Power BI 4 — DAX row/filter context. Cùng engine, cùng ngôn ngữ, nên kiến thức chuyển thẳng từ Excel sang Power BI.

Time intelligence — YTD, MoM, YoY

Báo cáo ngân hàng luôn hỏi "so với cùng kỳ năm ngoái", "luỹ kế từ đầu năm". DAX có nhóm hàm time intelligence làm việc này gọn — nhưng chúng cần một bảng lịch (Date table) chuẩn.

Date table — điều kiện tiên quyết

Time intelligence yêu cầu một bảng ngày liên tục, không thiếu ngày nào, phủ toàn bộ khoảng dữ liệu, và được đánh dấu là Date table (Power Pivot → Design → Mark as Date Table). Tạo nhanh bằng CALENDAR/CALENDARAUTO:

// Bảng lịch: một dòng mỗi ngày, kèm cột năm/quý/tháng để nhóm
Lich =
ADDCOLUMNS(
    CALENDAR( DATE(2020,1,1), DATE(2026,12,31) ),
    "Nam",  YEAR([Date]),
    "Quy",  "Q" & QUARTER([Date]),
    "Thang", FORMAT([Date], "YYYY-MM")
)

Nối Lich[Date] với cột ngày của bảng fact (quan hệ một-nhiều), rồi mọi hàm time intelligence sẽ hoạt động.

Các hàm chủ lực

// Luỹ kế từ đầu năm (Year-To-Date)
Du No YTD = TOTALYTD( [Tong Du No], Lich[Date] )

// Cùng kỳ năm trước
Du No Nam Truoc =
CALCULATE( [Tong Du No], SAMEPERIODLASTYEAR( Lich[Date] ) )

// Tháng trước (dịch -1 tháng) — nền cho MoM
Du No Thang Truoc =
CALCULATE( [Tong Du No], DATEADD( Lich[Date], -1, MONTH ) )

// Tăng trưởng YoY (%)
Tang Truong YoY =
DIVIDE( [Tong Du No] - [Du No Nam Truoc], [Du No Nam Truoc] )

// Tăng trưởng MoM (%)
Tang Truong MoM =
DIVIDE( [Tong Du No] - [Du No Thang Truoc], [Du No Thang Truoc] )

• TOTALYTD cộng dồn từ đầu năm tới ngày trong ngữ cảnh — cho biểu đồ luỹ kế.
• SAMEPERIODLASTYEAR dịch cả khoảng về đúng một năm trước — nền của YoY.
• DATEADD dịch tuỳ ý (MONTH, QUARTER, YEAR) — linh hoạt cho MoM/QoQ.

Các measure này tái sử dụng lẫn nhau: Tang Truong YoY gọi Du No Nam Truoc, vốn gọi Tong Du No. Sửa định nghĩa dư nợ ở một nơi, toàn bộ chuỗi tự đúng theo — đây là lợi thế lớn của measure so với công thức rải rác. Về triết lý thiết kế chỉ số, xem thêm Metrics & KPI.

Thực hành: mô hình dư nợ ngân hàng

Dựng một mô hình mini gồm ba bảng:

• Fact GiaoDich: transaction_id, customer_id, product_id, date_key, amount, kind (deposit/loan/casa...).
• Dimension Khach: customer_id, full_name, phan_khuc, city.
• Dimension SanPham: product_id, ten_sp, nhom_sp (Vay/Tiền gửi CASA/Tiền gửi có kỳ hạn).

Quan hệ: GiaoDich[customer_id] → Khach[customer_id], GiaoDich[product_id] → SanPham[product_id], và GiaoDich[date_key] → Lich[Date].

Vài measure lõi cho báo cáo:

// Tổng dư nợ cho vay
Tong Du No =
CALCULATE( SUM(GiaoDich[amount]), SanPham[nhom_sp] = "Vay" )

// Số dư CASA (tiền gửi không kỳ hạn)
So Du CASA =
CALCULATE( SUM(GiaoDich[amount]), SanPham[nhom_sp] = "Tiền gửi CASA" )

// Tổng tiền gửi (CASA + có kỳ hạn)
Tong Tien Gui =
CALCULATE(
    SUM(GiaoDich[amount]),
    SanPham[nhom_sp] IN { "Tiền gửi CASA", "Tiền gửi có kỳ hạn" }
)

// CASA ratio = số dư CASA / tổng tiền gửi
CASA Ratio = DIVIDE( [So Du CASA], [Tong Tien Gui] )

// Tăng trưởng dư nợ YoY (dùng lại các measure time intelligence ở trên)
Du No YoY =
DIVIDE(
    [Tong Du No] - CALCULATE([Tong Du No], SAMEPERIODLASTYEAR(Lich[Date])),
    CALCULATE([Tong Du No], SAMEPERIODLASTYEAR(Lich[Date]))
)

Kéo Khach[phan_khuc] lên hàng, Lich[Thang] lên cột của một PivotTable, đặt [Tong Du No], [CASA Ratio], [Du No YoY] vào vùng giá trị — bạn có ngay bảng dư nợ theo phân khúc × tháng, kèm tỷ lệ CASA và tăng trưởng so cùng kỳ, tự cập nhật khi refresh. Không một dòng VLOOKUP nào.

Data Model dùng chung: các measure này không thuộc riêng một PivotTable. Mọi PivotTable/PivotChart trong file đều dùng chung mô hình và measure, đảm bảo mọi báo cáo nói cùng một con số cho "Tổng dư nợ" — một dạng "single source of truth" ngay trong Excel.

Mẹo & cạm bẫy

• Thiếu Date table hoặc Date table có lỗ ngày → time intelligence trả sai/BLANK. Luôn dựng bảng lịch liên tục, phủ hết khoảng, và Mark as Date Table.
• Calculated column phình bộ nhớ: cột tính vật lý được lưu và nén kém nếu nhiều giá trị khác nhau. Cái gì tổng hợp được bằng measure thì đừng làm bằng column.
• Quan hệ nhiều-nhiều (many-to-many): dễ nhân đôi số liệu và gây kết quả khó lường. Ưu tiên một-nhiều với dimension "sạch" (khoá duy nhất ở đầu "một"); nếu buộc phải many-to-many, hiểu rõ hệ quả trước khi dùng.
• Chiều lọc (cross-filter direction): mặc định lọc chảy một chiều dimension → fact. Bật lọc hai chiều tuỳ tiện dễ gây vòng lọc và số sai; chỉ mở khi thật cần và kiểm chứng kỹ.
• Nhầm measure với calculated column: dùng column cho cái đáng ra là measure khiến số "đóng băng" không đổi theo pivot; ngược lại dùng measure ở nơi cần một thuộc tính để lọc thì không đặt lên hàng/cột được.
• Dùng / thay DIVIDE: chia 0 gây lỗi/Infinity; DIVIDE xử lý gọn.
• Nhồi thuộc tính vào fact: đừng để tên khách/sản phẩm trong bảng giao dịch — giữ star schema sạch, đặt thuộc tính ở dimension.

Use case thực tế

Phòng ALM cần báo cáo tháng: CASA ratio và tăng trưởng dư nợ YoY theo phân khúc khách, trên tập giao dịch 8 triệu dòng (3 năm dữ liệu) — vượt xa giới hạn ~1,05 triệu dòng của một worksheet.

Luồng thực hiện:

1. Kéo dữ liệu thô từ kho bằng SQL (xem câu ▶ bên dưới cho phần số dư tài khoản), nạp qua Power Query vào Data Model — 8 triệu dòng nén còn vài chục MB nhờ VertiPaq, mở được bình thường.
2. Dựng star schema: fact GiaoDich nối với Khach, SanPham, Lich bằng quan hệ một-nhiều.
3. Viết measure: Tong Du No, So Du CASA, Tong Tien Gui, CASA Ratio, Du No YoY như phần thực hành.
4. Dựng PivotTable: phan_khuc × Thang, giá trị là ba measure.

Kết quả mẫu (minh hoạ): phân khúc Priority đạt CASA ratio 41% (cao, chi phí vốn thấp), dư nợ tăng +18% YoY; phân khúc Mass có CASA ratio 12% nhưng dư nợ tăng +7% YoY. Toàn bộ báo cáo refresh trong vài giây mỗi tháng, thay cho quy trình cũ dùng chục cột VLOOKUP trên file 400 MB tính lại hàng phút và hay treo. Khi đã ổn định, chính mô hình này có thể bê thẳng sang Power BI (cùng engine VertiPaq, cùng DAX) để chia sẻ và lịch làm mới tự động.

Phần dữ liệu số dư tài khoản gốc thường bắt đầu từ một câu như sau (đúng schema sandbox — số dư gộp theo thành phố và loại tiền, đầu vào để đối chiếu với mô hình):

-- ▶ Chạy được
SELECT
  c.city                              AS thanh_pho,
  a.currency                          AS loai_tien,
  COUNT(DISTINCT a.id)                AS so_tai_khoan,
  SUM(a.balance)                      AS tong_so_du,
  ROUND(AVG(a.balance)::numeric, 2)   AS so_du_tb
FROM accounts a
JOIN customers c ON c.id = a.customer_id
GROUP BY c.city, a.currency
ORDER BY thanh_pho, loai_tien;

Ghi nhớ

• Data Model (Power Pivot) nạp nhiều bảng vào file Excel, nén cột bằng VertiPaq, vượt giới hạn ~1,05 triệu dòng và tính in-memory rất nhanh; sheet chỉ để hiển thị.
• Quan hệ một-nhiều giữa dimension và fact thay VLOOKUP gộp bảng; sắp xếp theo star schema (fact ở giữa, dimension bao quanh), giữ thuộc tính ở dimension.
• DAX: phân biệt measure (tính theo filter context, không phình RAM — ưu tiên) và calculated column (row context, dùng làm thuộc tính để lọc/nhóm).
• CALCULATE là trái tim — sửa filter context; SUMX/AVERAGEX iterator tính theo từng dòng; ALL/ALLEXCEPT/FILTER điều khiển bộ lọc; luôn dùng DIVIDE thay /.
• Ngữ cảnh: measure đổi theo ô pivot vì mỗi ô áp một filter context riêng — hiểu điều này là hiểu phần lớn DAX (chuyên sâu ở pbi-04-dax-context).
• Time intelligence (TOTALYTD, SAMEPERIODLASTYEAR, DATEADD) cho YTD/MoM/YoY — bắt buộc có Date table liên tục và Mark as Date Table.
• Cạm bẫy: thiếu Date table, calculated column phình RAM, quan hệ nhiều-nhiều, chiều lọc hai chiều tuỳ tiện, nhồi thuộc tính vào fact.
• Cùng engine và DAX với Power BI — mô hình dựng trong Excel bê thẳng sang được.

Đọc tiếp

• Bảng tính 3 — PivotTable & phân tích
• Bảng tính 4 — Power Query trong Excel
• Bảng tính 6 — Dashboards
• Bảng tính 8 — Thực hành ngân hàng
• Power BI 1 — Tổng quan
• Power BI 4 — DAX row/filter context