Подстановка в формулах Power Pivot

Одной из самых мощных функций в Power Pivot является возможность создания связей между таблицами, а затем использования связанных таблиц для подметки или фильтрации связанных данных. Связанные значения извлекались из таблиц с помощью языка формулPower Pivot, выражений анализа данных (DAX). DAX использует реляционную модель, поэтому может легко и точно извлечь связанные или соответствующие значения из другой таблицы или столбца. Если вы знакомы с функцией В ПРОСМОТР в Excel, эта функция в Power Pivot похожа, но гораздо проще внедрить.

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

Вычисляемые поля в Power Pivot

Вычисляемые столбцы в PowerPivot

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

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

Функции подытов

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

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

Примечание: Если вы знакомы с реляционными базами данных, то можете подумать, что в Power Pivot подытоги похожи на вложенные выписки по подпросам в Transact-SQL.

Ирисовка одного связанного значения

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

Предположим, у вас есть список сегодняшних отправлений в Excel. Однако в списке содержатся только номера сотрудников, номера заказов и ИД грузоотправителя, из-за этого отчет будет сложно читать. Чтобы получить дополнительные сведения, вы можете преобразовать этот список в связанную таблицу Power Pivot, а затем создать связи с таблицами "Сотрудник" и "Reseller", совпадая с полем EmployeeKey и "ResellerID" с полем ResellerKey.

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

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

Отправленные на сегодняшний день перед подыском

Код заказа

EmployeeID

ResellerID

100314

230

445

100315

15

445

100316

76

108

Таблица Employees

EmployeeID

Сотрудник

Reseller

230

Kuppa Vamsi

Модульные системы циклов

15

Василев

Модульные системы циклов

76

Юлимова

Связанные велосипеды

Сегодня отправленные с подытовкой

Код заказа

EmployeeID

ResellerID

Сотрудник

Reseller

100314

230

445

Kuppa Vamsi

Модульные системы циклов

100315

15

445

Василев

Модульные системы циклов

100316

76

108

Юлимова

Связанные велосипеды

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

Искомый список связанных значений

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

=COUNTROWS(RELATEDTABLE(ResellerSales_USD))

В этой формуле функция RELATEDTABLE сначала получает значение ResellerKey для каждого reseller в текущей таблице. (Указывать столбец "ИД" в формуле не требуется, так как Power Pivot используется существующая связь между таблицами.) Затем функция RELATEDTABLE получает все строки из таблицы ResellerSales_USD, которые связаны с каждым из reseller, и подсчитывают строки. Если между двумя таблицами нет прямой или косвенной связи, вы получите все строки из ResellerSales_USD таблицы.

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

Reseller

Записи в таблице продаж для этого торговых посредника

Модульные системы циклов

ИД reseller (ИД reseller)

SalesOrderNumber

445

SO53494

445

SO71872

445

SO65233

445

SO59000

ИД reseller (ИД reseller)

SalesOrderNumber

Связанные велосипеды

Примечание: Так как функция RELATEDTABLE возвращает таблицу, а не одно значение, ее необходимо использовать в качестве аргумента функции, которая выполняет операции с таблицами. Дополнительные сведения см. в функции RELATEDTABLE.

К началу страницы

Нужна дополнительная помощь?

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×