Calculated Columns và Measures trong DAX.

Một trong những khái niệm đầu tiên mà bạn luôn phải học khi tới với DAX đó là sự khác biệt giữa Calculated Columns và Measures. Bài viết này sẽ tóm tắt ngắn gọn những điểm khác biệt và chỉ rõ ra là lúc nào chúng ta nên sử dụng nó.

1. Calculated Columns.

Khi bạn tạo ra 1 Data Model ở trong Power Pivot trên Excel, Power BI Desktop, Bạn có thể mở rộng Table bằng cách tạo thêm các cột mới. Các cột này được định nghĩa bởi 1 biểu thức DAX theo từng dòng một. Giao diện người dùng sẽ không giống nhau tuỳ thuộc vào công cụ mà bạn sử dụng. Excel sẽ yêu cầu bạn phải viết biểu thức DAX này trong Formula Textbox khi bạn chọn cột cuối cùng bên tay phải (Add Column).

Bạn có thể đổi tên cột mới này trước hoặc sau khi xác định biểu thức bằng cách bấm chuột phải vào cột mới và tiến hành ấn vào nút Rename Column trên thanh Menu. Như bạn thấy ở bức ảnh dưới đây, công thức DAX mà bạn viết không bao gồm tên cột và nó bắt đầu bằng ký hiệu gán (=)

Trong Power BI Desktop, bạn có 1 giao diện hoàn toàn khác. Khi đó bạn cần click vào nút New Column để có thể tạo thêm 1 cột mới.

Tên của cột mới là 1 phần của công thức mà bạn viết trong Formula Textbox.

Ví dụ như hình trên thì ta đang tạo ra 1 cột mới tên là SalesAmount và công thức này được định nghĩa bằng cách lấy cột Quantity nhân với cột Unit Price của Table Sales.

1 Calculated Column cũng chỉ như các column khác trong Table và bạn có thể sử dụng cột này trong bất kỳ phần nào của báo cáo. Bạn cũng có thể sử dụng Calculated Column để có thể Define mối quan hệ (Relationship) nếu cần. Biểu thức DAX được tạo ra khi định nghĩa 1 Calculated Column sẽ được vận hành theo Row Context, áp dụng theo từng dòng trong Table mà bạn định nghĩa Calculated Column. Bất kỳ tham chiếu nào đến một cột đều trả về giá trị của cột đó cho hàng hiện tại (Current Row). Bạn không thể truy cập trực tiếp giá trị của các hàng khác.

Đây là 1 điểm khác biệt rất lớn khi so sánh với Excel. Với Excel bạn có thể truy cập đến bất kỳ dòng nào mà mình muốn. Tuy nhiên thì DAX sẽ không như vậy. Đây là điểm mà bạn cần lưu ý rất kỹ.

1 điểm rất quan trọng nữa mà bạn cần phải ghi nhớ đó là Calculated Columns sẽ luôn được tính toán trong quá trình xử lý dữ liệu và sau đó được lưu trữ trong data model của bạn. Điều này có vẻ hơi kỳ lạ nếu bạn đã quen với việc tính toán bằng SQL, đó là được tính toán tại thời điểm truy vấn và không sử dụng bộ nhớ. Tuy nhiên, trong các Data Model của DAX , tất cả các Calculated Column đều chiếm không gian trong bộ nhớ của bạn và được tính toán trong quá trình xử lý dữ liệu trong Table.

Điều trên rất hữu dụng khi bạn tạo ra các Calculated Column cực kỳ phức tạp. Thời gian cần thiết để tính toán chúng luôn là thời gian để xử lý tính toán chứ không phải là thời gian truy vấn. Đây là điểm bạn cần lưu ý để có thể tạo ra kết quả nhanh hơn và không ảnh hưởng đến trải nghiệm thực tế của người dùng khi xem các báo cáo. Bạn cần phải nhớ rằng Calculated Column sẽ sử dụng RAM trên máy tính của bạn. Ví dụ: Nếu bạn có 1 công thức cực kỳ phức tạp để tạo ra 1 Calculated Column, bạn có thể muốn tách các bước tính toán trong các cột trung gian khác nhau. Mặc dù kỹ thuật này rất hữu ích trong quá trình phát triển báo cáo của bạn nhưng nó thực sự là 1 thói quen xấu vì mỗi phép tính trung gian được lưu trữ trong RAM và lãng phí tài nguyên quý giá trên máy tính của bạn.

Để thuận tiện, khi viết công thức cho 1 Calculated Column trong bài viết này, chúng ta sẽ sử dụng dung 1 quy ước như sau.

Cú pháp này không tương ứng với những gì bạn nhập vào giao diện người dùng, nhưng nó sẽ giúp bạn dễ dàng viết chính xác tên của Calculated Column, bảng thuộc về nó và biểu thức DAX của nó. Tuỳ thuộc vào công cụ bạn sử dụng, bạn phải bỏ qua tên bảng hoặc cả tên bảng và tên cột trong công thức bạn nhập vào giao diện của người dùng. Ví dụ hãy xem xét biểu thức dưới đây.

GrossMargin = Sales[SalesAmount] - Sales[TotalProductCost]

Thật may là bài viết này của chúng ta sẽ tập trung vào PowerBI nên đây cũng sẽ là cú pháp mà ta sẽ áp dụng trong suốt bài viết này.

Như ví dụ trên thì ta đang tạo ra 1 cột mới tên là GrossMargin và cột này được định nghĩa bằng cách lấy cột SalesAmount của bảng Sales trừ đi cột TotalProductCost của bảng Sales.

2. Measures.

Có 1 cách khác nữa để viết các phép tính trong DAX ngoài Calculated Column, đó là sử dụng Measure. Nó rất hữu ích bất cứ khi nào bạn không muốn tính toán các giá trị cho mỗi hàng mà thay vào đó, bạn muốn tổng hợp các giá trị từ nhiều hàng trong một bảng. Các tính toán này được gọi là Measure. Đây cũng là một cái tên được sử dụng trong giao diện người dùng của bạn. Ngoại trừ phiên bản 2013 thì nó được sử dụng dưới cái tên là Calculated Field (Đến hiện tại thì trong Pivot Table của Excel vẫn còn tồn tại cái tên này) thay vì Measure. Đến phiên bản 2016 thì nó được đổi tên thành Measure. Và đây là cái tên nó được sử dụng đầu tiên trong Power Pivot bắt đầu từ phiên bản Excel 2010.

Trong các ví dụ trước thì bạn đã học cách định nghĩa ra cột GrossMargin trong bảng Sales. Vậy giờ nếu bạn muốn tính xem từng dòng GrossMargin chiếm bao nhiêu % trong tổng SalesAmount. Vậy thì giờ ta sẽ thử tạo ra 1 cột khác nữa có tên là GrossMarginPct trong bảng Sales với công thức DAX như sau.

GrossMarginPct = DIVIDE ( Sales[GrossMargin], Sales[SalesAmount] )

Công thức trên sẽ tính toán ra các kết quả chính xác theo từng dòng như trong bảng dưới đây.

Như bạn thấy là ở đây ta sẽ có kết quả GrossMarginPct cho từng dòng 1.

Với công thức trên khi bạn tạo ra Calculated Columns thì DAX sẽ hiểu là bạn muốn lấy GrossMargin của Current Row chia cho SalesAmount của Current Row. Ví dụ như dòng đầu tiên thì ta sẽ lấy 5 / 8 = 62.5% => Do ở đây ta không lấy số sau dấu phẩy nên kết quả thu được sẽ là 63% như hình trên.

Tuy nhiên đây không phải là kết quả ta muốn. Ta đang muốn có kết quả là xem % tổng GrossMargin chia cho tổng SalesAmount mới đúng.

Trong trường hợp này thì bạn không thể trông chờ vào Calculated Column được vì về bản chất Calculated Columns sẽ tính toán theo từng dòng và không thể hiểu được việc lấy tổng GrossMargin chia cho SalesAmount. Trong trường hợp này thì bạn sẽ cần 1 Measure. Vì về bản chất Calculated Columns chỉ tính toán row-by-row mà thôi.

Giờ ta sẽ viết 1 Measure như sau.

GrossMarginPct Measure = DIVIDE( SUM(Sales[GrossMargin]), SUM(Sales[SalesAmount]) )

Kết quả của phép tính này sẽ không thể thấy được trên data như Calculated Columns. Để thấy được thì ta sẽ kéo vào Visual trên Power BI và chọn Visualization là Card như hình dưới đây.

Tựu chung lại thì Measure hay Calculated Column đều là các biểu thức phép tính trong DAX. Điểm khác biệt của chúng là bối cảnh của việc tính toán (sau đây mình sẽ gọi nó bằng chữ context).

1 Measure sẽ sử dụng Filter Context, trong khi đó Calculated Column sẽ sử dụng Row Context. Filter Context sẽ phụ thuộc vào lựa chọn của người dùng trên báo cáo hoặc hình dạng của truy vấn DAX. Vì vậy khi bạn sử dụng SUM(Sales[SalesAmount]) trong 1 Measure thì ý nghĩa của nó là bạn sẽ tính tổng tất cả các ô trong cột SalesAmount. Còn nếu bạn áp dụng công thức trên trong Calculated Columns thì nó sẽ có ý nghĩa là bạn sẽ tính tổng cột SalesAmount chỉ ở Current Row (Tức Row hiện tại mà thôi).

1 Measure cần được định nghĩa trong 1 Table. Đây là điều bắt buộc vì đây là yêu cầu bắt buộc của ngôn ngữ DAX. Tuy nhiên Measure không thực sự thuộc về Table nào cả. Thực tế thì bạn có thể di chuyển Measure từ Table này sang Table khác mà không làm thay đổi kết quả của phép tính của bạn. Tuy nhiên thì Calculated Column đã gắn chặt với từng Table rồi nên không thể di chuyển thoải mái như Measure được.

3. Chọn lựa giữa Calculated Columns và Measures

Mặc dù Calculated Columns và Measures khá giống nhau nhưng giữa chúng lại có sự khác biệt rất lớn.

Kết quả của 1 Calculated Column được tính toán mỗi khi dữ liệu được làm mới và nó sử dụng dòng hiện tại hay current row làm context của mình. Điều này sẽ không ảnh hưởng đến tương tác của người dùng khi làm báo cáo. 1 Measure lại hoạt động dựa trên việc tổng hợp dữ liệu được xác định bởi context hiện tại, nó sẽ phụ thuộc vào bộ lọc trong báo cáo. Chẳng hạn Slicer hay lựa chọn các dòng, cột trong Pivot Table hoặc là các trục, bộ lọc được áp dụng cho biểu đồ chẳng hạn.

Tại thời điểm này, bạn đang tự hỏi là lúc nào nên sử dụng Calculated Column, lúc nào nên sử dụng Measure. Đôi lúc đây chỉ là 1 tuỳ chọn nhưng trong 1 số trường hợp nó sẽ phụ thuộc vào quyết định của bạn.

Bạn sẽ phải định nghĩa Calculated Column khi mà bạn muốn làm các việc như sau:

- Đặt kết quả tính toán được khi bạn phân loại dữ liệu. Ví dụ như bạn có 1 cột Customer[TotalChildren] chẳng hạn. Bạn muốn phân loại là nếu TotalChildren = 0 thì là “No Children”. Còn lại thì là “Have Children” chẳng hạn. Lúc này thì chắc chắn bạn cần phải sử dụng Calculated Columns.

- Hoặc khi bạn muốn xem kết quả trong dòng hoặc cột trong bảng tổng hợp (trái ngược với vùng giá trị) hoặc trong các trục của biểu đồ hoặc sử dụng kết quả làm điều kiện lọc trong truy vấn DAX.

- Định nghĩa một phép tính được liên kết chặt chẽ với dòng hiện tại. Ví dụ như Price * Quantity sẽ không thể hoạt động với tính trung bình hay tính tổng của 2 cột.

- Phân loại văn bản hoặc số. Ví dụ như khoảng dữ liệu phân loại độ tuổi, khoảng dữ liệu phân loại số lượng khách hàng chẳng hạn.

Tuy nhiên, bạn sẽ phải định nghĩa 1 Measure bất cứ khi nào bạn muốn hiển thị các kết quả tính toán dựa trên lựa chọn của người sử dụng và thấy được kết quả này trên Pivot Table hoặc biểu đồ mà bạn muốn. Ví dụ như:

- Khi bạn muốn tính tỷ lệ phần trăm lợi nhuận trên một lựa chọn dữ liệu nhất định.

- Khi bạn tính toán các tỷ lệ của một sản phẩm so với tất cả các sản phẩm nhưng vẫn giữ nguyên bộ lọc theo năm và theo khu vực.

Bạn có thể thể hiện một số phép tính cả với các Calculated Columns và với các Measures, kể cả khi bạn cần sử dụng các biểu thức DAX khác nhau trong những trường hợp này. Ví dụ: bạn có thể xác định GrossMargin như 1 Calculated Column như sau.

GrossMargin = Sales[SalesAmount] - Sales[TotalProductCost]

Nhưng bạn cũng có thể định nghĩa nó là 1 Measure như sau.

GrossMargin Measure = SUM(Sales[SalesAmount]) - SUM(Sales[TotalProductCost])

Mình khuyên các bạn nên sử dụng Measure trong trường hợp này. Bởi vì khi bạn sử dụng Measure thì sẽ không tốn bộ nhớ và không gian của ổ đĩa. Điều này sẽ rất quan trọng khi bạn có 1 Dataset lớn. Khi mà kích thước của model không phải là vấn đề thì bạn có thể sử dụng Calculated Columns hay Measures tuỳ thuộc vào việc bạn quen sử dụng cái nào hơn mà thôi.

Ví dụ giờ bạn muốn tính SalesAmount dựa vào Quantity và UnitPrice thì bạn có thể làm như sau.

Đầu tiên bạn tạo ra 1 Calculated Column trong bảng Sales như sau

SalesAmount = Sales[Quantity] * Sales[Unit Price]

Sau đó thì bạn tạo ra 1 Measure để tính ra tổng SalesAmount như sau.

Sum Of SalesAmount 01 = SUM(Sales[SalesAmount])

Nhưng bạn có thể làm đơn giản hơn bằng cách viết duy nhất 1 Measure như sau.

Sum of SalesAmount 02 =

SUMX(

    Sales,

    Sales[Quantity] * Sales[Unit Price]

)

Trong trường hợp bạn muốn tính Gross Margin % thì bạn sẽ có các cách tính như dưới đây.

Cách 1:

Bạn sẽ lần lượt tạo ra 3 Calculated Column trong bảng Sales với 3 công thức như sau.

SalesAmount = Sales[Quantity] * Sales[Unit Price]

TotalProductCost = Sales[Quantity] * Sales[Unit Cost]

GrossMargin = Sales[SalesAmount] - Sales[TotalProductCost]

Sau đó bạn tạo ra 1 Measure để tính Gross Margin % như sau.

GrossMarginPct Measure = DIVIDE( SUM(Sales[GrossMargin]), SUM(Sales[SalesAmount]) )

Cách 2:

Bạn hoàn toàn có thể tạo ra 1 phép tính duy nhất như dưới đây.

Gross Margin Pct 03 =

VAR SalesAmount =

SUMX(

    Sales,

    Sales[Quantity] * Sales[Unit Price]

)

VAR TotalProductCost =

SUMX(

    Sales,

    Sales[Quantity] * Sales[Unit Cost]

)

VAR GrossMargin = SalesAmount - TotalProductCost

VAR RESULT =

DIVIDE(

    GrossMargin,

    SalesAmount

)

RETURN

    RESULT

Qua các ví dụ trên thì phần nào bạn cũng đã thấy được sự khác biệt giữa Calculated Columns và Measures rồi đúng không nào. Trong phần lớn các trường hợp thì nếu bạn có thể viết được Measure thì mình khuyên bạn nên viết Measure. Chỉ trong 1 số trường hợp cụ thể như mình đã nói ở trên thì bạn mới nên sử dụng Calculated Columns mà thôi. Vì nếu bạn sử dụng quá nhiều Calculated Columns khi không thực sự cần thiết thì sẽ làm cho Data và File Power BI của các bạn trở nên rất nặng và sẽ gây ảnh hưởng tới trải nghiệm của người dùng khi sử dụng báo cáo mà bạn tạo ra.