Hướng dẫn sử dụng hàm OFFSET trong Power BI – Tương tự LEAD, LAG trong SQL

Nếu các bạn là người thường xuyên truy vấn, xử lý dữ liệu bằng SQL, chắc hẳn các bạn đều đã từng gặp các tình huống phải. Ở trong SQL server, chúng ta có 2 window functions rất tiện ích là hàm lead và lag. Còn trên Power BI, khi cần xử lý tình huống này, chúng ta có thể sử dụng hàm rank với một chút khéo léo để có được kết quả, tuy nhiên vẫn khá phức tạp. Tháng 12/2022, Power BI đã giới thiệu đến người dùng hàm OFFSET, một hàm cho phép chúng ta có thể dễ dàng xử lý tình huống trên. Trong bài viết này, Datapot sẽ giới thiệu đến bạn đọc cách sử dụng hàm OFFSET.  

Miêu tả hàm OFFSET trong Power BI

Hàm OFFSET cho phép trả về một giá trị hàng duy nhất – nằm trước hoặc nằm sau – hàng hiện tại trong cùng một bảng, với một độ lệch được chỉ định. Nếu không thể xác định được một giá trị hàng duy nhất cho hàng hiện tại, có thể trả về nhiều hàng.

Cấu trúc syntax 

OFFSET ( <delta>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>] ) 

Trong đó: 

• Delta: Đối số bắt buộc. Là độ lệch chỉ định – là số hàng trước (giá trị âm) hoặc số hàng sau (giá trị dương) so với hàng hiện tại để lấy dữ liệu. Nó có thể là số nguyên hoặc bất kỳ biểu thức DAX nào trả về một giá trị nguyên. 
• Relation: Tham số optional (Có thể có hoặc không). Là một biểu thức dạng bảng chỉ ra cột hoặc bảng chứa giá trị tham chiếu. Nếu chỉ định, tất cả các cột trong <OrderBy> và <partitionBy> phải được lấy từ bảng đó hoặc một bảng liên quan. Nếu bỏ qua: 
• Phải xác định rõ <orderBy>. 
• Tất cả các biểu thức <orderBy> và <partitionBy> phải là tên cột được xác định đầy đủ và lấy 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: Tham số optional (Có thể có hoặc không). Một mệnh đề ORDERBY() chứa các biểu thức để xác định thứ tự sắp xếp của cột hoặc bảng. Nếu bỏ qua: 
• Phải xác định rõ <relation>. 
• Mặc định là sắp xếp theo mọi cột trong <relation> chưa được xác định trong <partitionBy>. 
• PartitionBy: Tham số optional (Có thể có hoặc không). Một mệnh đề PARTITIONBY() chứa các cột xác định cách <relation> được phân vùng thành các nhóm giá trị. Nếu bỏ qua, <relation> được coi là một phân vùng duy nhất. 

Nguồn tham khảo: https://learn.microsoft.com/en-us/dax/offset-function-dax

Ví dụ minh họa: 

Đề bài: Có 1 bảng Order gồm 2 cột: OrderDate, và Acc_Revenue (doanh thu cộng dồn) theo date. Sử dụng hàm OFFSET bằng 2 cách Calculated Column và Measure trong Power BI để tính toán Revenue bán được của từng ngày. 

Bài giải: Với yêu cầu bài toán là cần tính toán được doanh thu theo từng ngày.  

Chúng ta có thể xây dựng công thức là: 

Doanh thu theo ngày hôm nay = Doanh thu cộng dồn hôm nay – doanh thu cộng dồn hôm trước  

1. Sử dụng Calculated Columns.  

Để viết Calculated Columns bằng hàm OFFSET, chúng ta vào Data View, chọn New column 

Áp dụng công thức tính ở trên và syntax của hàm OFFSET, chúng ta có thể viết hàm DAX cho Calculated Column Revenue như sau:

Trong đó: 

Sales[Acc_Revenue]: Thể hiện giá trị doanh thu cộng dồn của dòng hiện tại 

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 là ALLSELECTED(Sales[Acc_Revenue]): Lấy cột dữ liệu Acc_Revenue làm tham chiếu để lấy giá trị.
• OrderBy là (Sales[Acc_Revenue],ASC): Chỉ ra cột dữ liệu được sử dụng làm để thứ tự sắp xếp và hướng sắp xếp.
• Kết quả hiển thị của Calculated Column đã giống yêu cầu của đề bài:

2. Sử dụng Measures.  

Đầu tiên, để xử lý bài toán bằng Measure, chúng ta xác định lại các thành phần biểu thức: 

Doanh thu cộng dồn ngày hiện tại: MAX(Sales[Acc_Revenue]) 

Doanh thu cộng dồn ngày hôm trước: Sử dụng hàm CALCULATE với doanh thu cộng dồn ngày hiện tại, kết hợp với hàm OFFSET  

Kết hợp lại, chúng ta có measure OFFSET MEASURE như sau: 

Kết quả hiển thị của OFFSET Measure đã thể hiện đúng đầu ra của bài toán:

Lưu ý:

Khi chúng ta viết Measure bằng hàm OFFSET với sắp xếp thứ tự theo Time Intelligence.  

Chúng ta cần phải lưu ý khi viết syntax 2 phần: RELATION VÀ ORDERBY phải đưa vào giá trị cột được chỉ định là cột “Sort by column”.

Ví dụ minh họa: Trong bảng Date có cột Date được “Sort by column” là cột Month Number.

Hàm Measure có thể được viết như sau: 

Kết luận: 

Như chúng ta có thể thấy, Hàm OFFSET là một hàm nằm trong chuỗi hàm Windown Function rất tiện dụng. Bằng việc xác định các giá trị cột tham chiếu hay cách sắp xếp, chúng ta hoàn toàn có thể tìm kiếm các giá trị tiếp theo nó hay phía trước nó, nhằm giải quyết các bài toán trong thực tế của chúng ta. Trong bài viết sắp tới, chúng mình sẽ tiếp tục phân tích sâu hơn về 2 hàm DAX là hàm OrderBy và hàm PartitionBy để mọi người hiểu rõ hơn về hàm.

Hy vọng rằng bài viết này đã giải đáp được thắc mắc được cho mọi người. Hãy lưu lại bài viết để tra cứu khi cần nhé.