hàm INDEX và WINDOW trong Power BI

Khám phá hàm INDEX và WINDOW trong Power BI

Ngày đăng: 03/07/2023

Với những ai thường xuyên truy vấn dữ liệu bằng ngôn ngữ SQL, chắc hẳn không còn xa lạ với hai hàm LEAD, LAG. Hai hàm này cho phép chúng ta lấy ra các giá trị của các dòng đứng trước hoặc giá trị ở một vị trí cố định. Vào tháng 12/2022, Microsoft cũng đã phát hành cụm hàm DAX WINDOW FUNCTIONS giúp người dùng có thể thực hiện điều tương tự trong Power BI.

Nếu như bạn chưa tìm hiểu về Window Functions, hãy dành vài phút đọc lại 2 bài viết dưới đây nhé. 

  1. Hướng dẫn sử dụng hàm OFFSET trong Power BI 
  1. Hàm ORDERBY vs hàm PARTITIONBY trong DAX Power BI 

Trong bài viết này, chúng ta sẽ tìm hiểu về 2 hàm là INDEX (lấy giá trị của một dòng ở một vị trí xác định) và WINDOW (lấy giá trị của nhiều dòng linh hoạt).

Tổng quan hai hàm INDEX và WINDOW 

Hàm INDEX 

Mô tả: 

Trả về một dòng ở vị trí cụ thể, được chỉ định bởi tham số vị trí (position), trong phân vùng cụ thể (partition), đã được sắp xếp theo thứ tự được chỉ định (orderby). Nếu không thể xác định phân vùng hiện tại thành một phân vùng duy nhất, nó có thể trả về nhiều dòng dữ liệu. 

Cú pháp: 

INDEX(<position>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>])

Trong đó: 

Position: (Giá trị bắt buộc) là vị trí tuyệt đối (dựa trên dãy số nguyên) để lấy dữ liệu: 

  • Nếu <position> là số dương: 1 là dòng đầu tiên, 2 là dòng thứ hai, và cứ tiếp tục. 
  • Nếu <position> là số âm: -1 là dòng cuối cùng, -2 là dòng thứ hai từ cuối, và cứ tiếp tục. 
  • Khi <position> vượt quá ranh giới, hoặc bằng không, hoặc là BLANK(), INDEX sẽ trả về một bảng trống. <position> có thể là bất kỳ biểu thức DAX nào trả về một giá trị duy nhất. 

Relation: (Giá trị lựa chọn) Một biểu thức trả ra kết quả là một bảng. Nếu được chỉ định, tất cả các cột trong <partitionBy> phải xuất phát từ bảng đó hoặc bảng liên quan. Nếu bỏ qua không lựa chọn: 

  • <orderBy> phải được chỉ định một cách rõ ràng. 
  • Tất cả các biểu thức <orderBy> và <partitionBy> phải là tên cột đầy đủ và xuất phát từ một bảng duy nhất. 
  • Mặc định là ALLSELECTED() của tất cả các cột trong <orderBy> và <partitionBy>. 

OrderBy: (Giá trị lựa chọn) Mặc định là cách sắp xếp theo thứ tự tất cả các cột trong <relation> mà chưa được chỉ định trong <partitionBy>. 

Blanks: (Giá trị lựa chọn) Một danh sách định nghĩa cách xử lý giá trị trống khi sắp xếp. Tham số này được dành cho việc sử dụng trong tương lai. Hiện tại, giá trị được hỗ trợ duy nhất là KEEP (mặc định). 

PartitionBy: (Giá trị lựa chọn) Một biểu thức PARTITIONBY() chứa các cột xác định cách <relation> được phân vùng. Nếu bỏ qua, <relation> được xem như một phân vùng duy nhất. 

MatchBy: (Giá trị lựa chọn)) Một mệnh đề MATCHBY() chứa các cột xác định cách khớp dữ liệu và xác định dòng hiện tại. 

Hàm WINDOW 

Mô tả: 

Trả về nhiều dòng nằm trong khoảng giới hạn đã được xác định. 

Cú pháp: 

WINDOW ( from[, from_type], to[, to_type][, <relation>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>] )

Trong đó: 

From: (Giá trị bắt buộc) Chỉ định vị trí bắt đầu của hàm. Điều này có thể là bất kỳ biểu thức DAX nào trả về một giá trị duy nhất là số nguyên. Hành vi của  phụ thuộc vào tham số <from_type>: 

  • Nếu <from type> là REL (Relevant), số dòng dịch chuyển xuống (giá trị âm) hoặc dịch chuyển lên (giá trị dương) từ dòng hiện tại để lấy dóng dữ liệu đầu tiên trong hàm. 
  • Nếu <from type> là ABS (Abosulte) và <from> là số dương, thì đó là vị trí bắt đầu của cửa sổ từ đầu phân vùng của bảng. Đánh số bắt đầu từ 1. Ví dụ, 1 có nghĩa là hàm sẽ lấy dòng bắt đầu từ dòng đầu tiên của phân vùng. Nếu <from> là số âm, thì đó là vị trí bắt đầu của hàm ở cuối phân vùng của bảng. -1 có nghĩa là dòng cuối cùng trong phân vùng 

To: (Giá trị bắt buộc) Tương tự như <from>, nhưng chỉ định vị trí kết thúc của dòng. Dòng cuối cùng được bao gồm trong hàm. 

From type và to_type: (Giá trị bắt buộc)  Tương tự như <from_type>, nhưng sửa đổi hành vi của <to>. 

Relation: (Thông tin tùy chọn) Một biểu thức bảng từ đó bảng đầu ra được trả về. Nếu được chỉ định, tất cả các cột trong <partitionBy> phải xuất phát từ đó hoặc từ một bảng liên quan. Nếu bỏ qua: 

  • <orderBy> phải được chỉ định một cách rõ ràng. 
  • Tất cả các biểu thức <orderBy> và <partitionBy> phải là tên cột đầy đủ và xuất phát từ một bảng duy nhất. 
  • Mặc định là ALLSELECTED() của tất cả các cột trong <orderBy> và <partitionBy>. (Thông tin tùy chọn) Một mệnh đề ORDERBY() chứa các biểu thức xác định cách mỗi phân vùng được sắp xếp. 
  • Nếu bỏ qua: <relation> phải được chỉ định một cách rõ ràng. 

OrderBy: (Giá trị lựa chọn) Một mệnh đề ORDERBY() chứa các biểu thức xác định cách mỗi phân vùng được sắp xếp. Nếu bỏ qua: 

  • <relation> phải được chỉ định một cách rõ ràng. 
  • Mặc định là sắp xếp theo tất cả các cột trong <relation> mà chưa được chỉ định trong <partitionBy>. 

Blanks: (Giá trị lựa chọn) Một danh sách định nghĩa cách xử lý giá trị trống khi sắp xếp. Tham số này được dành cho việc sử dụng trong tương lai. Hiện tại, giá trị được hỗ trợ duy nhất là KEEP (mặc định). 

PartitionBy: (Giá trị lựa chọn) Một mệnh đề PARTITIONBY() chứa các cột xác định cách <relation> được phân vùng. Nếu bỏ qua, <relation> được xem như một phân vùng duy nhất. 

MatchBy: (Giá trị lựa chọn) mệnh đề MATCHBY() chứa các cột xác định cách khớp dữ liệu và xác định dòng hiện tại. 

Ví dụ minh họa 

Đề bài 1: Từ mô hình dữ liệu gồm 3 bảng Sales và Product và Date với liên kết với nhau thông qua mối quan hệ one-many như hình dưới.

Tổng quan hai hàm INDEX và WINDOW 

1. Tính toán giá trị chênh lệch của Total Quantity của các Subcategory vs 1st Sub Category. 

2. Tính toán Moving Average của Total Quantity với các tháng. 

Hướng dẫn giải: 

*Với yêu cầu tính toán giá trị chênh lệch của Total Quantity của các Subcategory vs 1st Sub Category (ví dụ như bảng dưới) 

Subcategory Total Sales Total Sales 1st SubCategory Total Sales compare with orthes sub 
Bib-Shorts $167,171.79  $167,171.79  0 
Bike Racks $182,007.60  $167,171.79  14835.81 
Bike Stands   $167,171.79  -167171.79 
Bottles and Cages $6,885.93  $167,171.79  -160285.86 
Bottom Brackets $44,444.93  $167,171.79  -122726.86 
Brakes $60,183.15  $167,171.79  -106988.64 
Caps $30,658.42  $167,171.79  -136513.37 
Chains $8,363  $167,171.79  -158808.79 
Cleaners $10,179.94  $167,171.79  -156991.85 
Cranksets $183,123.06  $167,171.79  15951.27 
Derailleurs $61,629.35  $167,171.79  -105542.44 
Fenders   $167,171.79  -167171.79 
Forks $77,966.80  $167,171.79  -89204.99 
Gloves $207,372.85  $167,171.79  40201.06 

Để tiến hành tính toán so sánh giữa giá trị Total Quantity của current Sub Category với Total Quantity của 1st SubCategory. Chúng ta có thể suy nghĩ tới bài trước là sử dụng hàm OFFSET. 

Với hàm OFFSET, chúng ta có thể lấy một dòng giá trị bên trên hoặc dưới dòng hiện tại (current row). Tuy nhiên ở đây bài toán yêu cầu là chúng ta cần lấy giá trị của SubCategory đầu tiên.

Vì vậy chúng ta chỉ có thể áp dụng công thức hàm INDEX (lấy giá trị ở một vị trí cố định) như sau: 

Trong đó: 

Với Measure Total Sales = SUM(Sales[Sales]) 

+ Position: (1) Lấy ra dòng đầu tiên của bảng theo sắp xếp bảng kí tự của bảng Relation 

+ Relation: sử dụng hàm ALLSELETED cho toàn bộ cột SubCategory để tạo một bảng đầy đủ các giá trị SubCategory 

minh họa hàm INDEX và WINDOW 

*Với yêu cầu tính toán đường trung bình trượt 3 tháng (Moving Average) của Total Quantity  

Để tính đường trung bình trượt 3 tháng (Moving Average 3M), chúng ta cần kết hợp 2 cụm hàm là AverageX để tính trung bình và hàm Window để lấy 3 dòng giá trị Total Quantity của 3 tháng gần nhất tính tới dòng hiện tại.  

Trong đó: 

FROM – TO: -2, 0: Lấy giá trị của 3 tháng gần nhất (bao gồm cả current row) để tính toán.  

Type: REL do các dòng dữ liệu được lấy linh hoạt theo current row nên ta lựa chọn REL 

RELATION: Sử dụng hàm Summize để tạo một bảng mô phỏng với toàn bộ các hàm Date liên quan 

ORDERY: Cách sắp xếp hàm, lưu ý cần phải đưa toàn bộ các cột sử dụng giá trị Sort to (Cột Month đang được sort by cột MonthNu) 

ví dụ hàm INDEX và WINDOW 

Biểu diễn giá trị Total Sales 3MA theo Line Chart như bảng dưới.  

Hàm WIndow trong Power BI

Kiến thức nâng cao: Trong trường hợp chúng ta cần lấy giá trị trung bình trượt của không phải 3 tháng mà có thể là các tháng khác nhau (như 1 tháng hay 5 tháng).

Chúng ta hoàn toàn có thể kết hợp Field Parameters để điều chỉnh độ rộng lớn của Moving Average. Không phải chỉ 3 tháng mà có thể từ 1-12 tháng.

kết hợp Field Parameters

Bạn có thể xem bài viết hướng dẫn sử dụng Fields Parameters của chúng mình tại đây. 

Sau khi kết hợp với Field Parameters để viết, chúng ta sẽ biến đổi công thức thành là:  

Với giá trị SMA Months Value sẽ được điều chỉnh trên Parameters như hình bên dưới: 

Hàm Window

Đề bài 2: Vẽ đường Average Line để thể hiện giá trị trung bình thay đổi qua từng năm của Total Quantity. 

Hướng dẫn giải:  

Để vẽ đường Average Line cho Line Chart, chúng ta hoàn toàn có thể sử dụng tính năng Analytics feature – Average Line của Power BI như bên dưới. 

Analytics feature – Average Line của Power BI
Analytics feature – Average Line của Power BI

Tuy nhiên nhìn vào biểu đổ ở đây, ta chỉ thấy duy nhất một dòng Average line cho toàn bộ chu kỳ thời gian. Nhưng khi nhìn kỹ, chúng ta sẽ thấy từng năm số lượng Total Quantity đều đang tăng trưởng.  

Có lẽ việc sử dụng một đường line cho toàn bộ các năm là không đủ tin cậy để chúng ta đưa ra quyết định.  

Lúc này, chúng ta hoàn toàn có thể áp dụng hàm DAX Window để viết lên một đường line có thể nhìn được giá trị trung bình của từng năm như sau: 

Đưa hàm Total Sales Year Avg, ta có visual mới hoàn thiện như sau: 

áp dụng hàm DAX Window

Đến đây chúng ta đã có thể xem được Average Line cho từng năm. 

Tổng kết 

Để tóm lại nội dung về 5 hàm Window Functions trong DAX (3 hàm chính: OFFSET, INDEX, WINDOW và 2 hàm phụ: ORDERBY, PARTITIONBY), chúng ta có thể tóm tắt nội dụng của tất cả các hàm như sau: 

  1. Lấy tất cả các dòng của bảng được chỉ định bởi tham số <table>.  
  2. Chia các dòng thành các phân vùng riêng biệt bằng các giá trị duy nhất của các cột Partition-by.  
  3. Sắp xếp các dòng trong mỗi phân vùng theo các cột order-by và hướng sắp xếp. Xác định phân vùng hiện tại và, nếu cần, dòng hiện tại trong phân vùng.  
  4. Trả về 0, 1 hoặc nhiều dòng trong phân vùng hiện tại. 
  • OFFSET trả về 0 hoặc 1 dòng ở một khoảng cách nhất định từ dòng hiện tại.  
  • INDEX trả về 0 hoặc 1 dòng ở một vị trí cố định trong phân vùng hiện tại.  
  • WINDOW trả về tất cả các dòng nằm giữa giới hạn dưới và giới hạn trên. Một trong hai giới hạn là một dòng ở một khoảng cách nhất định từ dòng hiện tại hoặc ở một vị trí cố định trong phân vùng hiện tại. 

Hy vọng rằng những tóm tắt và nội dung của chuỗi 3 bài viết trên đã giúp tất cả mọi người hiểu rõ hơn về hàm Window Functions. Cảm ơn bạn đã ghé thăm và đọc bài viết này. Hãy lưu bài viết lại để sử dụng ngay khi cần nhé.  

Chia sẻ bài viết này

Bài viết liên quan

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *