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