Hàm ORDERBY và PARTITIONBY trong DAX Power BI

Nếu như bạn đã quen với việc sử dụng các hàm Window function (Lead, Lag) trong SQL, chắc hẳn cũng đã quen thuộc với việc dùng 2 cụm hàm ORDERBY hay PARTITIONBY để sắp xếp và phân vùng của dữ liệu trong SQL. Sau nhiều lời yêu cầu gửi tới Microsoft, vào tháng 12.2022, Microsoft đã chính thức phát hành các hàm DAX Window function mới là (INDEX, OFFSET, WINDOW) và các hàm phụ trợ cho các hàm đó là (ORDERBY hay PARTITIONBY). Trong bài viết này, Datapot sẽ giới thiệu cách sử dụng 2 hàm ORDERBY hay PARTITIONBY.

Cách dùng và syntax trong Power BI 

Các hàm ORDERBY và PARTITIONBY là 2 hàm hỗ trợ cho các hàm Window function (INDEX, OFFSET, WINDOW) và chỉ duy nhất được sử dụng trong các hàm Window function. 

Trong các hàm Window function (OFFSET, INDEX hay WINDOW), vị trí của các hàm ORDER BY hay PARTIONBY thường ở các vị trí cuối của câu lệnh như sau.

WindowFunction(<row-selection>,<table>,<ORDERBY>,<blanks>,<PARTITIONBY>) 

Hàm ORDERBY 

Định nghĩa: Biểu thức giúp xác định thứ tự sắp xếp dữ liệu, trong từng phân vùng của các hàm window function. 

Cấu trúc syntax: 

ORDERBY ( [<orderBy_expression>[, <order>[, <orderBy_expression>[, <order>]] …]] )

Trong đó: 

• OrderBy_expression: Tham số optional (Có thể có hoặc không) là một biểu thức đơn giản hoặc phức tạp được sử dụng để sắp xếp dữ liệu trong mỗi phân vùng của hàm window function.. 
• Order: Tham số optional (Có thể có hoặc không): là một giá trị bao gồm hai phần, gồm”<OrderDirection> <BlankHandling>”. 

• <OrderDirection> xác định cách sắp xếp giá trị của<orderBy_expression> (ví dụ là thứ tự tăng dần (ascending) hoặc thứ tự thấp dần (descending). Trong đó: 

• DESC: Sắp xếp theo thứ tự giảm dần của <orderBy_expression>. 
• ASC: Sắp xếp theo thứ tự tăng dần. Đây là giá trị mặc định nếu không được chỉ định. 

Hàm PARTITIONBY 

Định nghĩa: Biểu thức để xác định các cột được sử dụng để tạo các phân vùng cho tham số <relation> của các hàm window function 

Cấu trúc syntax: 

PARTITIONBY ( [<partitionBy_columnName>[, partitionBy_columnName [, …]]] )

Trong đó: 

• PartitionBy_columnName: Tham số optional (Có thể có hoặc không)là tên của các cột có xuất hiện ở hàm ORDERBY và sẽ được sử dụng để phân vùng dữ liệu trong hàm cửa sổ. 

• Bạn cũng có thể sử dụng RELATED() để tham chiếu đến một cột trong một bảng liên quan đến <relation>. 

Ví dụ minh họa bằng hàm OFFSET 

Đề bài: Từ mô hình dữ liệu gồm 3 bảng Dimension là Customer, Date, Product và 1 bảng Fact Sales với dữ liệu được tóm tắt như hình dưới:  

Viết hàm DAX để lấy giá trị chênh lệch của số lượng giữa 2 lần mua liên tiếp của mỗi Customer. 

Bài giải: Chúng ta sẽ cùng phân tích các bước cần phải thực hiện để giải quyết bài toán như sau: 

Để viết hàm DAX tính giá trị chênh lệch số lượng giữa 2 lần mua liên tiếp của mỗi khách hàng. Chúng ta sẽ cùng phân tích với nhau các bước cần phải thực hiện:  

• Bước 1: Ta cần phải cơ cấu lại bảng Sales với phân vùng riêng cho từng khách hàng và với mỗi phân vùng khách hàng này chúng ta lại tiếp tục sắp xếp theo thứ tự thời gian từ trước đến lần mua hàng gần nhất.  
• Bước 2: Sau đó, ta sẽ viết hàm DAX OFFSET lấy giá trị mua hàng của khách hàng ở thời gian sau trừ đi lần mua hàng ở thời gian ngay liền trước nó  

Tuy nhiên, trong bài viết trước về hàm OFFSET, nếu mọi người đã đọc thì mọi người sẽ biết là nếu chúng ta chỉ đơn giản sử dụng hàm OFFSET mà bỏ qua 2 đối số optional là ORDERBY và PARTITIONBY chúng ta sẽ không thể trả ra kết quả mong muốn.

Ở đây chúng ta phải dẫn ra 2 đối số với ORDERBY: giúp sắp xếp chuỗi thời gian theo chiều từ thời gian sớm nhất đến thời gian gần đây nhất) và PARTITIONBY (giúp phân vùng cho từng khách hàng).

Suy nghĩ đã xong, chúng ta tiến hành áp dụng công thức hàm OFFSET với 2 đối số là ORDERBY và PARTITIONBY.

Trong đó: 

Total Quantity: Thể hiện giá trị tổng số lượng đơn hàng.

Hàm OFFSET với:  

• Delta là – 1 ->Tương đương với việc lùi xuống 1 dòng so với dòng hiện tại  
• Relation: SUMMARIZE(ALLSELECTED(‘Sales’), Customer[Customer], ‘Date'[Date]): tạo một bảng để lấy ra dữ liệu của bảng sales với 2 cột giá trị là Customer và Date. 
• OrderBy là ORDERBY(‘Date'[Date]): Chỉ ra cột dữ liệu Date sẽ được sử dụng để làm thứ tự sắp xếp. Trường hợp không viết gì, mặc định hướng sắp xếp dữ liệu là ASC (tăng dần) 
• PARTITIONBY(Customer[Customer]) Chỉ ra dữ liệu sẽ phân vùng theo từng giá trị của cột Customer. 

Chúng ta cùng thể hiện kết quả ra visual table như sau: 

Kết luận: 

Như chúng ta có thể thấy, 2 hàm ORDERBY và PARTITIONBY là 2 hàm bổ trợ quan trọng trong chuỗi hàm Window Function. Với việc hiểu và sử dụng linh hoạt 2 hàm bổ trợ này một cách thuần thục, chúng ta hoàn toàn có thể lọc, trích xuất, tính toán các dòng dữ liệu theo ý muốn của chúng ta trong tất cả các bảng như trong SQL.

Mong rằng bài viết này đã giải đáp được phần nào đó thắc mắc được cho mọi người về 2 hàm ORDERBY và PARTITIONBY. Ngoài ra, nếu bạn chưa đọc bài về hàm OFFSET của DAX, hãy tìm đọc để hiểu thêm ở đây nhé.

Tìm hiểu về hai hàm còn lại của Window Function là INDEX và WINDOW: Khám phá hàm INDEX và WINDOW trong Power BI