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 lựa chọn hàng hoặc ô hiện tại và cả mọi dữ liệu liên quan. Hiểu ngữ cảnh và sử dụng ngữ cảnh hiệu quả rất quan trọng để xây dựng công thức hiệu suất cao, phân tích động và khắc phục các vấn đề trong công thức.
Phần này xác định các kiểu ngữ cảnh khác nhau: ngữ cảnh hàng, ngữ cảnh truy vấn và ngữ cảnh lọc. Bài viết giải thích cách đánh giá ngữ cảnh cho công thức trong cột tính toán và trong PivotTable.
Phần cuối 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ông thức trong Power Pivot có 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à các bộ lọc được sử dụng trong công thức. Ngữ cảnh là những gì 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ó nhiều kiểu ngữ cảnh khác nhau: ngữ cảnh hàng, ngữ cảnh truy vấn và ngữ cảnh 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 bao gồm các giá trị trong từng hàng riêng lẻ và giá trị trong cột có liên quan đến hàng hiện tại. Cũng có một số hàm (EARLIER và EARLIEST) lấy 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 các tiêu đề hàng và cột.
Ngữ cảnh bộ lọc là tập hợp các giá trị được phép trong mỗi cột, dựa trên ràng buộc 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 trong công thức.
Ngữ cảnh Hàng
Nếu bạn tạo công thức trong cột được tính, ngữ cảnh hàng cho công thức đó sẽ bao gồm 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 một cột được tính, =[Cước_phí] + [Thuế], cộng hai cột từ cùng một bảng với nhau. Công thức này hoạt động giống như 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ý hiệu 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 theo dõi mối quan hệ giữa các bảng để xác định hàng nào trong bảng có 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 lãnh thổ mà đơn hàng đã được vận chuyển đế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 lấy thuế suất cho khu vực đó từ bảng liên quan.
= [Freight] + RELATED('Region'[TaxRate])
Công thức này đơn giản lấy thuế suất cho vùng hiện tại, từ bảng Vùng. Bạn không cần biết hoặc không cần xác định khóa kết nối các bảng.
Ngữ cảnh Nhiều Hàng
Ngoài ra, DAX còn bao gồm các hàm lặp lại các phép tính 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. Theo 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 . Bạn có thể muốn xem qua toàn bộ bảng bán hàng, nơi chứa đầ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, tính toán này yêu cầu một chuỗi các tóm tắt trung gian mà sẽ phải được dựng lại nếu dữ liệu thay đổi. Nếu bạn là một 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 để thực hiện công việc này. Ngoài ra, trong cơ sở dữ liệu quan hệ, bạn có thể viết select phụ 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ập nhật bất cứ 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 hàm EARLIER.
Nói 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 này lưu trữ hai tập hợp ngữ cảnh trong bộ 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à bộ 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 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 đến 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 tính toán cần thiết để điền vào 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 nơi 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 sử dụng công thức trong PivotTable với nhiều nhóm và bộ lọc hay trong cột được tính 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('Sales'[Profit])
Nếu bạn sử dụng công thức này trong một cột được tính toán bên trong bảng Sales , 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 Sales . 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ả các sản phẩm, tất cả các 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 mà thay vào đó, bạn muốn thu đượ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 sự kết hợp nào đó của những giá trị này, và sau đó có được tổng cộng.
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 và bằng cách thêm hoặc loại bỏ Slicer. Bạn có thể tạo công thức như trên trong 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 trong đó 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 dùng trong PivotTable sẽ được đánh giá trong ngữ cảnh truy vấn khác nhau cho mỗi ô.
Ngữ cảnh Bộ lọc
Ngữ cảnh bộ lọc được thêm vào khi bạn xác định ràng buộc bộ lọc trên tập hợp 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 công thức. Ngữ cảnh bộ lọc áp dụng bên trên các ngữ cảnh khác, 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, 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ể xác đị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 các hàm Bộ lọc.
Để biết ví dụ về cách xóa bộ lọc để tạo tổng cuối, hãy xem hàm ALL.
Để biết ví dụ về cách xóa có 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 thước đo hoặc công thức được sử dụng trong PivotTable để bạn nhận thức được ngữ cảnh 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, trước tiên Power Pivot cho Excel sẽ kiểm tra cú pháp chung, sau đó sẽ 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 khả thi trong ngữ cảnh hiện tại. Nếu Power Pivot không thể tìm thấy các cột và bảng được xác đị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 có sẵn trong sổ làm việc, mọi mối quan hệ giữa các bảng và mọi bộ lọc đã được áp dụng.
Ví dụ: nếu bạn vừa mới nhập một số dữ liệu vào bảng mới và chưa áp dụng bất kỳ bộ lọc nào thì toàn bộ tập hợp 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 nối 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ẽ mà cũng có thể làm cho việc khắc phụ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, 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 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 mở 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 liên quan. Thao tác 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 giữ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 bộ lọc vào 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 công cụ này để ghi đè các bộ lọc được áp dụng như là kết quả của 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 trừ một bộ lọc mà bạn chỉ định. Cả hai chủ đề đều bao gồm các ví dụ giúp hướng dẫn bạn cách xây dựng công thức và tìm hiểu các ngữ cảnh phức tạp.
- Hàm EARLIER và EARLIEST cho phép bạn đi 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 đến một giá trị từ vòng lặp bên trong. Nếu bạn đã quen với khái niệm đệ quy và 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 mang lại. Nếu bạn mới làm quen với các 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 bối cảnh bên trong và bên ngoài được dùng như thế nào trong tính toán.
Tính toàn vẹn tham chiếu
Phần này thảo luận một số khái niệm nâng cao liên quan đến các giá trị thiếu trong bảng Power Pivot được kết nối bằng các 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 trong việc hiểu kết quả.
Nếu bạn mới làm quen với các khái niệm về 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à quan hệ Power Pivot
Power Pivot không 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 sử 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 kết nối ngoài SQL.
Trong PivotTable, nếu bạn nhóm dữ liệu theo một bên của mối quan hệ, bất kỳ dữ liệu không khớp nào ở nhiều bên của mối quan hệ sẽ được nhóm lại với nhau và sẽ được bao gồm trong tổng với đầu đề hàng trống. Đầu đề trống gần 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
Bạn có thể quen thuộc với khái niệm phần tử không xác định 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ữ này chưa quen với bạn, ví dụ sau đây sẽ giải thích phần tử không xác định là gì và nó ảnh hưởng đến tính toán như thế nào.
Giả sử bạn đang tạo một phép tính tính tính tổng doanh số 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 giá trị cho tên cửa hàng. Cho rằng các bảng cho Cửa hàng và Bán hàng được kết nối bằng tên cửa hàng, bạn mong đợi điều gì sẽ xảy ra trong công thức? PivotTable nên nhóm 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à vấn đề thường gặp trong kho dữ liệu, nơi các bảng dữ liệu dữ kiện lớn phải có liên quan lô-gic với bảng thứ nguyên chứa thông tin về các kho hàng, 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, mọi dữ kiện mới không liên quan đến thực thể hiện có sẽ tạm thời được 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 lại xuất hiện trong một PivotTable dưới một tiêu đề trống.
Xử lý các giá trị trống so với hàng trống
Giá trị trống khác với các hàng trống được thêm vào để chứa phần tử không xác định. Giá trị trống là một giá trị đặc biệt được dùng để biểu thị 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 các kiểu dữ liệu trong Mô hình Dữ liệu.