Одна з найпотужніших функцій програми Power Pivot – можливість створювати зв'язки між таблицями, а потім використовувати пов'язані таблиці для підстановки або фільтрування пов'язаних даних. Пов'язані значення можна отримати з таблиць за допомогою мови формул, наданоїPower Pivot, виразів аналізу даних (DAX). У DAX використовується реляційна модель, тому можна легко й точно отримувати пов'язані або відповідні значення в іншій таблиці або стовпці. Якщо ви вже знайомі з функцією VLOOKUP Excel, ця Power Pivot у програмі 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 |
Ідентифікаційний номер працівника |
ResellerID (Id реселерів) |
|---|---|---|
|
100314 |
230 |
445 |
|
100315 |
15 |
445 |
|
100316 |
76 |
108 |
Таблиця працівників
|
Ідентифікаційний номер працівника |
Працівник |
Реселер |
|---|---|---|
|
230 |
Kuppa Vamsi |
Системи модульного циклу |
|
15 |
Pilar Ackeman |
Системи модульного циклу |
|
76 |
Кім Ранц |
Пов'язані велосипеди |
Сьогоднішні доставки з підстановками
|
OrderID |
Ідентифікаційний номер працівника |
ResellerID (Id реселерів) |
Працівник |
Реселер |
|---|---|---|---|---|
|
100314 |
230 |
445 |
Kuppa Vamsi |
Системи модульного циклу |
|
100315 |
15 |
445 |
Pilar Ackeman |
Системи модульного циклу |
|
100316 |
76 |
108 |
Кім Ранц |
Пов'язані велосипеди |
Функція використовує зв'язки між зв'язаною таблицею та таблицями "Працівники" й "Реселери", щоб отримати правильне ім'я для кожного рядка у звіті. Для обчислень також можна використовувати пов'язані значення. Докладні відомості та приклади див. в статті RELATED Function.
Отримання списку пов'язаних значень
Функція RELATEDTABLE має наявний зв'язок і повертає таблицю, яка містить усі відповідні рядки з указаної таблиці. Припустімо, наприклад, що потрібно дізнатися, скільки замовлень розмістив кожен реселер цього року. Ви можете створити новий обчислюваний стовпець у таблиці "Реселери", який містить наведену нижче формулу, яка шукає записи для кожного реселера в таблиці ResellerSales_USD і підраховує кількість окремих замовлень, розміщених у кожному реселері.
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
У цій формулі спочатку функція RELATEDTABLE отримує значення реселерівKey для кожного реселера в поточній таблиці. (Не потрібно вказувати стовпець "Ідентифікатор" у формулі, тому що Power Pivot використовує наявний зв'язок між таблицями.) Потім функція RELATEDTABLE отримує всі рядки з таблиці ResellerSales_USD, пов'язаних із кожним реселером, і рахує рядки. Якщо між двома таблицями немає зв'язку (прямого або непрямого), ви отримаєте всі рядки з ResellerSales_USD таблиці.
Для систем модульного циклу для реселерів у нашій вибірці бази даних у таблиці "Продажі" є чотири замовлення, тому функція повертає 4. Для пов'язаних велосипедів торговий партнер не має даних про продажі, тому функція повертає пусте значення.
|
Реселер |
Records in sales table for this reseller |
|
|---|---|---|
|
Системи модульного циклу |
Ідентифікатор реселера |
SalesOrderNumber |
|
445 |
SO53494 |
|
|
445 |
SO71872 |
|
|
445 |
SO65233 |
|
|
445 |
SO59000 |
|
|
Ідентифікатор реселера |
SalesOrderNumber |
|
|
Пов'язані велосипеди |
Примітка.: Оскільки функція RELATEDTABLE повертає таблицю, а не одне значення, її потрібно використовувати як аргумент для функції, яка виконує операції з таблицями. Докладні відомості див. в статті Функція RELATEDTABLE.
