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 vùng chọn hàng hoặc ô 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 một cách 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. Tài liệu giải thích cách đánh giá ngữ cảnh cho công thức trong các 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.
Tìm 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, do 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. Tìm hiểu ngữ cảnh rất quan trọng trong việc 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 một cột được tính, ngữ cảnh hàng sẽ bao gồm các giá trị trong mỗi hàng riêng lẻ và các giá trị trong các cột có liên quan đến hàng hiện tại. Cũng có một số hàm (EARLIER và EARLIEST) 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 tới 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 giá trị được phép trong mỗi cột, dựa trên các ràng buộc về 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 đó 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 từ cùng một bảng. Công thức này hoạt động giống như công thức Excel bảng, tự động tham chiếu 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 ghi chú 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 tự động theo sau 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 để lấy giá trị thuế từ bảng có liên quan, dựa trên khu vực đã giao đơn hàng. Giá trị thuế được xác định bằng cách dùng giá trị cho khu vực trong bảng hiện tại, tra tại khu vực trong bảng có liên quan, sau đó lấy thuế suất cho khu vực đó từ bảng có liên quan.
= [Freight] + RELATED('Region'[TaxRate])
Công thức này đơn giản là sẽ tính thuế suất cho khu vực hiện tại, từ bảng Khu vực. Bạn không cần 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 sao cho tính toán trên một bảng. Các hàm này có thể có nhiều hàng và ngữ cảnh hàng hiện tại. Trong các thuật ngữ lập trình, bạn có thể tạo các công thức lặp lại trên một 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 Bán hàng. Có thể bạn sẽ muốn xem qua toàn bộ bảng doanh số, có đầ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 từng sản phẩm trong bất kỳ giao dịch nào.
Trong Excel, tính toán 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 tái tạo nếu dữ liệu thay đổi. Nếu bạn là người dùng toàn năng của Excel, bạn có thể xây dựng những 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à các kết quả được tự động cập nhật bất kỳ lúc 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 phần TRƯỚC .
Nói ngắn gọn, 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 thể hiện 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 các giá trị giữa hai vòng lặp để bạn có thể tạo các tổng hợp phức tạp.
Ngữ cảnh Truy vấn
Ngữ cảnh truy vấn tham chiếu tới tập dữ liệu con được truy xuất ngầm cho một 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 các 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 để nhập từng ô trong PivotTable. Tập hợp dữ liệu được truy xuất là ngữ cảnh truy vấn cho mỗi ô.
Vì ngữ cảnh có thể thay đổi tùy thuộc vào vị trí bạn đặt công thức, kết quả của công thức cũng thay đổi tùy thuộc vào việc bạn 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 mà 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 một cột được tính toán trong bảng Doanh số, kết quả cho công thức sẽ giống nhau cho 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ả các khu vực, tất cả sản phẩm, tất cả năm, v.v..
Tuy nhiên, thông thường bạn không muốn nhì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ó đượ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 kết hợp nào đó của những sản phẩm này, sau đó có đượ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 đây, theo đơn vị đo, rồi thả 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 mà số đo được đánh giá. Các thao tác cắt 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 ô.
Ngữ cảnh Bộ lọc
Ngữ cảnh bộ lọc được thêm vào khi bạn chỉ định các giới hạn bộ lọc trên tập hợp các giá trị được 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 của bộ lọc áp dụng ở phía trên cùng của 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 đó về ngữ cảnh truy vấn. Tuy nhiên, bên trong các số đo hoặc cột được tính toán 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ị được 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 chính, hãy xem mục TẤT CẢ.
Để biết ví dụ về cách xóa chọn lọc và áp dụng bộ 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 công thức, Power Pivot cho Excel đầu tiên sẽ kiểm tra cú pháp chung, rồi kiểm tra tên của cột và bảng mà bạn cung cấp so với 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 dùng các bảng sẵn dùng trong sổ làm việc, bất kỳ mối quan hệ nào 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 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 bằng 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 việc khắc phục sự cố công thức trở nên khó khăn. 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. Mục 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 kiểu 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 để đưa các giá trị vào một cột 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 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 các 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 giúp bạn loại bỏ tất cả các bộ lọc ngoại trừ bộ lọc 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.
-
Hàm EARLIER và EARLIEST cho phép bạn lặp qua các bảng bằng cách thực hiện tính toán, đồng thời tham chiếu 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 sử 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 cách dùng ngữ cảnh bên trong và bên ngoài 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 các 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 chứa nhiều bảng và công thức phức tạp và muốn trợ giúp để hiểu kết quả.
Nếu bạn mới dùng khái niệm dữ liệu quan hệ, chúng tôi khuyên bạn trước tiê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ột mối quan hệ hợp lệ. Thay vào đó, một hàng trống được tạo ở đầ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. Phép nối bên ngoài hoạt động hiệu SQL bên ngoài.
Trong PivotTable, nếu bạn nhóm dữ liệu theo một bên của mối quan hệ thì mọi dữ liệu không khớp ở nhiều bên 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 xùng tương đương với "thành viên 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 với bạn, ví dụ sau đây sẽ giải thích thành viên không xác định là gì và việc này ảnh hưởng đến các tính toán như thế nào.
Giả sử bạn đang tạo một phép tính tính tổng doanh thu hàng tháng cho mỗi cửa hàng, nhưng một cột trong bảng Doanh số sẽ thiếu một giá trị cho tên cửa hàng. Trong trường hợp các bảng cho Storevà Sales được kết nối với tên cửa hàng, bạn nên kỳ vọng điều gì sẽ xảy ra trong công thức này? 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?
Vấn đề này là sự cố phổ biến trong các nhà kho dữ liệu, trong đó các bảng dữ liệu dữ kiện lớn phải có liên quan lô-gic đến bảng kích thước chứa thông tin về kho lưu trữ, khu vực và các thuộc tính khác được dùng để phân loại và tính toán dữ kiện. Để giải quyết vấn đề này, bất kỳ dữ kiện mới nào không liên quan đến một thực thể hiện có sẽ được tạm thời gán cho thành viên không xác định. Đó 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 tiêu đề trống.
Xử lý Giá trị Trống so với Hàng Trống
Giá trị trống khác với 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 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.
