DAX #005 – Lock TopN Ranking with Time Intelligence

→ Sếp bạn muốn xem Top N (= 3) khách hàng VIP nhất dựa trên doanh số cả năm 2024 (Date Slicer).
→ Sau đó, muốn giữ lại cố định (đóng băng) danh sách Top N này, và lọc tiếp theo các Time Intelligence: YTD, QTD (Calculation Group) và các Slicer khác.

→ Power BI tự động tính Top N theo đúng lựa chọn trên Time Intelligence (Calculation Group) & Slicer → Không đúng ý của sếp là phải đóng băng danh sách lại cho dù có lựa chọn gì khác.

→ Vấn đề tiếp theo: Sếp muốn xem thêm khách hàng trong danh sách đóng băng đang thuộc Region nào. Nhưng khi thêm cột Region vào báo cáo thì mỗi vùng lại tính Top 3 riêng (Không đúng danh sách đóng băng ban đầu yêu cầu)

→ Tôi muốn danh sách Top N (=3) cố định theo cả năm.
→ Bất kể chọn tháng nào, chọn YTD hay QTD, hay drill-down vào vùng nào, thì 3 ông đó vẫn phải hiện.
→ Chỉ thay đổi giá trị (Sales) theo context thôi, chứ khách hàng không được thay đổi.

Output Mẫu:

Slicer đang chọn: Tháng 7 – Time Intelligence = Current – Top N = 10

→ #1 ▲ (+40): Top #1 năm đang xét. Tăng 40 bậc so với năm trước (Năm trước rank #41, năm nay rank #1)
→ #8 ▼ (-3): Top #8 năm đang xét. Giảm 3 bậc so với năm trước (Năm trước rank #5, năm nay rank #8)
→ 7 —: Top #7 năm đang xét. Giữ nguyên thứ hạng (không đổi)
→ 5 ★ NEW: Top #5 năm nay. Khách hàng mới (năm trước không có)

So sánh: Rank Change vs Rank Context

→ Khách hàng 10:

• Rank Change = #9 ▼ (-3):
  • Đây là thứ hạng trong cả năm đang xét (danh sách khách hàng này sẽ được cố định lại).
  • So với năm trước, khách hàng giảm 3 bậc.
  • Rank Change = Thứ hạng “Đóng Băng” (cả năm đang xét, so với năm trước)
• Rank Context = #15:
  • Đây là thứ hạng trong tháng 7 (dựa trên context đang lọc hiện tại).
  • Trong tháng 7, tại Time Intelligence = Current thì khách hàng này chỉ đứng #15.
  • Nhưng cả năm vẫn là #9 nên vẫn xuất hiện trong Top 10 cố định.
  • Rank Context = Thứ hạng “Dynamic” (thay đổi theo context đang chọn)

Khi bạn thay đổi Time Intelligence thì danh sách khách hàng trên vẫn sẽ cố định, chỉ có giá trị là thay đổi.

Total Sales = SUM(Fact_Sales[SalesAmount])

Anchor Sales Full Year = 
VAR _SelectedYear = MAX('Dim_Date'[Year])
VAR _Result = 
    CALCULATE(
        [Total Sales], 
        ALL('Dim_Date'),                     
        'Dim_Date'[Year] = _SelectedYear,   
        REMOVEFILTERS('Time Calculation'),   
        REMOVEFILTERS(Dim_Product)          
    )
RETURN
    _Result

Sales Top N Locked = 
VAR _TopN_Value = [Top N Value] 
VAR _TopCustomers = 
    TOPN( 
        _TopN_Value,
        ALL(Dim_Customer),
        [Anchor Sales Full Year], 
        DESC
    )
VAR _IsVisible = 
    CALCULATE(
        COUNTROWS(Dim_Customer), 
        KEEPFILTERS(_TopCustomers)
    ) > 0

RETURN 
    IF(
        _IsVisible,
        CALCULATE(
            COALESCE([Total Sales], 0),
            KEEPFILTERS(_TopCustomers)
        ),
        BLANK() 
    )

Anchor Sales PY = 
VAR _SelectedYear = MAX('Dim_Date'[Year])
RETURN
    CALCULATE(
        [Total Sales],
        ALL('Dim_Date'),
        'Dim_Date'[Year] = _SelectedYear - 1,
        REMOVEFILTERS('Time Calculation')
    )

Rank Change = 
IF(
    NOT ISBLANK([Sales Top N Locked]) && ISINSCOPE(Dim_Customer[CustomerName]), 
    VAR _AnchorPY = [Anchor Sales PY]
    VAR _CurrentRank = 
        RANKX(
            ALL(Dim_Customer), 
            [Anchor Sales Full Year], 
            , DESC, Dense
        )
    VAR _PrevRank = 
        RANKX(
            ALL(Dim_Customer), 
            [Anchor Sales PY], 
            , DESC, Dense
        )    
    VAR _Diff = _PrevRank - _CurrentRank 
    VAR _Icon = 
        SWITCH(TRUE(),
            ISBLANK(_AnchorPY) || _AnchorPY = 0, "★ NEW", 
            _Diff > 0, "▲ (+" & _Diff & ")", 
            _Diff < 0, "▼ (" & _Diff & ")", 
            "—" 
        )    
    RETURN "#" & _CurrentRank & " " & _Icon
)

Giả sử báo cáo đang ở trạng thái sau:

• Slicer Year: 2024.
• Matrix Visual: Đang hiển thị danh sách khách hàng.
• Top N: Chọn Top 2.

Trong data có 3 khách hàng:

Customer	Sales 2024 (Full Year)	Sales 2023 (PY)	Ghi chú
Cust A	500	400	(Top 2 năm nay)
Cust B	600	300	(Top 1 năm nay)
Cust C	200	500	(Top 3 – Bị loại khỏi list Locked)

TRƯỜNG HỢP 1: Tại dòng của “Cust A” (Khách hàng VIP)

BƯỚC 1: Đánh giá điều kiện IF

Power BI đứng tại ô Rank Change của dòng Cust A.

• ISINSCOPE(...): TRUE (Đang ở dòng chi tiết).
• NOT ISBLANK([Sales Top N Locked]):
  • Measure Sales Top N Locked kiểm tra xem A có trong Top 2 không? -> Sales 500 (Hạng 2) -> Có.
  • Kết quả: TRUE.
  • ACTION: Điều kiện thỏa, BẮT ĐẦU đi vào tính toán các biến VAR bên trong.

BƯỚC 2: Tính Rank Năm Nay (_CurrentRank)

Hàm RANKX(ALL(Dim_Customer), [Anchor Sales Full Year]) bắt đầu chạy:

• Tạo Bảng Ảo: ALL(Dim_Customer) -> List danh sách {A, B, C}.
• Vòng Lặp (Iteration) & Context Transition:
  • Xét A: Chuyển Filter -> Tính Sales 2024 của A -> 500.
  • Xét B: Chuyển Filter -> Tính Sales 2024 của B -> 600.
  • Xét C: Chuyển Filter -> Tính Sales 2024 của C -> 200.
• Xếp Hạng: Danh sách điểm số {600, 500, 200}.
  • Giá trị hiện tại của A là 500.
  • So sánh: 500 đứng thứ 2.
  • _CurrentRank = 2.

BƯỚC 3: Tính Rank Năm Ngoái (_PrevRank)

Hàm RANKX(ALL(Dim_Customer), [Anchor Sales PY]) bắt đầu chạy:

• Tạo Bảng Ảo: Tương tự, list danh sách {A, B, C}.
• Vòng Lặp (Iteration) & Context Transition:
  • Xét A: Chuyển Filter -> Tính Sales 2023 (PY) của A -> 400.
  • Xét B: Chuyển Filter -> Tính Sales 2023 (PY) của B -> 300.
  • Xét C: Chuyển Filter -> Tính Sales 2023 (PY) của C -> 500.
• Xếp Hạng: Danh sách điểm số theo thứ tự giảm dần {500 (C), 400 (A), 300 (B)}.
  • Giá trị hiện tại (PY) của A là 400.
  • So sánh: 400 đứng thứ 2.
  • _PrevRank = 2.

BƯỚC 4: Tính toán Logic Icon

• _Diff = _PrevRank (2) – _CurrentRank (2) = 0.
• SWITCH check:
  • ISBLANK(_AnchorPY)? -> False (400).
  • _Diff > 0? -> False.
  • _Diff < 0? -> False.
  • Default: -> "—".
• RETURN: "#2 —".

TRƯỜNG HỢP 2: Tại dòng của “Cust C” (Khách hàng thường – Non VIP)

BƯỚC 1: Đánh giá điều kiện IF

Power BI đứng tại ô Rank Change của dòng Cust C.

• ISINSCOPE(...): TRUE.
• NOT ISBLANK([Sales Top N Locked]):
  • Measure Sales Top N Locked kiểm tra C có trong Top 2 không? -> Sales 200 (Hạng 3) -> Không.
  • Measure trả về BLANK.
  • Điều kiện NOT ISBLANK(...) trả về FALSE.

BƯỚC 2: Ngắt Mạch

• Do điều kiện IF là FALSE.
• Power BI DỪNG NGAY LẬP TỨC.
• Nó KHÔNG chạy RANKX cho năm nay.
• Nó KHÔNG chạy RANKX cho năm ngoái.
• Nó trả về BLANK() ngay lập tức.

Rank Context = 
IF(
    ISINSCOPE(Dim_Customer[CustomerName]) && NOT ISBLANK([Sales Top N Locked]), 
    VAR _RankInMonth = 
        RANKX(
            ALL(Dim_Customer), 
            [Total Sales],
            , DESC, Dense
        )
    RETURN 
        "#" & _RankInMonth
)

Giả sử báo cáo đang ở trạng thái sau:

• Slicer: Month = Tháng 7 (Context bên ngoài).
• Visual Matrix: Đang hiển thị dòng của khách hàng tên là “Cust A”.
• Data:
  • “Cust A” có Sales tháng 7 = 100 (Thuộc Top N Locked).
  • “Cust B” có Sales tháng 7 = 200.
  • “Cust C” có Sales tháng 7 = 50.
  • Tổng cộng có 3 khách hàng này trong data (để đơn giản hóa).

BƯỚC 1: Đánh giá điều kiện IF (Evaluation Context)

Power BI đứng tại ô của “Cust A”.

• ISINSCOPE(Dim_Customer[CustomerName]): Có đang group theo tên không? YES.
• NOT ISBLANK([Sales Top N Locked]): “Cust A” có thuộc danh sách bị Lock không? YES.
• Kết luận: Điều kiện TRUE → Tiếp tục đi vào tính toán biến _RankInMonth.

BƯỚC 2: Khởi tạo vòng lặp RANKX

• Input Table: ALL(Dim_Customer) gỡ bỏ filter “Cust A”.
• Bảng ảo được tạo ra trong RAM chứa 3 dòng: { Cust A, Cust B, Cust C }.
• Current Value: Xác định giá trị của “Cust A” (context hiện tại của visual) để đem đi so sánh.
  • Tính [Total Sales] cho “Cust A” trong Tháng 7 = 100.

BƯỚC 3: Iteration & Context Transition (Trái tim của RANKX)

RANKX bắt đầu chạy vòng lặp qua bảng ảo 3 dòng kia để tính điểm cho từng người:

• Lần lặp 1 – Xét Cust A:
  • Row Context: Đang cầm dòng “Cust A”.
  • Context Transition: Biến dòng này thành Filter Dim_Customer[Name] = "Cust A".
  • Calculate: Tính [Total Sales] với filter (Tháng 7 +Cust A) = 100.
• Lần lặp 2 – Xét Cust B:
  • Row Context: Đang cầm dòng “Cust B”.
  • Context Transition: Biến dòng này thành Filter Dim_Customer[Name] = "Cust B".
  • Calculate: Tính [Total Sales] với filter (Tháng 7 + Cust B) = 200.
• Lần lặp 3 – Xét Cust C:
  • Row Context: Đang cầm dòng “Cust C”.
  • Context Transition: Biến dòng này thành Filter Dim_Customer[Name] = "Cust C".
  • Calculate: Tính [Total Sales] với filter (Tháng 7 + Cust C) = 50.

BƯỚC 4: Sắp xếp và Gán hạng

Sau khi tính xong, RANKX có danh sách điểm số:

• Cust B: 200
• Cust A: 100
• Cust C: 50

• Giá trị gốc của visual (“Cust A” = 100) được đem so sánh với danh sách này.
• 100 đứng thứ 2 trong danh sách giảm dần.
• Kết quả _RankInMonth = 2.

BƯỚC 5: Trả về kết quả (Return)

• Hàm nối chuỗi: "#" & 2.
• Kết quả cuối cùng hiển thị trên Visual: "#2".