Power BI 5 — DAX nâng cao: time intelligence & iterators

Từ "biết CALCULATE" đến "làm chủ DAX"

Ở bài DAX context bạn đã nắm ba trụ cột: row context (ngữ cảnh dòng), filter context (ngữ cảnh lọc) và CALCULATE — hàm duy nhất có thể ghi đè filter context. Bài này đứng trên nền đó để đi vào những kỹ thuật khiến DAX thực sự mạnh trong thực tế doanh nghiệp: dịch chuyển thời gian (time intelligence), lặp theo dòng (iterators), biến (VAR), bảng ảo (virtual tables) và xếp hạng (ranking).

Nếu bạn chưa vững phần bối cảnh, hãy quay lại bài trước — vì mọi thứ dưới đây thực chất chỉ là các cách khác nhau để thao túng filter context. Time intelligence là "dịch cửa sổ ngày sang trái/phải"; iterator là "tạo row context tạm rồi tổng hợp"; virtual table là "chuẩn bị một bảng làm bộ lọc cho CALCULATE". Hiểu chung một khung tư duy này, bạn sẽ thấy DAX bớt ma thuật hơn nhiều.

Toàn bộ code trong bài là minh hoạ (không chạy trong sandbox). Bạn dán vào Power BI Desktop / DAX Studio để thử.

Nền tảng bắt buộc: bảng Date

Time intelligence trong DAX không tự sinh ngày. Nó cần một bảng ngày (Date/Calendar table) thoả các điều kiện:

• Chứa một dòng cho mỗi ngày liên tục, không gián đoạn, phủ trọn khoảng thời gian của dữ liệu (thường từ 1/1 của năm sớm nhất đến 31/12 của năm muộn nhất).
• Có quan hệ với bảng fact qua cột ngày.
• Được đánh dấu là bảng ngày ("Mark as Date Table"). Bước này báo cho engine biết cột nào là khoá ngày, cho phép nhiều hàm time intelligence hoạt động chính xác và tự động reset filter context trên bảng đó.

Tạo bảng ngày bằng DAX (calculated table):

-- Bảng ngày liên tục (minh hoạ)
DimDate =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Năm", YEAR ( [Date] ),
    "Số tháng", MONTH ( [Date] ),
    "Tên tháng", FORMAT ( [Date], "MMM" ),
    "Quý", "Q" & FORMAT ( [Date], "Q" ),
    "Năm-Tháng", FORMAT ( [Date], "YYYY-MM" )
)

Không có bảng ngày đúng chuẩn, các hàm TOTALYTD, SAMEPERIODLASTYEAR... sẽ trả kết quả sai hoặc lỗi. Đây là nguyên nhân số một của lỗi time intelligence.

Time intelligence: dịch chuyển cửa sổ thời gian

Ý tưởng cốt lõi: mọi hàm time intelligence đều nhận filter context hiện tại trên bảng ngày, biến đổi tập ngày đó, rồi trả về tập ngày mới để CALCULATE áp dụng. Sơ đồ dưới minh hoạ cách một measure YTD/YoY "dịch" cửa sổ ngày:

Nhóm hàm hay dùng

Lưu ý phân biệt DATEADD vs PARALLELPERIOD: cả hai dịch thời gian, nhưng khác nhau khi filter context có nhiều đơn vị ở granularity khác. DATEADD giữ đúng số ngày đang chọn rồi dịch; PARALLELPERIOD trả về toàn bộ đơn vị (cả tháng/quý/năm) song song. SAMEPERIODLASTYEAR thực chất là DATEADD(..., -1, YEAR).

Mẫu YTD (lũy kế từ đầu năm)

-- Doanh thu lũy kế từ đầu năm (minh hoạ)
Doanh thu YTD =
TOTALYTD ( [Doanh thu], DimDate[Date] )

-- Tương đương, viết "trần" bằng CALCULATE:
Doanh thu YTD (v2) =
CALCULATE ( [Doanh thu], DATESYTD ( DimDate[Date] ) )

Mẫu YoY và YoY %

-- Doanh thu cùng kỳ năm trước (minh hoạ)
Doanh thu năm trước =
CALCULATE ( [Doanh thu], SAMEPERIODLASTYEAR ( DimDate[Date] ) )

-- Chênh lệch tuyệt đối YoY
YoY =
[Doanh thu] - [Doanh thu năm trước]

-- Tăng trưởng YoY %  (DIVIDE tránh chia 0)
YoY % =
VAR NamTruoc = [Doanh thu năm trước]
RETURN
    DIVIDE ( [Doanh thu] - NamTruoc, NamTruoc )

Mẫu MoM (so tháng liền trước)

-- Doanh thu tháng trước (minh hoạ)
Doanh thu tháng trước =
CALCULATE ( [Doanh thu], DATEADD ( DimDate[Date], -1, MONTH ) )

MoM % =
VAR ThangTruoc = [Doanh thu tháng trước]
RETURN
    DIVIDE ( [Doanh thu] - ThangTruoc, ThangTruoc )

Mẫu moving average 3 tháng

Trung bình trượt làm mượt biến động ngắn hạn. DATESINPERIOD lấy khoảng 3 tháng tính đến ngày cuối trong context, sau đó ta chia cho số tháng có dữ liệu:

-- Trung bình trượt doanh thu 3 tháng (minh hoạ)
Doanh thu MA3 =
VAR KhoangNgay =
    DATESINPERIOD (
        DimDate[Date],
        MAX ( DimDate[Date] ),   -- mốc cuối của context
        -3,
        MONTH
    )
VAR TongDT = CALCULATE ( [Doanh thu], KhoangNgay )
VAR SoThang = CALCULATE ( DISTINCTCOUNT ( DimDate[Năm-Tháng] ), KhoangNgay )
RETURN
    DIVIDE ( TongDT, SoThang )

Iterators — họ hàm X

SUM, AVERAGE, MAX... là aggregator: chúng cộng/gộp một cột đã tồn tại. Nhưng nhiều phép tính cần tính theo từng dòng rồi mới tổng hợp — đó là lúc cần iterator (họ hàm kết thúc bằng X): SUMX, AVERAGEX, MAXX, MINX, COUNTX, RANKX.

Iterator hoạt động hai bước: (1) duyệt qua từng dòng của bảng đối số, tạo row context cho mỗi dòng; (2) đánh giá biểu thức trong row context đó; (3) tổng hợp toàn bộ kết quả (cộng với SUMX, lấy trung bình với AVERAGEX...).

Ví dụ kinh điển: doanh thu = số lượng × đơn giá ở từng dòng, rồi cộng lại. Không thể dùng SUM(qty) * SUM(price) vì đó là phép nhân sai (tổng lượng nhân tổng giá).

-- Doanh thu tính đúng theo dòng (minh hoạ)
Doanh thu =
SUMX ( Sales, Sales[qty] * Sales[price] )

-- Trung bình có trọng số: doanh thu / dòng, rồi lấy AVERAGEX
Doanh thu TB mỗi đơn =
AVERAGEX ( Sales, Sales[qty] * Sales[price] )

Khi nào cần iterator? Khi biểu thức phải chạy ở mức dòng: nhân/chia/biến đổi giữa các cột trước khi gộp, tính trọng số, hoặc gộp trên một bảng ảo (xem bên dưới). Nếu chỉ cộng một cột có sẵn, dùng SUM cho gọn và nhanh hơn.

Mẹo hiệu năng: khi lặp trên bảng nhiều dòng nhưng biểu thức chỉ phụ thuộc một cột ít giá trị phân biệt, hãy iterate trên VALUES(cột) thay vì cả bảng để giảm số vòng lặp.

VAR — biến trong DAX

VAR khai báo một biến, tính một lần rồi tái sử dụng. Ba lợi ích:

1. Dễ đọc: đặt tên cho từng bước logic.
2. Hiệu năng: biểu thức lặp lại chỉ tính một lần thay vì mỗi lần xuất hiện.
3. Tránh bẫy bối cảnh: biến "đóng băng" giá trị tại nơi và thời điểm nó được khai báo — rất hữu ích khi so sánh trước/sau khi CALCULATE đổi context.

-- VAR làm rõ và tăng tốc (minh hoạ)
YoY % (VAR) =
VAR DTHienTai = [Doanh thu]
VAR DTNamTruoc = CALCULATE ( [Doanh thu], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
VAR ChenhLech = DTHienTai - DTNamTruoc
RETURN
    DIVIDE ( ChenhLech, DTNamTruoc )

Điểm quan trọng về scope: biến được đánh giá tại chỗ khai báo, không phải tại chỗ dùng trong RETURN. Vì thế nếu bạn khai báo VAR x = [Doanh thu] bên ngoài một CALCULATE, thì x vẫn giữ giá trị của context gốc ngay cả khi dùng lại bên trong CALCULATE — đây là cách sạch để lấy "giá trị trước khi lọc". Ngoài ra, VAR có thể chứa cả giá trị vô hướng lẫn cả bảng.

Virtual tables — bảng ảo làm bộ lọc

DAX có thể tạo bảng "trong bộ nhớ" chưa từng tồn tại trong mô hình, rồi dùng chúng làm đối số cho CALCULATE hoặc iterator. Các hàm chủ lực:

Ví dụ dùng FILTER làm bộ lọc động cho CALCULATE — đếm doanh thu chỉ của các giao dịch lớn:

-- Doanh thu từ đơn giá trị cao (minh hoạ)
Doanh thu đơn lớn =
CALCULATE (
    [Doanh thu],
    FILTER ( Sales, Sales[qty] * Sales[price] > 10000000 )
)

SUMMARIZE + ADDCOLUMNS để dựng một bảng trung gian rồi tổng hợp — mẫu tính "trung bình doanh thu mỗi khách hàng":

-- Doanh thu TB mỗi khách hàng (minh hoạ)
DT TB mỗi khách =
AVERAGEX (
    SUMMARIZE ( Sales, Sales[MaKhachHang] ),  -- bảng ảo: 1 dòng / khách
    CALCULATE ( [Doanh thu] )                 -- tính lại doanh thu trong context từng khách
)

TREATAS hữu ích khi bạn muốn áp bộ lọc từ một bảng không có quan hệ vật lý — ví dụ chuyển danh sách chi nhánh trong một TOPN thành bộ lọc lên fact.

Ranking & Top-N

RANKX — xếp hạng

RANKX là iterator: nó duyệt một bảng, tính giá trị cho từng dòng, rồi trả về thứ hạng của dòng hiện tại. Đối số đầu tiên là bảng cơ sở để so sánh — chọn đúng bảng này quyết định "xếp hạng trong phạm vi nào".

-- Xếp hạng chi nhánh theo doanh thu (minh hoạ)
Hạng chi nhánh =
RANKX (
    ALL ( DimBranch[TenChiNhanh] ),   -- so trên toàn bộ chi nhánh
    [Doanh thu],
    ,
    DESC,
    DENSE
)

Chọn bảng cơ sở:

• ALL(...) — xếp hạng trên toàn bộ danh sách, bỏ qua slicer người dùng.
• ALLSELECTED(...) — xếp hạng trong phạm vi người dùng đã chọn (tôn trọng slicer). Thường đây mới là điều người xem mong đợi.

Hai lưu ý khi dùng RANKX:

• Dòng tổng: dùng HASONEVALUE / ISINSCOPE để không xếp hạng cho dòng "Tổng".
• Số thực (floating point): khi các giá trị rất sát nhau, sai số dấu phẩy động có thể cho hạng sai. Khắc phục bằng ROUND giá trị hoặc dùng kiểu Fixed Decimal.

-- RANKX an toàn cho dòng tổng, tôn trọng slicer (minh hoạ)
Hạng chi nhánh (an toàn) =
IF (
    HASONEVALUE ( DimBranch[TenChiNhanh] ),
    RANKX (
        ALLSELECTED ( DimBranch[TenChiNhanh] ),
        ROUND ( [Doanh thu], 0 ),
        ,
        DESC
    )
)

TOPN — lấy N dòng đầu

TOPN trả về một bảng gồm N dòng đứng đầu theo biểu thức sắp xếp — thường dùng bên trong CALCULATE/SUMX để "chỉ tính cho top N".

-- Doanh thu của Top 10 chi nhánh (minh hoạ)
DT Top 10 chi nhánh =
VAR Top10 =
    TOPN (
        10,
        ALLSELECTED ( DimBranch[TenChiNhanh] ),
        [Doanh thu],
        DESC
    )
RETURN
    CALCULATE ( [Doanh thu], KEEPFILTERS ( Top10 ) )

Cảnh báo thường gặp: TOPN bản thân không tự tôn trọng filter context như bạn tưởng — nó xếp hạng đúng theo bảng bạn truyền vào. Nếu truyền ALL(...) thay vì ALLSELECTED(...), "top 10" sẽ giống nhau ở mọi năm/mọi slicer. Chọn bảng cơ sở cho đúng ý đồ. Ngoài ra TOPN có thể trả nhiều hơn N dòng nếu có đồng hạng ở vị trí thứ N.

Các mẫu tính toán hay dùng

% of total (tỷ trọng)

-- Tỷ trọng doanh thu chi nhánh trên toàn hệ thống (minh hoạ)
% toàn hệ thống =
DIVIDE (
    [Doanh thu],
    CALCULATE ( [Doanh thu], ALL ( DimBranch ) )
)

Running total (lũy kế theo ngày)

-- Lũy kế doanh thu đến ngày hiện tại (minh hoạ)
Lũy kế =
CALCULATE (
    [Doanh thu],
    FILTER ( ALL ( DimDate[Date] ), DimDate[Date] <= MAX ( DimDate[Date] ) )
)

So với ngân sách (variance)

-- Chênh lệch thực hiện so với kế hoạch (minh hoạ)
Chênh lệch KH % =
DIVIDE ( [Doanh thu] - [Ngân sách], [Ngân sách] )

Dynamic measure với SWITCH

Cho phép người dùng chọn chỉ tiêu hiển thị qua một slicer (bảng tham số):

-- Đo lường động theo lựa chọn người dùng (minh hoạ)
Chỉ tiêu động =
SWITCH (
    SELECTEDVALUE ( ThamSo[MaChiTieu] ),
    "DT",  [Doanh thu],
    "YTD", [Doanh thu YTD],
    "YOY", [YoY %],
    "MA3", [Doanh thu MA3],
    BLANK ()
)

Use case thực tế: dashboard doanh thu ngân hàng

Hình dung một dashboard cho ban lãnh đạo một ngân hàng, đặt trên mô hình sao đã dựng ở bài Mô hình dữ liệu: fact GiaoDich, chiều DimDate, DimBranch, DimProduct.

• Thẻ KPI hàng đầu: Doanh thu (tháng đang chọn), Doanh thu YTD (lũy kế năm), YoY % (so cùng kỳ năm trước) — dùng đúng các measure ở trên. Người xem lọc tháng ở slicer, cả ba con số tự dịch cửa sổ thời gian.
• Biểu đồ đường xu hướng: doanh thu theo tháng cùng đường Doanh thu MA3 (trung bình trượt) để làm mượt mùa vụ.
• Bảng Top 10 chi nhánh: cột Doanh thu, Hạng chi nhánh (an toàn) (RANKX theo ALLSELECTED), lọc chỉ giữ hạng ≤ 10 hoặc dùng visual-level filter Top N. Khi lãnh đạo chọn "Miền Bắc" ở slicer, bảng tự xếp lại top 10 trong miền đó.
• Cột tỷ trọng: % toàn hệ thống cho biết mỗi chi nhánh chiếm bao nhiêu phần trăm.
• Nút chọn chỉ tiêu: SWITCH cho phép đổi giữa Doanh thu / YTD / YoY / MA3 mà không cần nhiều biểu đồ.

Tất cả những measure này viết một lần, tự tính lại đúng theo mọi lát cắt — đó là sức mạnh của việc thao túng filter context có kỷ luật.

Hiệu năng DAX (nối tiếp bài storage)

DAX chạy trên hai engine (chi tiết ở bài Chế độ lưu trữ):

• Storage Engine (VertiPaq): nhanh, đa luồng, quét cột nén, chỉ làm phép gộp đơn giản.
• Formula Engine: đơn luồng, xử lý logic phức tạp mà storage engine không làm được.

Nguyên tắc tối ưu:

• Đẩy việc xuống Storage Engine càng nhiều càng tốt; tránh logic hàng-dòng phức tạp bắt Formula Engine gánh.
• Dùng VAR để không tính lại cùng một measure nặng nhiều lần.
• Tránh lồng measure nặng nhiều tầng trong iterator lớn — mỗi vòng lặp có thể kích hoạt lại toàn bộ chuỗi tính.
• Giảm cardinality khi iterate: lặp trên VALUES(cột) thay vì cả bảng fact khi có thể.
• DIVIDE thay cho / để xử lý chia 0 an toàn và thường tối ưu hơn.
• Đo bằng DAX Studio (Server Timings) để biết measure tốn thời gian ở SE hay FE.

Ghi nhớ

• Time intelligence cần bảng ngày liên tục + "Mark as Date Table" — thiếu là sai/lỗi.
• Mọi hàm time intelligence = biến đổi tập ngày trong filter context rồi cho CALCULATE áp dụng.
• TOTALYTD chỉ là đường tắt của CALCULATE + DATESYTD; SAMEPERIODLASTYEAR = DATEADD(-1, YEAR).
• Iterator (hàm X) khi cần tính theo dòng rồi gộp (vd SUMX(Sales, qty*price)); SUM khi chỉ cộng một cột.
• VAR làm code dễ đọc, nhanh hơn và "đóng băng" giá trị theo context tại chỗ khai báo.
• Virtual tables (FILTER, SUMMARIZE, ADDCOLUMNS, TREATAS...) là bộ lọc/đầu vào mạnh cho CALCULATE và iterator.
• RANKX/TOPN: chọn đúng bảng cơ sở — ALL (toàn bộ) vs ALLSELECTED (tôn trọng slicer); xử lý dòng tổng và sai số float.
• Hiệu năng: ưu tiên Storage Engine, dùng VAR, giảm cardinality khi lặp, đo bằng DAX Studio.

Bài liên quan: DAX context: row/filter context & CALCULATE · Mô hình dữ liệu trong Power BI · Chế độ lưu trữ: Import vs DirectQuery & VertiPaq