Однією з найпотужніших функцій 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 зв'язану таблицю, а потім створити зв'язки з таблицями "Працівник" і "Реселер", зіставити "Ідентифікатор працівника" з полем "Ключ працівника" та "Ідентифікатор реселерів" на поле ResellerKey.
Щоб відобразити відомості про підстановку у зв'язаній таблиці, додайте два нові обчислювані стовпці з такими формулами:
= RELATED('Employees'[EmployeeName])
= RELATED('Торговельні партнери'[Назва_компанії])
Сьогоднішні відправлення перед пошуком
|
OrderID |
Ідентифікатор працівника |
Ідентифікатор торговельного партнера |
|---|---|---|
|
100314 |
230 |
445 |
|
100315 |
15 |
445 |
|
100316 |
76 |
108 |
Таблиця працівників
|
Ідентифікатор працівника |
Співробітник |
Реселлером |
|---|---|---|
|
230 |
Kuppa Vamsi |
Системи модульного циклу |
|
15 |
Пілар Акеман |
Системи модульного циклу |
|
76 |
Кім Роллс |
Пов'язані велосипеди |
Сьогоднішні відправлення з підстановками
|
OrderID |
Ідентифікатор працівника |
Ідентифікатор торговельного партнера |
Співробітник |
Реселлером |
|---|---|---|---|---|
|
100314 |
230 |
445 |
Kuppa Vamsi |
Системи модульного циклу |
|
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 |
SO53494 |
|
|
445 |
SO71872 |
|
|
445 |
SO65233 |
|
|
445 |
SO59000 |
|
|
Ідентифікатор торговельного партнера |
Номер замовлення продажів |
|
|
Пов'язані велосипеди |
Примітка.: Оскільки функція RELATEDTABLE повертає таблицю, а не одне значення, її потрібно використовувати як аргумент функції, яка виконує операції з таблицями. Докладні відомості див. в статті Функція RELATEDTABLE.
