Calculated Columns và Measures khác nhau như thế nào?

CALCULATED COLUMN (Cột Được Tính)

Khi bạn tạo mô hình dữ liệu trong Power Pivot for Excel, Analysis Services Tabular hoặc Power BI Desktop, bạn có thể mở rộng bảng bằng cách tạo các cột mới. Nội dung của các cột được xác định bởi biểu thức DAX được xét theo từng hàng. Giao diện người dùng sẽ khác nhau tùy thuộc vào công cụ bạn sử dụng. Excel và các Dịch vụ Phân tích yêu cầu bạn viết biểu thức trong hộp văn bản công thức khi bạn chọn cột cuối cùng bên phải – “Thêm cột”. Bạn có thể đổi tên cột mới 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à chọn mục menu Đổi tên Cột. Như bạn thấy trong hình sau, công thức DAX bạn viết không chứa tên cột và bắt đầu bằng ký hiệu gán (=).

Trong Power BI Desktop, bạn có một giao diện người dùng khác. Bạn phải nhấp vào nút New Column để tạo một cột mới.

Tên cột mới là một phần của công thức bạn viết trong hộp văn bản công thức.

Giao diện người dùng cho phép bạn chỉ cần xác định một cột mới, nhưng chúng ta nói về cột được tính toán để phân biệt giữa các cột gốc (những cột được đọc từ nguồn dữ liệu hoặc được đánh giá bởi một truy vấn được viết trong Power Query hoặc Power BI) và các cột được tính toán (những cột được tạo mở rộng một bảng trong mô hình dữ liệu).

Cột được tính toán cũng giống như bất kỳ cột nào khác trong bảng và bạn có thể sử dụng cột đó trong bất kỳ phần nào của báo cáo. Bạn cũng có thể sử dụng cột được tính toán để xác định mối quan hệ nếu cần. Biểu thức DAX được xác định cho một cột được tính toán hoạt động trong ngữ cảnh của hàng hiện tại trên bảng đó. 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. Bạn không thể truy cập trực tiếp giá trị của các hàng khác.

Một khái niệm quan trọng mà bạn cần nhớ về các cột tính toán (Calculated Column) là chúng được tính toán trong quá trình tạo cột và sau đó được lưu trữ trong mô hình. Điều này có vẻ lạ nếu bạn đã quen với các cột được tính toán bằng SQL – không tồn tại – được tính toán tại thời điểm truy vấn và không sử dụng bộ nhớ. Trong mô hình dữ liệu cho DAX, tất cả các cột được tính toán đều chiếm không gian trong bộ nhớ và được tính toán trong quá trình xử lý bảng.

Điều này hữu ích bất cứ khi nào bạn tạo các cột được tính toán rất 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ý chứ không phải thời gian truy vấn, dẫn đến trải nghiệm người dùng tốt hơn. Tuy nhiên, bạn phải luôn nhớ rằng một cột được tính toán sử dụng bộ nhớ RAM. Ví dụ: nếu bạn có một công thức phức tạp cho một cột được tính toán, 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 dự án, nhưng nó là một thói quen xấu trong sản xuất vì mỗi phép tính trung gian được lưu trữ trong RAM và lãng phí bộ nhớ quý giá.

Để thuận tiện, khi viết công thức cho một cột được tính trong một bài báo hoặc trong một cuốn sách, chúng tôi sử dụng quy ước sau:

TableName[ColumnName] = <DAX expression for calculated column>

Tên bảng[tên cột] = < Biểu thức DAX cho cột được tính>

Cú pháp này không tương ứng với những gì bạn nhập trong giao diện người dùng, nhưng giúp bạn dễ dàng viết chính xác tên của cột được tính toán, bảng thuộc về nó và biểu thức DAX của nó. Tùy 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 người dùng. Ví dụ: hãy xem xét biểu thức sau trong một bài báo: Trong Power BI Desktop, bạn sẽ vào bảng Bán hàng (Sales), nhấp vào nút Cột Mới và nhập công thức sau:

Sales[GrossMargin] = Sales[SalesAmount] – Sales[TotalProductCost]

Trong Excel và Dịch vụ Phân tích, bạn sẽ vào bảng Bán hàng và thêm vào cột mới công thức sau:

= Sales[SalesAmount] – Sales[TotalProductCost]

Trong Power BI Desktop, bạn sẽ vào bảng Bán hàng, nhấp vào nút Cột Mới và nhập công thức sau:

GrossMargin = Sales[SalesAmount] – Sales[TotalProductCost]

MEASURES (Chỉ Số)

Có một cách khác để xác định các phép tính trong mô hình DAX, hữu ích bất cứ khi nào bạn không muốn tính 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 là các chỉ số. Đây là tên tương tự được sử dụng trong giao diện người dùng, ngoại trừ Excel 2013, sử dụng thuật ngữ “trường được tính toán” thay vì “chỉ số”. Excel 2016 đã phục chế trở lại “chỉ số”, là thuật ngữ được sử dụng trong DAX và ban đầu cũng được sử dụng trong Power Pivot cho Excel 2010.

Trong ví dụ trước, bạn đã học cách xác định cột GrossMargin trong bảng Sales để tính số tiền lãi gộp. Tuy nhiên, điều gì xảy ra nếu bạn muốn hiển thị tỷ suất lợi nhuận gộp dưới dạng tỷ lệ phần trăm của doanh thu? Bạn có thể tạo một cột được tính toán với công thức sau:

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

Công thức này tính giá trị phù hợp ở cấp độ hàng, như bạn có thể thấy trong hình sau:

Tuy nhiên, khi tính toán giá trị tổng hợp của một tỷ lệ phần trăm, bạn không thể dựa vào các cột được tính toán. Thay vào đó, bạn cần tính giá trị tổng hợp bằng tổng lợi nhuận gộp chia cho tổng số tiền bán hàng. Do đó, trong trường hợp này, bạn cần tính tỷ lệ trên các tổng thể – bạn không thể sử dụng tập hợp các cột được tính toán. Nói cách khác, bạn tính tỷ lệ của các tổng, không phải tổng của tỷ lệ.

Bạn không thể sử dụng một cột được tính toán cho hoạt động này. Nếu bạn cần hoạt động trên các giá trị tổng hợp thay vì trên cơ sở từng hàng, bạn phải tạo các chỉ số.

TableName[MeasureName] := <DAX expression for measure>

Tên bảng[tên chỉ số] := < Biểu thức DAX cho chỉ số

Cú pháp này đơn giản hóa định nghĩa về tên của chỉ số, của bảng mà nó thuộc về và biểu thức DAX của nó. Tùy thuộc vào công cụ bạn sử dụng, bạn phải sử dụng một cú pháp khác nhau khi nhập công thức trong giao diện người dùng.

Ví dụ: hãy coi việc triển khai chính xác cho GrossMarginPct được xác định là một chỉ số:

Sales[Gross Margin %] := DIVIDE ( SUM ( Sales[GrossMargin] ), SUM (Sales[SalesAmount] ) )

Trong Excel và Dịch vụ Phân tích, bạn sẽ đi vào lưới chỉ số của bảng Bán hàng và nhập văn bản sau vào một ô trống:

Gross Margin % := DIVIDE ( SUM ( ] ), SUM (Sales[SalesAmount] ) )

Trong Power BI Desktop, bạn sẽ vào bảng Bán hàng, nhấp vào nút Chỉ số Mới và nhập công thức trước hoặc công thức sau:

Gross Margin % = DIVIDE ( SUM ( Sales[GrossMargin] ), SUM (Sales[SalesAmount] ) )

Nếu bạn sử dụng toán tử gán “: =” trong cú pháp của mình, Power BI Desktop sẽ tự động biến đổi nó trong toán tử “=”. Tuy nhiên, trong các bài báo và sách, chúng tôi luôn sử dụng toán tử gán “: =” cho các chỉ số. Quy ước này giúp bạn dễ dàng phân biệt giữa các chỉ số và cột trong mã.

Các chỉ số và cột được tính toán đều sử dụng biểu thức DAX. Sự khác biệt là bối cảnh đánh giá. Một chỉ số được xét trong ngữ cảnh của ô được đánh giá trong báo cáo hoặc trong truy vấn DAX, trong khi cột được tính toán được tính ở cấp hàng trong bảng. Ngữ cảnh của ô phụ thuộc vào lựa chọn của người dùng trong 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 (Doanh số [SalesAmount]) trong một chỉ số, có nghĩa là tổng của tất cả các ô được tổng hợp trong ô này, trong khi bạn sử dụng Doanh số [SalesAmount] trong một cột được tính toán, lại có nghĩa là giá trị của SalesAmount trong hàng hiện tại.

Một chỉ số cần được xác định trong một bảng. Đây là một trong những yêu cầu của ngôn ngữ DAX. Tuy nhiên, chỉ số không thực sự thuộc hàng. Trên thực tế, bạn có thể di chuyển chỉ số từ bảng này sang bảng khác mà không làm mất chức năng của nó.

1. Lựa chọn giữa các cột và chỉ số được tính toán

Ngay cả khi chúng trông giống nhau, vẫn có sự khác biệt lớn giữa các cột và chỉ số được tính toán. Giá trị của một cột cần tính được tính toán trong quá trình làm mới dữ liệu và sử dụng hàng hiện tại làm ngữ cảnh; nó không phụ thuộc vào tương tác của người dùng trong báo cáo. Một chỉ số lại hoạt động dựa trên việc tổng hợp dữ liệu được xác định bởi ngữ cảnh hiện tại, phụ thuộc vào bộ lọc được áp dụng trong báo cáo – chẳng hạn như lựa chọn slicer, hàng và cột trong bảng tổng hợp hoặc các trục và bộ lọc được áp dụng cho biểu đồ.

Tại thời điểm này, bạn có thể tự hỏi khi nào sử dụng các cột được tính toán trên các chỉ số. Đôi khi một trong hai là một lựa chọn, nhưng trong hầu hết các tình huống, nhu cầu tính toán của bạn sẽ quyết định lựa chọn của bạn.

Bạn phải xác định một cột được tính toán bất cứ khi nào bạn muốn thực hiện như sau:

• Đặt kết quả được tính toán trong slicer hoặc xem kết quả trong hà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.
• Xác định một biểu thức được liên kết chặt chẽ với hàng hiện tại. Ví dụ: Giá * Số lượng không thể hoạt động trên mức trung bình hoặc trên tổng của hai cột.
• Phân loại văn bản hoặc số. Ví dụ: một loạt các giá trị cho một chỉ số, một loạt các độ tuổi khách hàng, chẳng hạn như 0–18, 18–25, v.v.

Tuy nhiên, bạn phải xác định một chỉ số bất cứ khi nào bạn muốn hiển thị các giá trị tính toán kết quả phản ánh các lựa chọn của người dùng và xem chúng trong vùng giá trị của bảng tổng hợp hoặc trong vùng vẽ của biểu đồ – ví dụ:

• Khi bạn tính toán 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 cột được tính toán và với các chỉ số, ngay 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 là một cột được tính toán:

Sales[GrossMargin] = Sales[SalesAmount] – Sales[TotalProductCost]

nhưng nó cũng có thể được định nghĩa là một chỉ số:

[GrossMargin] := SUM ( Sales[SalesAmount] ) – SUM ( Sales[TotalProductCost] )

Chúng tôi khuyên bạn nên sử dụng một chỉ số trong trường hợp này, vì nó được đánh giá tại thời điểm truy vấn, nó không tiêu tốn bộ nhớ và không gian đĩa. Điều này trở nên quan trọng hơn với các bộ dữ liệu lớn. Khi kích thước của mô hình không phải là vấn đề, bạn có thể sử dụng phương pháp mà bạn cảm thấy thoải mái hơn.

Bạn nên xem xét rằng thông thường bạn có thể tránh các cột được tính toán như là các phép tính trung gian cho một chỉ số. Ví dụ: nếu bạn phải tạo một chỉ số dựa trên kết quả của một sản phẩm được tạo ra theo từng hàng, bạn có thể xác định một cột được tính toán và sau đó là một chỉ số như sau:

1. Sales[SalesAmount] = Sales[Quantity] * Sales[Unit Price]
2. Sales[Sum of SalesAmount] := SUM ( Sales[SalesAmount] )

Hoặc bạn có thể chỉ sử dụng một chỉ số duy nhất để đánh giá cùng một biểu thức của cột được tính toán theo từng hàng trong vòng lặp của bảng.

Sales[Sum of SalesAmount] := SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )

Kỹ thuật này có thể được mở rộng cho hầu hết các chỉ số. Ví dụ: chúng tôi đã tạo các cột được tính toán sau và chỉ số trong ví dụ trước:

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

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

Sales[GrossMargin] = Sales[SalesAmount] – Sales[TotalProductCost]

Sales[Gross Margin %] := DIVIDE ( SUM ( Sales[GrossMargin] ), SUM (Sales[SalesAmount] ) )

Tuy nhiên, bạn có thể tạo cùng một chỉ số cuối cùng theo cách này:

Sales[Gross Margin %] :=

DIVIDE (

SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )

– SUMX ( Sales, Sales[Quantity] * Sales[Unit Cost] ),

SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )

)

Hoặc, trong Excel 2016, Power BI Desktop và Dịch vụ phân tích 2016, bạn có thể tận dụng cú pháp biến (VAR) để không lặp lại phép tính SUMX của số tiền bán hàng hai lần và bạn có thể chia phép tính thành nhiều bước trong một lần nữa cách dễ đọc, mà không phải trả chi phí lưu trữ các kết quả trung gian trong các cột được tính toán:

Sales[Gross Margin %] :=

VAR SalesAmount = SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )

VAR TotalProductCost = SUMX ( Sales, Sales[Quantity] * Sales[Unit Cost] )

VAR GrossMargin = SalesAmount – TotalProductCost

RETURN DIVIDE ( GrossMargin, SalesAmount )

Hãy nhớ rằng có những cách thay thế để xác định cột được tính toán trước khi nhập dữ liệu được DAX sử dụng. Ví dụ: bạn có thể sử dụng Power Query trong Excel hoặc Trình soạn thảo truy vấn tương ứng trong Power BI Desktop, cung cấp ngôn ngữ mạnh mẽ để thao tác dữ liệu theo từng hàng.

Các cột được tính toán trong DAX rất hữu ích bất cứ khi nào bạn phải sử dụng dữ liệu từ các bảng khác trong mô hình dữ liệu hoặc xem xét dữ liệu tổng hợp trong một phép tính. Hai ví dụ trong đó các cột được tính toán rất hữu ích là Phân đoạn tĩnh và các mẫu Phân loại ABC. Bạn có thể tải xuống các ví dụ trong Power Pivot for Excel 2013 và Power BI Destkop trong tệp demo.

Nguồn: Internet

Bài viết được sưu tầm trên mạng với mục đích chia sẻ kiến thức tới mọi người