Ngữ cảnh cho phép bạn thực hiện phân tích động, trong đó kết quả của công thức có thể thay đổi để phản ánh hàng hoặc vùng chọn ô hiện tại cũng như mọi dữ liệu liên quan. Hiểu ngữ cảnh và sử dụng ngữ cảnh hiệu quả là rất quan trọng để xây dựng các công thức hiệu suất cao, phân tích động và khắc phục sự cố trong công thức.
Phần này xác định các loại ngữ cảnh khác nhau: ngữ cảnh hàng, ngữ cảnh truy vấn và ngữ cảnh bộ lọc. Bài báo giải thích ngữ cảnh được đánh giá như thế nào đối với các công thức trong cột được tính toán và trong PivotTable.
Phần cuối cùng của bài viết này cung cấp liên kết đến các ví dụ chi tiết minh họa cách kết quả của công thức thay đổi theo ngữ cảnh.
Hiểu ngữ cảnh
Các công thức trong Power Pivot thể bị ảnh hưởng bởi các bộ lọc được áp dụng trong PivotTable, bởi mối quan hệ giữa các bảng và bởi các bộ lọc được dùng trong công thức. Ngữ cảnh là điều giúp bạn có thể thực hiện phân tích động. Hiểu ngữ cảnh rất quan trọng để xây dựng và khắc phục sự cố công thức.
Có các loại ngữ cảnh khác nhau: ngữ cảnh hàng, ngữ cảnh truy vấn và ngữ cảnh bộ lọc.
Ngữ cảnh hàng có thể được hiểu là "hàng hiện tại". Nếu bạn đã tạo cột được tính, ngữ cảnh hàng sẽ bao gồm các giá trị trong mỗi hàng và giá trị riêng lẻ trong các cột có liên quan đến hàng hiện tại. Cũng có một số hàm (SỚM HƠN và SỚM NHẤT) nhận giá trị từ hàng hiện tại, rồi sử dụng giá trị đó trong khi thực hiện thao tác trên toàn bộ bảng.
Ngữ cảnh truy vấn tham chiếu đến tập dữ liệu con được tạo ngầm cho mỗi ô trong PivotTable, tùy thuộc vào tiêu đề hàng và cột.
Ngữ cảnh bộ lọc là tập hợp các giá trị được cho phép trong mỗi cột, dựa trên các ràng buộc của bộ lọc đã được áp dụng cho hàng hoặc được xác định bởi biểu thức bộ lọc bên trong công thức.
Ngữ cảnh Hàng
Nếu bạn tạo công thức trong một cột được tính, ngữ cảnh hàng cho công thức đó sẽ bao gồm các giá trị từ tất cả các cột trong hàng hiện tại. Nếu bảng có liên quan đến một bảng khác, nội dung cũng bao gồm tất cả các giá trị từ bảng khác đó có liên quan đến hàng hiện tại.
Ví dụ: giả sử bạn tạo cột được tính, =[Cước_phí] + [Thuế],
, mà cộng hai cột lại với nhau từ cùng một bảng. Công thức này hoạt động như các công thức trong bảng Excel, tự động tham chiếu các giá trị từ cùng một hàng. Lưu ý rằng bảng khác với phạm vi: bạn không thể tham chiếu giá trị từ hàng trước hàng hiện tại bằng cách sử dụng ký tự phạm vi và bạn không thể tham chiếu bất kỳ giá trị đơn tùy ý nào trong bảng hoặc ô. Bạn phải luôn làm việc với bảng và cột.
Ngữ cảnh hàng sẽ tự động tuân theo mối quan hệ giữa các bảng để xác định hàng nào trong các bảng liên quan được liên kết với hàng hiện tại.
Ví dụ: công thức sau đây sử dụng hàm RELATED để tải giá trị thuế từ bảng có liên quan, dựa trên vùng mà đơn hàng đã được giao đến. Giá trị thuế được xác định bằng cách sử dụng giá trị cho khu vực trong bảng hiện tại, tra cứu khu vực trong bảng có liên quan, rồi nhận thuế suất cho khu vực đó từ bảng có liên quan.
= [Cước_phí] + RELATED('Khu_vực'[Thuế_suất])
Công thức này chỉ lấy thuế suất cho khu vực hiện tại, từ bảng Khu vực. Bạn không cần phải biết hoặc chỉ định khóa kết nối các bảng.
Ngữ cảnh Nhiều Hàng
Ngoài ra, DAX bao gồm các hàm lặp tính toán trên một bảng. Các hàm này có thể có nhiều hàng hiện tại và ngữ cảnh hàng hiện tại. Trong thuật ngữ lập trình, bạn có thể tạo công thức lặp lại qua vòng lặp bên trong và bên ngoài.
Ví dụ: giả sử sổ làm việc của bạn chứa một bảng Sản phẩm và một bảng Doanh số. Bạn có thể muốn xem qua toàn bộ bảng bán hàng, với đầy đủ các giao dịch liên quan đến nhiều sản phẩm và tìm số lượng lớn nhất được đặt hàng cho mỗi sản phẩm trong bất kỳ giao dịch nào.
Trong Excel, phép tính này đòi hỏi một chuỗi các bản tóm tắt trung gian, vốn sẽ phải được xây dựng lại nếu dữ liệu thay đổi. Nếu bạn là người dùng Excel toàn năng, bạn có thể xây dựng các công thức mảng có thể thực hiện công việc đó. Ngoài ra, trong một cơ sở dữ liệu quan hệ, bạn có thể viết các lựa chọn con lồng nhau.
Tuy nhiên, với DAX, bạn có thể xây dựng một công thức trả về giá trị chính xác và kết quả sẽ tự động được cập nhật bất kỳ khi nào bạn thêm dữ liệu vào bảng.
=MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])
Để biết hướng dẫn chi tiết về công thức này, hãy xem mục SỚM HƠN.
Tóm lại, hàm EARLIER lưu trữ ngữ cảnh hàng từ thao tác trước thao tác hiện tại. Tại mọi thời điểm, hàm lưu trữ trong bộ nhớ hai bộ ngữ cảnh: một bộ ngữ cảnh đại diện cho hàng hiện tại cho vòng lặp bên trong của công thức và một tập hợp ngữ cảnh khác đại diện cho hàng hiện tại cho vòng lặp bên ngoài của công thức. DAX tự động cấp dữ liệu giá trị giữa hai vòng lặp để bạn có thể tạo các tổng gộp phức tạp.
Ngữ cảnh Truy vấn
Ngữ cảnh truy vấn tham chiếu đến tập dữ liệu con được truy xuất ngầm cho công thức. Khi bạn thả một số đo hoặc trường giá trị khác vào một ô trong PivotTable, công cụ Power Pivot sẽ kiểm tra tiêu đề hàng và cột, Slicer và bộ lọc báo cáo để xác định ngữ cảnh. Sau đó, Power Pivot thực hiện các phép tính cần thiết để điền từng ô vào PivotTable. Tập dữ liệu được truy xuất là ngữ cảnh truy vấn cho từng ô.
Vì ngữ cảnh có thể thay đổi tùy thuộc vào vị trí bạn đặt công thức nên kết quả của công thức cũng thay đổi tùy thuộc vào việc bạn sử dụng công thức trong PivotTable có nhiều nhóm và bộ lọc hay trong cột được tính toán không có bộ lọc và ngữ cảnh tối thiểu.
Ví dụ: giả sử bạn tạo công thức đơn giản này để tính tổng các giá trị trong cột Lợi nhuận của bảng Doanh số:=SUM('Doanh số'[Lợi nhuận]).
Nếu bạn sử dụng công thức này trong cột được tính toán trong bảng Doanh số, kết quả cho công thức sẽ giống nhau đối với toàn bộ bảng, vì ngữ cảnh truy vấn cho công thức luôn là toàn bộ tập dữ liệu của bảng Doanh số. Kết quả của bạn sẽ có lợi nhuận cho tất cả khu vực, tất cả sản phẩm, mọi năm, v.v.
Tuy nhiên, thông thường bạn không muốn thấy cùng một kết quả hàng trăm lần nhưng thay vào đó, bạn muốn có lợi nhuận cho một năm cụ thể, một quốc gia hoặc khu vực cụ thể, một sản phẩm cụ thể hoặc một sự kết hợp nào đó, rồi nhận được tổng cuối.
Trong PivotTable, bạn có thể dễ dàng thay đổi ngữ cảnh bằng cách thêm hoặc loại bỏ tiêu đề cột và hàng, cũng như bằng cách thêm hoặc loại bỏ Slicer. Bạn có thể tạo công thức như công thức ở trên, theo một số đo, rồi thả công thức đó vào PivotTable. Bất cứ khi nào bạn thêm đầu đề cột hoặc hàng vào PivotTable, bạn sẽ thay đổi ngữ cảnh truy vấn đánh giá số đo. Thao tác licing và lọc cũng ảnh hưởng đến ngữ cảnh. Do đó, cùng một công thức, được sử dụng trong PivotTable, được đánh giá trong ngữ cảnh truy vấn khác cho mỗi ô.
Lọc Ngữ cảnh
Ngữ cảnh bộ lọc được thêm vào khi bạn chỉ định ràng buộc bộ lọc trên tập giá trị được cho phép trong một cột hoặc bảng, bằng cách sử dụng các đối số cho một công thức. Ngữ cảnh bộ lọc áp dụng cho các ngữ cảnh khác, chẳng hạn như ngữ cảnh hàng hoặc ngữ cảnh truy vấn.
Ví dụ: PivotTable tính toán giá trị của nó cho mỗi ô dựa trên đầu đề hàng và cột, như được mô tả trong phần trước trên ngữ cảnh truy vấn. Tuy nhiên, trong các số đo hoặc cột được tính mà bạn thêm vào PivotTable, bạn có thể chỉ định biểu thức bộ lọc để kiểm soát các giá trị mà công thức sử dụng. Bạn cũng có thể xóa có chọn lọc trên các cột cụ thể.
Để biết thêm thông tin về cách tạo bộ lọc trong công thức, hãy xem mục Hàm lọc.
Để biết ví dụ về cách có thể xóa bộ lọc để tạo tổng cuối, hãy xem mục TẤT CẢ.
Để biết ví dụ về cách xóa và áp dụng có chọn lọc trong công thức, hãy xem Hàm ALLEXCEPT.
Do đó, bạn phải xem lại định nghĩa của các số đo hoặc công thức được sử dụng trong PivotTable để bạn biết ngữ cảnh của bộ lọc khi diễn giải kết quả của công thức.
Xác định Ngữ cảnh trong Công thức
Khi bạn tạo một công thức, Power Pivot cho Excel trước tiên sẽ kiểm tra cú pháp chung, rồi kiểm tra tên của các cột và bảng mà bạn cung cấp dựa trên các cột và bảng có thể có trong ngữ cảnh hiện tại. Nếu Power Pivot thể tìm thấy các cột và bảng được chỉ định bởi công thức, bạn sẽ gặp lỗi.
Ngữ cảnh được xác định như mô tả trong các phần trước, bằng cách sử dụng các bảng sẵn dùng trong sổ làm việc, mọi mối quan hệ giữa các bảng và bất kỳ bộ lọc nào đã được áp dụng.
Ví dụ: nếu bạn vừa mới nhập một số dữ liệu vào một bảng mới và chưa áp dụng bất kỳ bộ lọc nào, toàn bộ tập hợp các cột trong bảng sẽ là một phần của ngữ cảnh hiện tại. Nếu bạn có nhiều bảng được liên kết theo mối quan hệ và bạn đang làm việc trong PivotTable đã được lọc bằng cách thêm đầu đề cột và sử dụng Slicer, ngữ cảnh sẽ bao gồm các bảng liên quan và mọi bộ lọc trên dữ liệu.
Ngữ cảnh là một khái niệm mạnh mẽ cũng có thể khiến bạn khó khắc phục sự cố công thức. Chúng tôi khuyên bạn nên bắt đầu với các công thức và mối quan hệ đơn giản để xem ngữ cảnh hoạt động như thế nào, rồi bắt đầu thử nghiệm với các công thức đơn giản trong PivotTable. Phần sau đây cũng cung cấp một số ví dụ về cách các công thức sử dụng các loại ngữ cảnh khác nhau để tự động trả về kết quả.
Ví dụ về Ngữ cảnh trong Công thức
-
Hàm RELATED bung rộng ngữ cảnh của hàng hiện tại để bao gồm các giá trị trong một cột có liên quan. Điều này cho phép bạn thực hiện tra cứu. Ví dụ trong chủ đề này minh họa sự tương tác của việc lọc và ngữ cảnh hàng.
-
Hàm FILTER cho phép bạn chỉ định các hàng cần đưa vào ngữ cảnh hiện tại. Các ví dụ trong chủ đề này cũng minh họa cách nhúng bộ lọc bên trong các hàm khác thực hiện tổng hợp.
-
Hàm ALL đặt ngữ cảnh trong một công thức. Bạn có thể dùng nó để ghi đè lên các bộ lọc được áp dụng theo ngữ cảnh truy vấn.
-
Hàm ALLEXCEPT cho phép bạn loại bỏ tất cả các bộ lọc ngoại trừ bộ lọc mà bạn chỉ định. Cả hai chủ đề đều bao gồm các ví dụ hướng dẫn bạn cách xây dựng công thức và tìm hiểu ngữ cảnh phức tạp.
-
Các hàm SỚM HƠN và SỚM NHẤT cho phép bạn di chuyển vòng lặp qua các bảng bằng cách thực hiện tính toán, trong khi tham chiếu một giá trị từ vòng lặp bên trong. Nếu bạn quen thuộc với khái niệm lặp lại và với các vòng lặp bên trong và bên ngoài, bạn sẽ đánh giá cao sức mạnh mà các hàm EARLIER và EARLIEST cung cấp. Nếu bạn mới dùng những khái niệm này, bạn nên làm theo các bước trong ví dụ một cách cẩn thận để xem ngữ cảnh bên trong và bên ngoài được sử dụng như thế nào trong tính toán.
Tính toàn vẹn tham chiếu
Mục này thảo luận về một số khái niệm nâng cao liên quan đến giá trị bị thiếu Power Pivot bảng được kết nối bằng mối quan hệ. Phần này có thể hữu ích cho bạn nếu bạn có sổ làm việc với nhiều bảng và công thức phức tạp và muốn được trợ giúp trong việc hiểu kết quả.
Nếu bạn mới dùng khái niệm dữ liệu quan hệ, trước tiên chúng tôi khuyên bạn nên đọc chủ đề giới thiệu, Tổng quan về Mối quan hệ.
Tính toàn vẹn tham chiếu và Power Pivot quan hệ
Power Pivot yêu cầu thực thi tính toàn vẹn tham chiếu giữa hai bảng để xác định mối quan hệ hợp lệ. Thay vào đó, hàng trống được tạo trên đầu "một" của mỗi mối quan hệ một-nhiều và được dùng để xử lý tất cả các hàng không khớp từ bảng có liên quan. Nó hoạt động hiệu quả như một nối ngoài SQL.
Trong PivotTable, nếu bạn nhóm dữ liệu theo một phía của mối quan hệ, mọi dữ liệu không khớp ở bên nhiều của mối quan hệ sẽ được nhóm lại với nhau và sẽ được đưa vào tổng với đầu đề hàng trống. Đầu đề trống tương đương với "phần tử không xác định".
Tìm hiểu về Thành viên Không xác định
Khái niệm về phần tử không xác định có thể quen thuộc với bạn nếu bạn đã làm việc với các hệ thống cơ sở dữ liệu đa chiều, chẳng hạn như SQL Server Analysis Services. Nếu thuật ngữ mới đối với bạn, ví dụ sau đây sẽ giải thích phần tử không xác định là gì và việc thuật ngữ đó ảnh hưởng đến các tính toán như thế nào.
Giả sử bạn đang tạo phép tính tính tổng doanh thu hàng tháng cho từng cửa hàng nhưng một cột trong bảng Doanh số lại thiếu một giá trị cho tên cửa hàng. Nếu các bảng cho Store và Doanhsố được kết nối bằng tên cửa hàng, điều gì sẽ xảy ra trong công thức? Nhóm PivotTable hoặc hiển thị số liệu bán hàng không liên quan đến cửa hàng hiện có như thế nào?
Sự cố này là một sự cố phổ biến trong kho dữ liệu, trong đó các bảng dữ liệu dữ liệu lớn phải có liên quan lô-gic đến bảng kích thước chứa thông tin về cửa hàng, khu vực và các thuộc tính khác được sử dụng để phân loại và tính toán dữ kiện. Để giải quyết vấn đề, bất kỳ dữ kiện mới không liên quan đến một thực thể hiện đang tạm thời được gán cho các thành viên không biết. Đó là lý do tại sao các dữ kiện không liên quan sẽ xuất hiện được nhóm trong PivotTable dưới một đầu đề trống.
Xử lý Giá trị Trống với Hàng Trống
Các giá trị trống khác với các hàng trống được thêm vào để phù hợp với phần tử không xác định. Giá trị trống là một giá trị đặc biệt được dùng để đại diện cho các giá trị null, chuỗi trống và các giá trị bị thiếu khác. Để biết thêm thông tin về giá trị trống, cũng như các kiểu dữ liệu DAX khác, hãy xem Kiểu dữ liệu trong Mô hình Dữ liệu.
