Một trong các tính năng hiệu quả nhất trong Power Pivot là khả năng tạo mối quan hệ giữa các bảng, rồi dùng các bảng có liên quan để tra cứu hoặc lọc các dữ liệu liên quan. Bạn truy xuất các giá trị liên quan từ các bảng bằng cách sử dụng ngôn ngữ công thức được cung cấp cùng vớiPower Pivot, Biểu thức Phân tích Dữ liệu (DAX). DAX dùng một mô hình quan hệ và do đó có thể truy xuất các giá trị liên quan hoặc tương ứng một cách chính xác trong một bảng hoặc cột khác. Nếu bạn quen dùng VLOOKUP trong công Excel thì chức năng này Power Pivot giống nhau nhưng dễ thực hiện hơn nhiều.
Bạn có thể tạo công thức tra cứu như là một phần của cột được tính hoặc như là một phần của phép đo để sử dụng trong PivotTable hoặc PivotChart. Để biết thêm thông tin, hãy xem các chủ đề sau:
Trường được Tính toán trong PowerPivot
Cột được Tính trong Power Pivot
Phần này mô tả các hàm DAX được cung cấp cho tra cứu, cùng với một số ví dụ về cách sử dụng các hàm.
Lưu ý: Tùy thuộc vào kiểu thao tác tra cứu hoặc công thức tra cứu mà bạn muốn sử dụng, bạn có thể cần phải tạo mối quan hệ giữa các bảng trước tiên.
Tìm hiểu về các hàm tra cứu
Khả năng tra cứu khớp hoặc dữ liệu liên quan từ một bảng khác đặc biệt hữu ích trong trường hợp bảng hiện tại chỉ có mã định danh có một số loại nhưng dữ liệu bạn cần (chẳng hạn như giá sản phẩm, tên hoặc các giá trị chi tiết khác) được lưu trữ trong một bảng liên quan. Nó cũng hữu ích khi có nhiều hàng trong một bảng khác có liên quan đến hàng hoặc giá trị hiện tại. Ví dụ: bạn có thể dễ dàng truy xuất toàn bộ doanh số gắn với một khu vực, cửa hàng hoặc nhân viên bán hàng cụ thể.
Ngược lại với các hàm tra cứu Excel chẳng hạn như VLOOKUP, vốn được dựa trên mảng hoặc LOOKUP, vốn nhận được giá trị đầu tiên trong số nhiều giá trị khớp nhau, DAX tuân theo các mối quan hệ hiện có giữa các bảng được nối bằng phím để có được giá trị liên quan duy nhất khớp chính xác. DAX cũng có thể truy xuất bảng các bản ghi liên quan đến bản ghi hiện thời.
Lưu ý: Nếu bạn quen thuộc với các cơ sở dữ liệu quan hệ, bạn có thể xem tra cứu trong Power Pivot tương tự như câu lệnh chọn riêng lồng trong Transact-SQL.
Truy xuất Một Giá trị Liên quan Duy nhất
Hàm RELATED trả về một giá trị duy nhất từ một bảng khác liên quan đến giá trị hiện tại trong bảng hiện tại. Bạn chỉ định cột có chứa dữ liệu mà bạn muốn và hàm tuân theo các mối quan hệ hiện có giữa các bảng để lấy giá trị từ cột được chỉ định trong bảng có liên quan. Trong một số trường hợp, hàm phải tuân theo một chuỗi các mối quan hệ để truy xuất dữ liệu.
Ví dụ: giả sử bạn có danh sách các lô hàng trong hôm nay Excel. Tuy nhiên, danh sách chỉ chứa số ID nhân viên, số ID đơn hàng và số ID của công ty vận chuyển khiến báo cáo khó đọc. Để có thêm thông tin bạn muốn, bạn có thể chuyển đổi danh sách đó thành bảng được liên kết của Power Pivot, rồi tạo mối quan hệ cho bảng Nhân viên và Nhà bán lại, khớpID Nhân viên với trường EmployeeKey vàID của Nhà bán lại thành trường ResellerKey.
Để hiển thị thông tin tra cứu trong bảng đã nối kết của bạn, bạn thêm hai cột được tính mới bằng các công thức sau đây:
= RELATED('Employees'[Tên Nhân viên])
= RELATED('Resellers'[CompanyName])
Các lô hàng hôm nay trước khi tra cứu
|
OrderID |
ID Nhân viên |
RESEllerID |
|---|---|---|
|
100314 |
230 |
445 |
|
100315 |
15 |
445 |
|
100316 |
76 |
108 |
Bảng nhân viên
|
ID Nhân viên |
Nhân viên |
Người bán lại |
|---|---|---|
|
230 |
Kuppa Vamsi |
Hệ thống Vòng đời Mô-đun |
|
15 |
Pilar Ackeman |
Hệ thống Vòng đời Mô-đun |
|
76 |
Kim Ralls |
Xe đạp Liên kết |
Các lô hàng ngày nay có tra cứu
|
OrderID |
ID Nhân viên |
RESEllerID |
Nhân viên |
Người bán lại |
|---|---|---|---|---|
|
100314 |
230 |
445 |
Kuppa Vamsi |
Hệ thống Vòng đời Mô-đun |
|
100315 |
15 |
445 |
Pilar Ackeman |
Hệ thống Vòng đời Mô-đun |
|
100316 |
76 |
108 |
Kim Ralls |
Xe đạp Liên kết |
Hàm sử dụng các mối quan hệ giữa bảng đã liên kết với bảng Nhân viên và Bán lẻ để có tên chính xác cho từng hàng trong báo cáo. Bạn cũng có thể dùng giá trị liên quan cho các phép tính. Để biết thêm thông tin và ví dụ, hãy xem Hàm RELATED.
Truy xuất Danh sách các Giá trị Liên quan
Hàm RELATEDTABLE theo sau một mối quan hệ hiện có và trả về bảng có chứa tất cả các hàng khớp nhau từ bảng đã xác định. Ví dụ: giả sử bạn muốn tìm hiểu số lượng đơn đặt hàng mà mỗi nhà bán lại đã đặt trong năm nay. Bạn có thể tạo một cột được tính mới trong bảng Resellers bao gồm công thức sau đây, công thức này sẽ tìm kiếm các bản ghi cho mỗi nhà bán lẻ trong bảng ResellerSales_USD và đếm số đơn hàng riêng lẻ do mỗi nhà bán lẻ đặt.
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
Trong công thức này, hàm RELATEDTABLE trước tiên sẽ nhận được giá trị của ResellerKey cho mỗi nhà bán lại trong bảng hiện tại. (Bạn không cần phải xác định cột ID ở bất kỳ đâu trong công thức, vì Power Pivot dùng mối quan hệ hiện có giữa các bảng.) Sau đó, hàm RELATEDTABLE nhận được tất cả các hàng từ bảng ResellerSales_USD có liên quan đến từng nhà bán lại và đếm hàng. Nếu không có mối quan hệ (trực tiếp hoặc gián tiếp) giữa hai bảng, thì bạn sẽ nhận được tất cả các hàng từ bảng ResellerSales_USD bảng.
Đối với nhà bán lại Hệ thống Vòng đời Mô-đun trong cơ sở dữ liệu mẫu của chúng tôi, có bốn đơn hàng trong bảng doanh số, vì vậy hàm trả về 4. Đối với Xe đạp Liên kết, nhà bán lại không có doanh thu, vì vậy hàm trả về giá trị trống.
|
Người bán lại |
Các bản ghi trong bảng bán hàng cho nhà bán lẻ này |
|
|---|---|---|
|
Hệ thống Vòng đời Mô-đun |
ID Nhà bán lại |
SalesOrderNumber |
|
445 |
SO53494 |
|
|
445 |
SO71872 |
|
|
445 |
SO65233 |
|
|
445 |
SO59000 |
|
|
ID Nhà bán lại |
SalesOrderNumber |
|
|
Xe đạp Liên kết |
Lưu ý: Vì hàm RELATEDTABLE trả về một bảng, không phải là một giá trị duy nhất, nó phải được sử dụng như một đối số cho một hàm thực hiện các thao tác trên bảng. Để biết thêm thông tin, hãy xem Hàm RELATEDTABLE.
