Одной из самых мощных функций в 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.
