Одна из самых мощных функций 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 с полем EmployeeKey, а resellerID — с полем ResellerKey.
Чтобы отобразить данные подытовки в связанной таблице, добавьте два новых вычисляемом столбца со следующими формулами:
= RELATED('Employees'[EmployeeName])
= RELATED('Resellers'[CompanyName])
Сегодня отправленные перед подыском
|
Код заказа |
Employeeid |
ResellerID |
|---|---|---|
|
100314 |
230 |
445 |
|
100315 |
15 |
445 |
|
100316 |
76 |
108 |
Таблица Employees
|
Employeeid |
Сотрудника |
Реселлера |
|---|---|---|
|
230 |
Kuppa Vamsi |
Модульные системы циклов |
|
15 |
Г-на Мякяэмен |
Модульные системы циклов |
|
76 |
Химова Раусс |
Связанные велосипеды |
Сегодня отправленные с подытовкой
|
Код заказа |
Employeeid |
ResellerID |
Сотрудника |
Реселлера |
|---|---|---|---|---|
|
100314 |
230 |
445 |
Kuppa Vamsi |
Модульные системы циклов |
|
100315 |
15 |
445 |
Г-на Мякяэмен |
Модульные системы циклов |
|
100316 |
76 |
108 |
Химова Раусс |
Связанные велосипеды |
Функция использует связи между связанной таблицей и таблицей Employees и Resellers, чтобы получить правильное имя для каждой строки в отчете. Вы также можете использовать связанные значения для вычислений. Дополнительные сведения и примеры см. в функции RELATED.
Ирисовка списка связанных значений
Функция RELATEDTABLE следует за существующей связью и возвращает таблицу, содержающую все совпадающие строки из указанной таблицы. Предположим, например, что вы хотите узнать, сколько заказов каждый reseller разместил в этом году. Можно создать в таблице "Посредники" новый вычисляемой столбец, который содержит указанную ниже формулу, которая ищет записи для каждого посредника в таблице ResellerSales_USD и подсчитывает количество заказов, размещенных каждым из них.
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
В этой формуле функция RELATEDTABLE сначала получает значение ResellerKey для каждого resellerkey в текущей таблице. (Не нужно указывать столбец "ИД" в формуле, так как Power Pivot используется существующая связь между таблицами.) Затем функция RELATEDTABLE получает все строки из ResellerSales_USD, которые связаны с каждым reseller, и подсчитывает строки. Если между двумя таблицами нет прямой или косвенной связи, вы получите все строки из ResellerSales_USD таблицы.
Для систем модульного цикла торговых посредников в нашем примере базы данных в таблице продаж содержится четыре заказа, поэтому функция возвращает 4. Для связанных велосипедов у торговых посредников нет продаж, поэтому функция возвращает пустое место.
|
Реселлера |
Записи в таблице продаж для этого торговых посредников |
|
|---|---|---|
|
Модульные системы циклов |
ИД reseller (ИД reseller) |
SalesOrderNumber |
|
445 |
SO53494 |
|
|
445 |
SO71872 |
|
|
445 |
SO65233 |
|
|
445 |
SO59000 |
|
|
ИД reseller (ИД reseller) |
SalesOrderNumber |
|
|
Связанные велосипеды |
Примечание: Так как функция RELATEDTABLE возвращает таблицу, а не одно значение, ее необходимо использовать в качестве аргумента функции, которая выполняет операции с таблицами. Дополнительные сведения см. в сведениях о функции RELATEDTABLE.
