Biểu thức Phân tích Dữ liệu (DAX) trong PowerPivot

Biểu thức Phân tích Dữ liệu (DAX) thoạt nghe có vẻ hơi đáng sợ, nhưng đừng để cái tên lừa gạt bạn. Các kiến thức cơ bản về DAX thực sự khá dễ hiểu. Trước tiên, DAX KHÔNG phải là một ngôn ngữ lập trình. DAX là một ngôn ngữ công thức. Bạn có thể sử dụng DAX để xác định các phép tính tùy chỉnh cho các Cột được Tính toán và cho Các Số đo (còn được gọi là trường tính toán). DAX bao gồm một số hàm dùng trong công thức Excel và các hàm bổ sung được thiết kế để làm việc với dữ liệu quan hệ và thực hiện tổng hợp động.

Tìm hiểu về Công thức DAX

Công thức DAX rất giống với công thức Excel. Để tạo một bảng điều khiển, bạn hãy nhập dấu bằng, theo sau là tên hàm hoặc biểu thức và bất kỳ giá trị hoặc đối số bắt buộc nào. Giống như Excel, DAX cung cấp nhiều hàm khác nhau mà bạn có thể sử dụng để làm việc với các chuỗi, thực hiện tính toán bằng ngày và giờ hoặc tạo các giá trị có điều kiện.

Tuy nhiên, các công thức DAX khác biệt ở những điểm quan trọng sau:

• Nếu bạn muốn tùy chỉnh tính toán trên cơ sở từng hàng, DAX bao gồm các hàm cho phép bạn sử dụng giá trị hàng hiện tại hoặc một giá trị liên quan để thực hiện tính toán thay đổi tùy theo ngữ cảnh.
• DAX bao gồm một loại hàm trả về một bảng dưới dạng kết quả, chứ không phải là một giá trị đơn. Các hàm này có thể được sử dụng để cung cấp đầu vào cho các hàm khác.
• Các hàm hiển thị thời gian thông minhtrong DAX cho phép tính toán bằng cách sử dụng các phạm vi ngày và so sánh kết quả trong các khoảng thời gian song song.

Nơi dùng Công thức DAX

Bạn có thể tạo công thức trong Power Pivot trong cột được tính hoặc trong trường được tính toán.

Cột được tính toán

Cột được tính là cột mà bạn thêm vào bảng Power Pivot hiện có. Thay vì dán hoặc nhập các giá trị vào cột, bạn tạo công thức DAX xác định các giá trị cột. Nếu bạn đưa bảng Power Pivot vào PivotTable (hoặc PivotChart), cột được tính toán có thể được dùng như bất kỳ cột dữ liệu nào khác.

Các công thức trong cột được tính toán rất giống các công thức bạn tạo trong Excel. Tuy nhiên, không giống như trong Excel, bạn không thể tạo công thức khác cho các hàng khác nhau trong bảng; Thay vào đó, công thức DAX được tự động áp dụng cho toàn bộ cột.

Khi một cột chứa công thức, giá trị được tính cho mỗi hàng. Kết quả được tính toán cho cột ngay khi bạn tạo công thức. Giá trị cột chỉ được tính toán lại nếu dữ liệu cơ sở được làm mới hoặc nếu sử dụng tính toán lại thủ công.

Bạn có thể tạo các cột được tính dựa trên các số đo và các cột được tính khác. Tuy nhiên, tránh sử dụng cùng tên cho cột được tính toán và số đo vì điều này có thể dẫn đến kết quả nhầm lẫn. Khi tham chiếu đến một cột, tốt nhất là sử dụng tham chiếu cột đầy đủ tiêu chuẩn, để tránh vô tình gọi ra một số đo.

Để biết thêm thông tin chi tiết, hãy xem Cột được Tính trong Power Pivot.

Biện pháp

Số đo là công thức được tạo riêng để sử dụng trong PivotTable (hoặc PivotChart) sử dụng dữ liệu Power Pivot. Các số đo có thể dựa trên các hàm tổng hợp tiêu chuẩn, chẳng hạn như COUNT hoặc SUM, hoặc bạn có thể xác định công thức của riêng mình bằng cách sử dụng DAX. Số đo được dùng trong khu vực Giá trị của PivotTable. Nếu bạn muốn đặt kết quả được tính toán vào một khu vực khác của PivotTable, hãy dùng cột được tính thay thế.

Khi bạn xác định công thức cho một số đo rõ ràng, không có gì xảy ra cho đến khi bạn thêm số đo đó vào PivotTable. Khi bạn thêm số đo, công thức sẽ được định trị cho mỗi ô trong khu vực Giá trị của PivotTable. Vì kết quả được tạo cho mỗi tổ hợp tiêu đề hàng và cột, kết quả cho số đo có thể khác nhau trong mỗi ô.

Định nghĩa của số đo mà bạn tạo sẽ được lưu cùng với bảng dữ liệu nguồn của nó. Nó xuất hiện trong danh sách Trường PivotTable và sẵn dùng cho tất cả người dùng của sổ làm việc.

Để biết thông tin chi tiết hơn, hãy xem Giá trị đo trong Power Pivot.

Tạo công thức bằng cách dùng thanh công thức

Power Pivot, giống như Excel, cung cấp thanh công thức để giúp bạn dễ dàng tạo và sửa công thức, cùng chức năng Tự động Hoàn tất để giảm thiểu lỗi nhập và cú pháp.

Để nhập tên bảng Bắt đầu nhập tên bảng. Tự động Hoàn tất Công thức cung cấp một danh sách thả xuống có chứa các tên hợp lệ bắt đầu bằng các chữ cái đó.

Để nhập tên cột Nhập một dấu ngoặc rồi chọn cột từ danh sách các cột trong bảng hiện tại. Đối với một cột từ bảng khác, bắt đầu nhập chữ cái đầu tiên của tên bảng, rồi chọn cột từ danh sách thả xuống Tự động điền.

Để biết thêm chi tiết và hướng dẫn về cách xây dựng công thức, hãy xem Tạo Công thức cho Phép tính trong Power Pivot.

Mẹo sử dụng tính năng Tự động Hoàn tất

Bạn có thể sử dụng tính năng Tự động Hoàn tất Công thức ở giữa công thức hiện có với các hàm được lồng vào. Văn bản ngay trước điểm chèn được dùng để hiển thị các giá trị trong danh sách thả xuống và tất cả văn bản sau điểm chèn vẫn không thay đổi.

Tên đã xác định mà bạn tạo cho hằng số không hiển thị trong danh sách thả xuống Tự động Hoàn tất nhưng bạn vẫn có thể nhập chúng.

Power Pivot không thêm dấu đóng ngoặc đơn của hàm hoặc tự động khớp các dấu ngoặc đơn. Bạn nên đảm bảo rằng từng hàm đều đúng về mặt cú pháp, nếu không bạn sẽ không thể lưu hay sử dụng công thức. 

Sử dụng nhiều hàm trong một công thức

Bạn có thể lồng các hàm, nghĩa là bạn sử dụng kết quả từ một hàm làm đối số của một hàm khác. Bạn có thể lồng tối đa 64 mức hàm vào các cột tính toán. Tuy nhiên, việc lồng nhau có thể gây khó khăn cho việc tạo hoặc khắc phục sự cố công thức.

Nhiều hàm DAX được thiết kế để chỉ dùng như hàm lồng. Các hàm này trả về một bảng, do đó không thể trực tiếp lưu bảng; Thông tin này nên được cung cấp dưới dạng dữ liệu đầu vào cho một hàm bảng. Ví dụ: các hàm SUMX, AVERAGEX và MINX đều yêu cầu bảng làm đối số đầu tiên.

So sánh các Hàm DAX và các Hàm Excel

Thư viện hàm DAX dựa trên thư viện hàm Excel nhưng các thư viện có nhiều điểm khác biệt. Phần này tóm tắt sự khác biệt và tương đồng giữa các hàm Excel và các hàm DAX.

• Nhiều hàm DAX có cùng tên và hành vi chung như hàm Excel nhưng đã được sửa đổi để nhận các kiểu đầu vào khác nhau và trong một số trường hợp, có thể trả về kiểu dữ liệu khác. Nói chung, bạn không thể sử dụng các hàm DAX trong công thức Excel hoặc sử dụng công thức Excel trong Power Pivot mà không có một số sửa đổi.
• Hàm DAX không bao giờ nhận tham chiếu ô hoặc dải ô làm tham chiếu, thay vào đó, hàm DAX nhận một cột hoặc bảng làm tham chiếu.
• Hàm ngày và giờ của DAX trả về kiểu dữ liệu ngày/giờ. Ngược lại, hàm ngày và thời gian của Excel trả về một số nguyên đại diện cho ngày dưới dạng số sê-ri.
• Nhiều hàm DAX mới có thể trả về bảng giá trị hoặc sẽ tính toán dựa trên bảng giá trị làm dữ liệu đầu vào. Ngược lại, Excel không có hàm trả về bảng nhưng một số hàm có thể làm việc với mảng. Khả năng dễ dàng tham chiếu toàn bộ bảng và cột là một tính năng mới trong Power Pivot.
• DAX cung cấp các hàm tra cứu mới tương tự như hàm tra cứu mảng và vector trong Excel. Tuy nhiên, các hàm DAX yêu cầu thiết lập mối quan hệ giữa các bảng.
• Dữ liệu trong một cột dự kiến sẽ luôn có cùng một kiểu dữ liệu. Nếu dữ liệu không cùng kiểu, DAX sẽ thay đổi toàn bộ cột thành kiểu dữ liệu phù hợp nhất với tất cả các giá trị.

Kiểu Dữ liệu DAX

Bạn có thể nhập dữ liệu vào mô hình dữ liệu Power Pivot từ nhiều nguồn dữ liệu khác nhau mà có thể hỗ trợ các kiểu dữ liệu khác nhau. Khi bạn nhập hoặc tải dữ liệu, rồi sử dụng dữ liệu trong tính toán hoặc trong PivotTable, dữ liệu sẽ được chuyển đổi thành một trong các kiểu dữ liệu Power Pivot. Để biết danh sách các kiểu dữ liệu, hãy xem Kiểu dữ liệu trong Mô hình Dữ liệu.

Kiểu dữ liệu bảng là kiểu dữ liệu mới trong DAX được sử dụng làm dữ liệu đầu vào hoặc đầu ra cho nhiều hàm mới. Ví dụ: hàm FILTER nhận một bảng làm dữ liệu đầu vào và xuất ra một bảng khác chỉ bao gồm các hàng đáp ứng điều kiện lọc. Bằng cách kết hợp các hàm bảng với hàm tổng hợp, bạn có thể thực hiện các phép tính phức tạp trên các tập dữ liệu được xác định một cách linh động. Để biết thêm thông tin, hãy xem Tổng hợp trong Power Pivot.

Công thức và Mô hình Quan hệ

Cửa sổ Power Pivot là khu vực nơi bạn có thể làm việc với nhiều bảng dữ liệu và kết nối các bảng trong một mô hình quan hệ. Trong mô hình dữ liệu này, các bảng được kết nối với nhau bằng các mối quan hệ, cho phép bạn tạo mối tương quan với các cột trong bảng khác và tạo các phép tính thú vị hơn. Ví dụ, bạn có thể tạo công thức tính tổng các giá trị cho một bảng có liên quan, rồi lưu giá trị đó vào một ô duy nhất. Hoặc để kiểm soát các hàng từ bảng liên quan, bạn có thể áp dụng bộ lọc cho bảng và cột. Để biết thêm thông tin, hãy xem Mối quan hệ giữa các bảng trong Mô hình Dữ liệu.

Vì bạn có thể liên kết các bảng bằng cách sử dụng mối quan hệ, PivotTable của bạn cũng có thể bao gồm dữ liệu từ nhiều cột vốn từ các bảng khác nhau.

Tuy nhiên, vì công thức có thể hoạt động với toàn bộ bảng và cột, bạn cần thiết kế tính toán khác với trong Excel.

• Nói chung, một công thức DAX trong một cột luôn được áp dụng cho toàn bộ tập hợp giá trị trong cột đó (không bao giờ chỉ áp dụng cho một vài hàng hoặc ô).
• Các bảng trong Power Pivot phải luôn có cùng một số cột trong mỗi hàng và tất cả các hàng trong cột phải chứa cùng một kiểu dữ liệu.
• Khi các bảng được kết nối bằng một mối quan hệ, bạn phải đảm bảo rằng hai cột được dùng làm khóa có các giá trị khớp nhau, phần lớn. Vì Power Pivot không buộc tính toàn vẹn tham chiếu, nên có thể có các giá trị không khớp trong cột khóa mà vẫn tạo ra một mối quan hệ. Tuy nhiên, sự hiện diện của các giá trị trống hoặc không khớp có thể ảnh hưởng đến kết quả của công thức và giao diện của PivotTable. Để biết thêm thông tin, hãy xem Tra cứu trong Công thức Power Pivot.
• Khi bạn liên kết các bảng bằng cách sử dụng các mối quan hệ, bạn sẽ mở rộng phạm vi hoặc ngữ cảnh trong đó các công thức của bạn được đánh giá. Ví dụ: mọi bộ lọc hoặc đề mục hàng và cột trong PivotTable có thể ảnh hưởng đến các công thức trong PivotTable. Bạn có thể viết công thức thao túng ngữ cảnh, nhưng ngữ cảnh cũng có thể khiến kết quả của bạn thay đổi theo cách mà bạn có thể không lường trước được. Để biết thêm thông tin, hãy xem Ngữ cảnh trong Công thức DAX.

Cập nhật kết quả của các công thức

Làm mới và tính toán lại dữ liệu là hai thao tác riêng biệt nhưng có liên quan mà bạn cần hiểu khi thiết kế mô hình dữ liệu chứa các công thức phức tạp, lượng lớn dữ liệu hoặc dữ liệu lấy từ các nguồn dữ liệu bên ngoài.

Làm mới dữ liệu là quy trình cập nhật dữ liệu trong sổ làm việc của bạn với dữ liệu mới từ một nguồn dữ liệu bên ngoài. Bạn có thể làm mới dữ liệu theo cách thủ công trong các khoảng thời gian mà bạn chỉ định. Hoặc, nếu bạn đã phát hành sổ làm việc đến site SharePoint, bạn có thể lên lịch làm mới tự động từ các nguồn bên ngoài.

Tính toán lại là quy trình cập nhật kết quả của các công thức để phản ánh bất kỳ thay đổi nào đối với chính các công thức đó và để phản ánh những thay đổi đó trong dữ liệu cơ bản. Việc tính toán lại có thể ảnh hưởng đến hiệu suất theo những cách sau đây:

• Đối với cột được tính, kết quả của công thức phải luôn được tính toán lại cho toàn bộ cột, bất kỳ khi nào bạn thay đổi công thức.
• Đối với giá trị đo, kết quả của công thức không được tính cho đến khi số đo được đặt trong ngữ cảnh của PivotTable hoặc PivotChart. Công thức cũng sẽ được tính toán lại khi bạn thay đổi bất kỳ đầu đề hàng hoặc cột nào ảnh hưởng đến bộ lọc trên dữ liệu hoặc khi bạn làm mới PivotTable theo cách thủ công.

Khắc phục sự cố công thức

Lỗi khi viết công thức

Nếu bạn gặp lỗi khi xác định công thức, công thức đó có thể chứa lỗi cú pháp, lỗi ngữ nghĩa hoặc lỗi tính toán.

Lỗi cú pháp là cách dễ giải quyết nhất. Chúng thường bao gồm thiếu dấu ngoặc đơn hoặc dấu phẩy. Để được trợ giúp về cú pháp của các hàm riêng lẻ, hãy xem Tham khảo Hàm DAX.

Loại lỗi khác xảy ra khi cú pháp chính xác, nhưng giá trị hoặc cột được tham chiếu không có ý nghĩa trong ngữ cảnh của công thức. Các lỗi ngữ nghĩa và tính toán này có thể do bất kỳ vấn đề nào sau đây gây ra:

• Công thức tham chiếu đến một cột, bảng hoặc hàm không tồn tại.
• Công thức có vẻ chính xác, nhưng khi công cụ dữ liệu tải dữ liệu, nó phát hiện ra sự không khớp kiểu và xuất hiện lỗi.
• Công thức chuyển một số lượng hoặc kiểu tham số không chính xác đến một hàm.
• Công thức tham chiếu đến một cột khác có lỗi, do đó giá trị của công thức không hợp lệ.
• Công thức tham chiếu đến một cột chưa được xử lý, nghĩa là cột có siêu dữ liệu nhưng không có dữ liệu thực để sử dụng cho các phép tính.

Trong bốn trường hợp đầu tiên, DAX gắn cờ cho toàn bộ cột chứa công thức không hợp lệ. Trong trường hợp cuối cùng, DAX làm xám cột để biểu thị cột đang ở trạng thái chưa xử lý.

Kết quả không chính xác hoặc bất thường khi xếp hạng hoặc sắp xếp thứ tự các giá trị cột

Khi xếp hạng hoặc xếp thứ tự một cột chứa giá trị NaN (Không phải Số), bạn có thể nhận được kết quả sai hoặc không mong muốn. Ví dụ: khi phép tính chia 0 cho 0, kết quả NaN được trả về.

Điều này là do công cụ công thức thực hiện xếp thứ tự và xếp hạng bằng cách so sánh các giá trị số; Tuy nhiên, NaN không thể so sánh được với các số khác trong cột.

Để đảm bảo kết quả chính xác, bạn có thể sử dụng câu lệnh điều kiện sử dụng hàm IF để kiểm định giá trị NaN và trả về giá trị số 0.

Tính tương thích với các mô hình dạng bảng Dịch vụ Phân tích và Chế độ Truy vấn Trực tiếp

Nói chung, các công thức DAX mà bạn tạo trong PowerPivot hoàn toàn tương thích với các mô hình dạng bảng Dịch vụ Phân tích. Tuy nhiên, nếu bạn di chuyển mô hình Power Pivot của mình sang một phiên bản Analysis Services, rồi triển khai mô hình trong chế độ Truy vấn Trực tiếp, sẽ có một số giới hạn.

• Một số công thức DAX có thể trả về các kết quả khác nếu bạn triển khai mô hình trong chế độ Truy vấn Trực tiếp.
• Một số công thức có thể gây ra lỗi xác thực khi bạn triển khai mô hình vào chế độ Truy vấn Trực tiếp vì công thức chứa hàm DAX không được hỗ trợ đối với nguồn dữ liệu có quan hệ.

Để biết thêm thông tin, hãy xem tài liệu lập mô hình dạng bảng của Dịch vụ Phân tích trong Sách Trực tuyến của SQL Server 2012.