CALCULATE Filters Fundamentals

(Nguồn: Sqlbi.com)

Khi bạn viết câu lệnh CALCULATE, đối số lọc phía sau luôn trả về một table, ví dụ như một danh sách các giá trị của một hoặc nhiều cột hoặc cả một bảng.

Cụ thể, nếu bạn viết một công thức như bên dưới để lọc sản phẩm có màu Blue hoặc Red

[Sales Red or Blue] :=

CALCULATE (

[Sales Amount],

Product[Color] = "Red" || Product[Color] = "Blue"

)

Thực tế đối số lọc ở phía sau sẽ được trả về dưới dạng

[Sales Red or Blue] :=

CALCULATE (

[Sales Amount],

FILTER (

ALL ( Product[Color] ),

Product[Color] = "Red" || Product[Color] = "Blue"

)

)

Bản chất là công thức gốc là đối số lọc phía sau đã chuyển đổi về một table có 1 cột color. Nhưng vì công thức 1 đơn giản hơn nên mọi người hay dùng và chính vì điều này làm cho đa số hiểu sai về đối số lọc về phía sau. Lỗi sai này bạn có thể thấy ở ví dụ bên dưới:

[Sales Red or Contoso - invalid] :=

CALCULATE (

[Sales Amount],

'Product'[Color] = "Red" || 'Product'[Brand] = "Contoso"

Với bộ lọc gồm 2 điều kiện ở hai cột, công thức sẽ bị lỗi và không cho ra kết quả. Lúc này chúng ta phải quay trở về yêu cầu ban đầu với đối số lọc là một table có 2 cột để lọc gồm Color và Brand. Công thức bên dưới là một giải pháp để cho ra kết quả theo yêu cầu :

* Table Filter

[Sales Red or Contoso - table filter] :=

CALCULATE (

[Sales Amount],

FILTER (

'Product',

'Product'[Color] = "Red" || 'Product'[Brand] = "Contoso"

)

)

Sử dụng luôn table Product chứa cả 2 cột Color và Brand để làm đối số lọc để cho ra kết quả nhưng giải pháp trên chưa phải là tối ưu. Một giải pháp tốt hơn như bên dưới :

* ALL Columns Filter

[Sales Red or Contoso - ALL columns filter] :=

CALCULATE (

[Sales Amount],

FILTER (

ALL ( 'Product'[Color], 'Product'[Brand] ),

'Product'[Color] = "Red" || 'Product'[Brand] = "Contoso"

)

)

Bạn có thể tạo một bộ lọc với chỉ 2 cột (cần lọc) bằng cách tạo một table có 2 cột bạn cần lọc bằng cách sử dụng hàm ALL. Với công thức ALL ở trên bạn sẽ có kết hợp unique các giá trị tồn tại trong 2 cột.

Tình huống này tối ưu hơn ở trên vì lọc một table là sự kết hợp giá trị của 2 cột thông tin sẽ ít hơn nhiều so với lọc cả một table Product với nhiều cột và dòng hơn. Một giải pháp khác cũng gần giống cách trên là sử dụng hàm CROSSJOIN để kết hợp tất cả các giá trị có thể của 2 cột Color và Brand mà không quan tâm chúng có tồn tại kết hợp hay không :

* CROSSJOIN Columns Filter

[Sales Red or Contoso - CROSSJOIN columns filter] :=

CALCULATE (

[Sales Amount],

FILTER (

CROSSJOIN (

ALL ( 'Product'[Color] ),

ALL ( 'Product'[Brand] )

),

'Product'[Color] = "Red" || 'Product'[Brand] = "Contoso"

)

)

Về peformance của CROSSJOIN hay ALL cái nào tốt hơn thì nó sẽ tùy vào mức độ chi tiết của dữ liệu, hơn ai hết bạn sẽ là người hiểu nhất về dữ liệu mình đang có. Ở ví dụ này trong môt số tình huống nếu mức độ chi tiết của Color và Brand càng lớn thì khả năng với CROSSJOIN sẽ tạo ra kết quả kết quả tương quan nhiều hơn so với thực tế nhưng bạn cần chú ý đến 2 ưu điểm của CROSSJOIN để lựa chọn cho phù hợp là : (1) CROSSJOIN sẽ ko cần scan cả table lọc để tìm kiếm sự kết hợp mà đơn giản chỉ là tạo ra một sự kết hợp giữa 2 cột sẵn có và (2) CROSSJOIN có thể kết hợp các cột giữa các table khác nhau trong khi ALL thì không sử dụng được.

Một nhược điểm của ALL và CROSSJOIN là nó sẽ ghi đè lên bộ lọc bên ngoài, trong một số tình huống làm cho ta bất ngờ với kết quả thì bạn có thể dùng hàm KEEPFILTER để bọc bên ngoài ALL /CROSSJOIN

[Sales Red or Contoso - ALL KEEPFILTERS] :=

CALCULATE (

[Sales Amount],

KEEPFILTERS (

FILTER (

ALL ( 'Product'[Color], 'Product'[Brand] ),

'Product'[Color] = "Red" || 'Product'[Brand] = "Contoso"

)

)

)

[Sales Red or Contoso - CROSSJOIN KEEPFILTERS] :=

CALCULATE (

[Sales Amount],

KEEPFILTERS (

FILTER (

CROSSJOIN (

ALL ( 'Product'[Color] ),

ALL ( 'Product'[Brand] )

),

'Product'[Color] = "Red" || 'Product'[Brand] = "Contoso"

)

)

)

Hoặc một cách khác, bạn có thể dùng hàm SUMMARIZE để tạo một list kết hợp các giá trị tồn tại của hai hoặc nhiều cột trong một table hoặc khác table miễn là chúng có cùng trong mối quan hệ many-to-one.

[Sales Red or Contoso - SUMMARIZE filter] :=

CALCULATE (

[Sales Amount],

FILTER (

SUMMARIZE (

'Product',

'Product'[Color],

'Product'[Brand]

),

'Product'[Color] = "Red" || 'Product'[Brand] = "Contoso"

)

)

Với trường hợp này SUMMARIZE đã giảm thiểu giá trị lọc đi so với ALL / CROSSJOIN nhưng bù lại engine phải mất một khoảng thời gian để scan table bộ lọc để ra các giá trị kết hợp tồn tại, đặc biệt sẽ chậm hơn với cột dữ liệu có low cardinality trong một table lớn. Mặt khác, nó có thể nhanh hơn trong tình huống table scan với nhiều điều kiện lọc phức tạp.

TỔNG KẾT

Bạn có thể nhiều lựa chọn để lọc khi áp dụng với hàm CALCULATE. Kết quả của đối số lọc phía sau luôn là table với một cột hoặc nhiều cột, và ‘chi phí’ của việc lọc này là số dòng trong table. Table lọc thường là một cách tiếp cận dễ khi viết các hàm lọc phức tạp nhưng nó có thể đưa đến low performance khi dữ liệu có tính cardinality cao bạn cần phải chú ý.