Одна з найпотужніших функцій Power Pivot – це можливість створювати зв'язки між таблицями, а потім використовувати пов'язані таблиці для пошуку або фільтрування пов'язаних даних. Щоб отримувати пов'язані значення з таблиць, використовується мова формул, надана в надбудові Power Pivot (DAX). DAX використовує реляційну модель і тому може легко й точно отримати пов'язані або відповідні значення в іншій таблиці або стовпці. Якщо ви знайомі з функцією VLOOKUP у програмі Excel, можна побачити, що ця функція в надбудові Power Pivot схожа, але реалізувати її набагато простіше.
Ви можете створювати формули з підстановками в обчислюваному стовпці або мірі для використання у зведеній таблиці чи зведеній діаграмі. Докладні відомості див. в таких статтях:
Обчислювані поля в надбудові Power Pivot
Обчислювані стовпці в надбудові Power Pivot
У цьому розділі описано функції DAX, які надаються для підстановки, а також наведено приклади використання цих функцій.
Примітка.
Залежно від типу операції підстановки або формули підстановки, яку потрібно використати, може знадобитися спочатку створити зв'язок між таблицями.
Докладні відомості про функції підстановки
Можливість шукати пов'язані або зіставлені дані з іншої таблиці особливо корисна, коли поточна таблиця має лише певний ідентифікатор, а необхідні дані (наприклад, ціну товару, назву або інші докладні значення) зберігаються в пов'язаній таблиці. Також корисно, коли в іншій таблиці, пов'язаній із поточним рядком або поточним значенням, є кілька рядків. Наприклад, ви можете легко отримати всі продажі, пов'язані з певним регіоном, магазином або продавцем.
На відміну від функцій підстановки Excel, як-от VLOOKUP, що базується на масивах, або LOOKUP, яка отримує перше з кількох збігів, формули DAX використовують наявні зв'язки між таблицями, з'єднаними ключами, щоб отримати єдине пов'язане значення, яке точно збігається. DAX також може отримати таблицю записів, пов'язаних із поточним записом.
Примітка.
Якщо ви знайомі з реляційними базами даних, підстановки в надбудові Power Pivot схожі на вкладену інструкцію вкладеного вибору в Transact-SQL.
Отримання одного пов'язаного значення
Функція RELATED повертає єдине значення з іншої таблиці, пов'язане з поточним значенням у поточній таблиці. Укажіть стовпець, який містить потрібні дані, а функція відповідає наявним зв'язкам між таблицями, щоб отримати значення з указаного стовпця зв'язаної таблиці. У деяких випадках для отримання даних функція має відповідати ланцюжку зв'язків.
Припустімо, наприклад, що у вас є список сьогоднішніх відправлень у програмі Excel. Однак список містить лише ідентифікаційний номер працівника, номер замовлення та ідентифікаційний номер служби доставки, що ускладнює читання звіту. Щоб отримати додаткову інформацію, ви можете перетворити цей список на зв'язану таблицю Power Pivot, а потім створити зв'язки з таблицями "Працівники" та "Торговельні партнери", зіставивши EmployeeID з полем EmployeeKey та ResellerID з полем ResellerKey.
Щоб відобразити відомості підстановки у зв'язаній таблиці, потрібно додати два нові обчислювані стовпці з такими формулами:
= RELATED('Employees'[EmployeeName])
= RELATED('Торговельні посередники'[Назва_компанії])
Сьогоднішні відправлення до пошуку
| OrderID | Ідентифікаційний номер працівника | Ідентифікатор торговельного партнера |
|---|---|---|
| 100314 | 230 | 445 |
| 100315 | 15 | 445 |
| 100316 | 76 | 108 |
Таблиця працівників
| Ідентифікаційний номер працівника | Працівник | Торговельний партнер |
|---|---|---|
| 230 | Куппа Вамсі | Модульні циклічні системи |
| 15 | Пілар Акеман | Модульні циклічні системи |
| 76 | Кім Раллс | Пов'язані велосипеди |
Сьогоднішні відправлення з підстановками
| OrderID | Ідентифікаційний номер працівника | Ідентифікатор торговельного партнера | Працівник | Торговельний партнер |
|---|---|---|---|---|
| 100314 | 230 | 445 | Куппа Вамсі | Модульні циклічні системи |
| 100315 | 15 | 445 | Пілар Акеман | Модульні циклічні системи |
| 100316 | 76 | 108 | Кім Раллс | Пов'язані велосипеди |
Функція використовує зв'язки між зв'язаною таблицею та таблицею "Працівники та торговельні партнери", щоб отримати правильне ім'я для кожного рядка у звіті. Для обчислень можна також використовувати пов'язані значення. Докладні відомості та приклади див. у статті про функцію RELATED
Отримання списку пов'язаних значень
Функція RELATEDTABLE використовує наявний зв'язок і повертає таблицю, яка містить усі рядки з указаної таблиці, що збігаються. Припустімо, наприклад, що потрібно дізнатися, скільки замовлень розмістив кожен торговельний партнер цього року. Ви можете створити новий обчислюваний стовпець у таблиці "Торговельні партнери" з наведеною нижче формулою, що шукає записи для кожного торговельного партнера в таблиці ResellerSales_USD та підраховує кількість окремих замовлень, зроблених кожним торговельним партнером.
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
У цій формулі функція RELATEDTABLE спочатку отримує значення ResellerKey для кожного торговельного партнера в поточній таблиці. (Стовпець "Ідентифікатор" у формулі вказувати не потрібно, тому що надбудова Power Pivot використовує наявний зв'язок між таблицями.) Потім функція RELATEDTABLE отримує всі рядки з таблиці ResellerSales_USD, пов'язані з кожним торговельним партнером, і підраховує рядки. Якщо між двома таблицями немає зв'язку (прямого або опосередкованого), ви отримаєте всі рядки з таблиці ResellerSales_USD.
Для систем модульного циклу торговельного партнера в нашому зразку бази даних чотири замовлення в таблиці продажів, тому функція повертає 4. Для пов'язаних велосипедів торговельний партнер не має продажів, тому функція повертає пусте значення.
| Торговельний партнер | Записи в таблиці продажів для цього торговельного партнера |
|---|---|
| Модульні циклічні системи | Ідентифікатор торговельного партнера |
| 445 | |
| 445 | |
| 445 | |
| 445 | |
| Ідентифікатор торговельного партнера | |
| Пов'язані велосипеди |
Примітка.
Оскільки функція RELATEDTABLE повертає лише таблицю, а не одне значення, її слід використовувати як аргумент для функції, яка виконує операції з таблицями. Докладні відомості див. у статті про функцію RELATEDTABLE.