Связи и уточняющие запросы в формулах

Одной из самых мощных возможностей PowerPivot для Excel является возможность создания связей между таблицами и использования связанных таблиц для уточняющих запросов или фильтрования взаимосвязанных данных. Для получения связанных значений из таблиц используется язык формул DAX (выражения анализа данных), предоставляемый в PowerPivot для Excel. В языке DAX используется реляционная модель, что позволяет легко и точно получать связанные или соответствующие значения из другой таблицы или столбца.

Формулы, выполняющие уточняющие запросы, можно создавать в составе вычисляемых столбцов или в составе меры для использования в сводной таблице либо сводной диаграмме. Дополнительные сведения см. в разделах:

Создание меры

Создание вычисляемого столбца

В этом разделе описываются функции языка DAX, которые поддерживаются для поиска, а также ряд примеров использования этих функций.

Примечание
В зависимости от типа используемой операции уточняющего запроса или формулы уточняющего запроса сначала может понадобиться создать связь между таблицами. Сведения о создании связей см. в разделе Связи между таблицами.

В зависимости от типа используемой операции уточняющего запроса или формулы уточняющего запроса сначала может понадобиться создать связь между таблицами. Сведения о создании связей см. в разделе Связи между таблицами.

Основные сведения о функциях поиска

Возможность поиска совпадающих или взаимосвязанных данных из другой таблицы особенно полезна в случаях, когда текущая таблица содержит только идентификатор некоторого типа, а необходимые данные (например, цена товара, наименование или другие необходимые конкретные значения) хранятся в связанной таблице. Это также полезно, если в другой таблице содержится несколько строк, связанных с текущей строкой или текущим значением. Например, можно легко получить все продажи, связанные с определенным регионом, магазином или менеджером по продажам.

В отличие от функций поиска Excel, например VLOOKUP, которые основаны на массивах, или LOOKUP, которые возвращают первое из нескольких совпадающих значений, DAX проходит по существующим связям между таблицами, соединенных по ключам, чтобы получить единственное связанное значение, обеспечивающее точное совпадение. DAX также возвращает таблицу записей, связанных с текущей записью.

Примечание
Пользователь, знакомый с реляционными базами данных, может представить уточняющие запросы в PowerPivot в виде вложенной инструкции SELECT в языке Transact-SQL.

Дополнительные сведения о реляционной модели, используемой в PowerPivot, см. в разделе Общие сведения о связях.

Получение единственного связанного значения

Функция RELATED возвращает одно значение из другой таблицы, которое связано с текущим значением в текущей таблице. Пользователь задает столбец, содержащий нужные данные, а функция проходит по существующим связям между таблицами, чтобы выбрать значение из указанного столбца в связанной таблице. В некоторых случаях функции необходимо пройти по цепочке связей, чтобы получить данные.

Например, допустим, что список отгрузок на текущую дату содержится в Excel. Однако список, который содержит только идентификатор сотрудника, идентификатор заказа и идентификатор отправителя, делает отчет неудобным для чтения. Чтобы получить нужные дополнительные данные, можно преобразовать список в связанную таблицу PowerPivot, а затем создать связи с таблицами Employee и Reseller, сопоставив EmployeeID с полем EmployeeKey, а ResellerID с полем ResellerKey.

Чтобы отобразить данные уточняющих запросов в связанной таблице, добавляются два новых вычисляемых столбца со следующими формулами:

= RELATED('Employees'[EmployeeName])
= RELATED('Resellers'[CompanyName])

Перед поиском	После поиска
Order IDEmployeeIDResellerID 100314230445 10031515445 10031676108 EmployeeIDEmployeeReseller 230Kuppa VamsiModular Cycle Systems 15Pilar AckemanModular Cycle Systems 76Kim RallsAssociated Bikes	Order IDEmployeeIDResellerIDEmployeeReseller 100314230445Kuppa VamsiModular Cycle Systems 10031515445Pilar AckemanModular Cycle Systems 10031676108Kim RallsAssociated Bikes

Функция использует связи между связанной таблицей и таблицами Employees и Resellers, чтобы получить правильное имя для каждой строки в отчете. Связанные значения также можно использовать в вычислениях. Дополнительные сведения и примеры см. в разделе Функция RELATED (DAX).

Получение списка связанных значений

Функция RELATEDTABLE проходит по существующей связи и возвращает таблицу, содержащую все совпадающие строки из указанной таблицы. Например, пусть нужно выяснить, сколько заказов поместил в этом году каждый посредник. В таблице Resellers можно создать новый вычисляемый столбец со следующей формулой, которая ищет записи для каждого посредника в таблице ResellerSales_USD и определяет количество отдельных заказов по каждому посреднику. Эти таблицы являются частью образца книги DAX. Дополнительные сведения об образцах данных см. в разделе Получение образца данных для PowerPivot.

=COUNTROWS(RELATEDTABLE(ResellerSales_USD))

В этой формуле функция RELATEDTABLE сначала возвращает значение ResellerKey для каждого посредника в текущей таблице. (Указывать столбец идентификаторов в формуле не нужно, так как PowerPivot использует существующую связь между таблицами.) Функция RELATEDTABLE получает все строки из таблицы ResellerSales_USD, связанные с каждым посредником, и подсчитывает строки. Обратите внимание, что, если между двумя таблицами нет связи (прямой или косвенной), из таблицы ResellerSales_USD будут получены все строки.

Для посредника Modular Cycle Systems в нашем образце базы данных имеется четыре заказа в таблице продаж, поэтому функция возвращает значение 4. Для посредника Associated Bikes продаж нет, поэтому функция возвращает пустое значение.

Reseller	Записи в таблице продаж для этого посредника
Modular Cycle Systems	Reseller IDSalesOrderNumber 445SO53494 445SO71872 445SO65233 445SO59000
Associated Bikes

Примечание
Поскольку функция RELATEDTABLE возвращает не единичное значение, а таблицу, она должна использоваться в качестве аргумента для функции, выполняющей операции с таблицами. Дополнительные сведения см. в разделе Функция RELATEDTABLE (DAX).

Поскольку функция RELATEDTABLE возвращает не единичное значение, а таблицу, она должна использоваться в качестве аргумента для функции, выполняющей операции с таблицами. Дополнительные сведения см. в разделе Функция RELATEDTABLE (DAX).