Одной из самых мощных функций в Power Pivot является возможность создавать связи между таблицами, а затем использовать связанные таблицы для поиска или фильтрации связанных данных. Связанные значения извлекаются из таблиц с помощью языка формул, предоставленногоPower Pivot, выражений анализа данных (DAX). DAX использует реляционную модель и поэтому может легко и точно извлекать связанные или соответствующие значения в другой таблице или столбце. Если вы знакомы с VLOOKUP в Excel, эта функция в Power Pivot аналогична, но гораздо проще реализовать.
Можно создавать формулы, которые выполняют подстановки как часть вычисляемого столбца или как часть меры для использования в сводной таблице или сводной диаграмме. Дополнительные сведения см. в следующих статьях:
Вычисляемые поля в Power Pivot
Вычисляемые столбцы в PowerPivot
В этом разделе описаны функции DAX, которые предоставляются для поиска, а также некоторые примеры использования функций.
Примечание: В зависимости от типа операции подстановки или формулы подстановки, которую вы хотите использовать, сначала может потребоваться создать связь между таблицами.
Общие сведения о функциях подстановки
Возможность поиска соответствующих или связанных данных из другой таблицы особенно полезна в ситуациях, когда текущая таблица имеет только идентификатор определенного типа, но необходимые данные (например, цена продукта, название или другие подробные значения) хранятся в связанной таблице. Это также полезно при наличии нескольких строк в другой таблице, связанной с текущей строкой или текущим значением. Например, можно легко получить все продажи, привязанные к определенному региону, магазину или продавцу.
В отличие от функций подстановки Excel, таких как VLOOKUP, основанных на массивах, или LOOKUP, которая получает первое из нескольких соответствующих значений, DAX следует существующим связям между таблицами, объединенными ключами, чтобы получить одно связанное значение, которое точно совпадает. DAX также может получить таблицу записей, связанных с текущей записью.
Примечание: Если вы знакомы с реляционными базами данных, вы можете представить подстановки в Power Pivot как похожие на вложенный оператор subselect в Transact-SQL.
Получение одного связанного значения
Функция RELATED возвращает одно значение из другой таблицы, связанное с текущим значением в текущей таблице. Вы указываете столбец, содержащий нужные данные, и функция следует существующим связям между таблицами, чтобы получить значение из указанного столбца в связанной таблице. В некоторых случаях функция должна следовать цепочке связей для получения данных.
Например, предположим, что у вас есть список современных отправлений в Excel. Однако список содержит только идентификатор сотрудника, номер заказа и номер идентификатора грузоотправителя, что делает отчет трудным для чтения. Чтобы получить нужные дополнительные сведения, можно преобразовать этот список в Power Pivot связанную таблицу, а затем создать связи с таблицами Employee и Reseller, сопоставляя EmployeeID с полем EmployeeKey и ResellerID с полем ResellerKey.
Чтобы отобразить данные подстановки в связанной таблице, добавьте два новых вычисляемых столбца со следующими формулами:
= RELATED('Employees'[EmployeeName])
= RELATED('Resellers'[CompanyName])
Сегодняшние поставки перед поиском
|
Код заказа |
Employeeid |
ResellerID |
|---|---|---|
|
100314 |
230 |
445 |
|
100315 |
15 |
445 |
|
100316 |
76 |
108 |
Таблица Employees
|
Employeeid |
Сотрудника |
Реселлера |
|---|---|---|
|
230 |
Куппа Vamsi |
Модульные системы циклов |
|
15 |
Пилар Акеман |
Модульные системы циклов |
|
76 |
Ким Ролс |
Связанные велосипеды |
Сегодняшние поставки с подстановками
|
Код заказа |
Employeeid |
ResellerID |
Сотрудника |
Реселлера |
|---|---|---|---|---|
|
100314 |
230 |
445 |
Куппа Vamsi |
Модульные системы циклов |
|
100315 |
15 |
445 |
Пилар Акеман |
Модульные системы циклов |
|
100316 |
76 |
108 |
Ким Ролс |
Связанные велосипеды |
Функция использует связи между связанной таблицей и таблицей Сотрудники и торговые посредники, чтобы получить правильное имя для каждой строки отчета. Для вычислений также можно использовать связанные значения. Дополнительные сведения и примеры см. в разделе RELATED Function.
Получение списка связанных значений
Функция RELATEDTABLE следует существующей связи и возвращает таблицу, содержащую все совпадающие строки из указанной таблицы. Например, предположим, что вы хотите узнать, сколько заказов было размещено каждым торговым посредником в этом году. Вы можете создать новый вычисляемый столбец в таблице "Торговые посредники", который содержит следующую формулу, которая ищет записи для каждого торгового посредника в таблице ResellerSales_USD и подсчитывает количество отдельных заказов, размещенных каждым торговым посредником.
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
В этой формуле функция RELATEDTABLE сначала получает значение ResellerKey для каждого торгового посредника в текущей таблице. (Не нужно указывать столбец идентификатора в любом месте формулы, так как Power Pivot использует существующую связь между таблицами.) Затем функция RELATEDTABLE получает все строки из таблицы ResellerSales_USD, связанные с каждым торговым посредником, и подсчитывает строки. Если между двумя таблицами нет прямой или косвенной связи, вы получите все строки из таблицы ResellerSales_USD.
Для систем модульного цикла торгового посредника в нашем примере базы данных в таблице sales есть четыре заказа, поэтому функция возвращает 4. Для связанных велосипедов торговый посредник не имеет продаж, поэтому функция возвращает пустое значение.
|
Реселлера |
Записи в таблице sales для этого торгового посредника |
|
|---|---|---|
|
Модульные системы циклов |
Идентификатор торгового посредника |
SalesOrderNumber |
|
445 |
SO53494 |
|
|
445 |
SO71872 |
|
|
445 |
SO65233 |
|
|
445 |
SO59000 |
|
|
Идентификатор торгового посредника |
SalesOrderNumber |
|
|
Связанные велосипеды |
Примечание: Так как функция RELATEDTABLE возвращает таблицу, а не одно значение, ее необходимо использовать в качестве аргумента для функции, которая выполняет операции с таблицами. Дополнительные сведения см. в разделе RELATEDTABLE Function.
