DAX nâng cao: Hàm TREATAS – Mối quan hệ “Ảo” trong Power BI

Trong công việc thực tế, khi xây dựng các mối quan hệ (relationship) giữa các bảng trong data model, đôi khi chúng ta sẽ gặp những tình huống mà 2 bảng không thể xây dựng mối quan hệ one to many do không có cột chung có unique value hoặc do không thể biến đổi mô hình dữ liệu để tạo ra các cột liên kết giữa các bảng. Một ví dụ phổ biến là khi các bảng có mức độ chi tiết khác nhau, ví dụ như Bảng Target hay Budget thường có mức độ chi tiết là theo từng tháng, trong khi đó bảng Sales lại lấy kết quả là theo ngày nên không thể tạo liên kết relationship one to many giữa 2 bảng.

Với việc 2 bảng không có mối quan hệ nào được thiết lập, nhưng chúng ta vẫn muốn tính toán thông qua mối liên kết giữa 2 bảng sẽ khiến chúng ta không trả ra kết quả đúng. Để giải quyết bài toán đó, Microsoft đã phát triển và đưa ra một hàm nâng cao trong DAX là hàm TREATAS.

Công dụng 

TREATAS là hàm được sử dụng để thiết lập một relationship “Ảo” thay thế cho relationship “Thật” giữa hai bảng dựa trên các giá trị khớp giữa các cột được chỉ định. Hàm này cho phép chúng ta đơn giản hóa mô hình dữ liệu của chúng ta nhưng các biểu thức tính toán vẫn linh hoạt và nâng cao giống như có relationship thật. 

Cú pháp 

TREATAS(table_expression, <column>[, <column>[, <column>[,…]]]})

Trong đó: 

• table_expression : Biểu thức hoặc cột cần đánh giá hoặc lọc. 
• column: Một cột hoặc nhiều cột, nhưng không thể là biểu thức.

Giá trị trả về

Một bảng chứa tất cả các dòng trong các cột mà nằm trong biểu thức table_expression.  

Một vài lưu ý

• Số lượng cột được chỉ định phải khớp với số lượng cột trong biểu thức bảng và sắp xếp cùng thứ tự. 
• Nếu một giá trị được trả về trong biểu thức bảng không tồn tại trong cột, nó sẽ bị bỏ qua. Ví dụ, TREATAS({“Red”, “Green”, “Yellow”}, DimProduct[Color]) thiết lập một bộ lọc trên cột DimProduct[Color] với ba giá trị “Red”, “Green” và “Yellow”. Nếu “Yellow” không tồn tại trong DimProduct[Color], các giá trị bộ lọc hiệu quả sẽ là “Red” và “Green”. 
• Hàm TREATAS phù hợp nhất khi không có mối quan hệ giữa các bảng. Nếu bạn có nhiều mối quan hệ giữa các bảng liên quan, hãy xem xét sử dụng USERELATIONSHIP. 
• Chức năng này không được hỗ trợ để sử dụng trong chế độ DirectQuery khi được sử dụng trong các cột tính toán hoặc quy tắc bảo mật cấp độ hàng (RLS). 

Ví dụ minh họa

Chúng ta có một mô hình dữ liệu gồm 3 bảng: Sales, Date và Targets.

Trong đó, bảng Targets có cột thể hiện giá trị thời gian là TargetMonthNo.  

Đề bài: Sử dụng measures trong DAX để viết biểu thức tính toán % đạt được của doanh thu Sales so với giá trị Target.

———————————————– 

Bài giải:

Do 2 bảng Targets với Date có mức độ chi tiết dữ liệu khác nhau (Bảng Targets có cột TargetMonthNo với Bảng Date có cột Date) nên không thể tạo mối quan hệ one to many giữa 2 bảng được.

Measures Target Amount = SUM(Targets[Target]) sẽ đưa ra kết quả hiển thị như hình: 

Tất nhiên đây không phải là kết quả chúng ta mong muốn Vì giá trị 676,210,000 đã được lặp đi lặp lại cho toàn bộ các tháng. 

Để có thể thực hiện yêu cầu bài toán trên chúng ta cần phải xây dựng mối quan hệ “ảo” giữa 2 bảng và từ đó tính toán.

Chúng ta viết các measures mới như sau: 

Trong đó với hàm Total Target: 

Cụm hàm TREATAS được sử dụng như một FILTER expressions cho hàm Calculate với:

Table Expression là SUMMARIZE ( ‘Date’, ‘Date'[Month] ),Targets[TargetMonth].

Khi tiến hành viết hàm TREATAS như vậy, Power BI hiểu là chúng ta xây dựng một kết nối ảo giữa 2 cột Date[Month] vs cột TargetMonthNo.

Chúng ta lần lượt biểu diễn các measures thông qua visual matrix như bảng dưới: 

Đến đây, chúng ta đã ra kết quả cho bài toán. Giá trị %Sales/Target giúp chúng ta xác định được mức độ đạt được của chỉ tiêu doanh thu so với số Target đặt ra.

Bạn có thể tải file Power BI của ví dụ trên mà Datapot đã thực hành tại đây.

So sánh hiệu suất hoạt động (Performance)

Trước khi hàm TREATAS được Microsoft công bố, với bài toán tương tự chúng ta có một vài cách khác để giải quyết như là viết DAX với các các cụm hàm như FILTER và INTERSECT.

Tuy nhiên, trong một phép toán so sánh của nhóm SQLBI, chúng ta đã có một kết quả tính hiệu suất hoạt động khá chênh lệch giữa các kết quả như bảng dưới đây: 

Hàm TREATAS chạy nhanh hơn gần một nửa so với hàm Filter vs Intersect. Tuy nhiên nếu thiết lập mối quan hệ thực có thể được xác định thì khoảng chênh lệch sẽ là gần 69 lần. 

Kết luận 

Sau khi hàm TREATAS được công bố, rất nhiều người đã bỏ qua việc xây dựng các mối quan hệ chằng chịt mà ưu tiên đơn giản hóa data model. Tuy nhiên như chúng mình đề cập ở trên, việc đơn giản hóa này được đánh đổi bằng hiệu quả hoạt động kém hơn đáng kể so với việc xây dựng mối quan hệ relationship thực. Vì vậy, đối với tùy từng bài toán mà bạn hãy cân nhắc kỹ càng trước khi sử dụng TREATAS nhé. 

Mong rằng bài viết trên đã giúp bạn hiểu thêm về TREATAS. Hãy lưu để đọc lại khi cần nhé.  

Tham khảo:  

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

https://learn.microsoft.com/en-us/dax/treatas-function