Контекст дає змогу виконувати динамічний аналіз, у якому результати формули можуть змінюватися залежно від виділеного елемента, вибраного в рядку чи клітинці, а також пов'язаних даних. Розуміння контексту та ефективне використання контексту дуже важливі для створення високопродуктивних формул, динамічного аналізу та виправлення неполадок у формулах.
У цьому розділі описуються різні типи контексту: контекст рядка, контекст запиту та контекст фільтра. Тут пояснено, як контекст обчислюється для формул в обчислюваних стовпцях і зведених таблицях.
Остання частина цієї статті містить посилання на докладні приклади, у яких показано, як результати формул змінюються залежно від контексту.
Розуміння контексту
Фільтри, застосовані у зведеній таблиці, можуть впливати на формули в надбудові Power Pivot, зв'язки між таблицями та фільтри, використані у формулах. Контекст – це те, що дає змогу виконувати динамічний аналіз. Розуміння контексту важливе під час створення формул і виправлення неполадок.
Існують різні типи контексту: контекст рядка, контекст запиту та контекст фільтра.
Контекст рядка можна розглядати як "поточний рядок". Якщо створено обчислюваний стовпець, контекст рядка складається зі значень у кожному окремому рядку та значень у стовпцях, пов'язаних із поточним рядком. Також є деякі функції (РАНІШЕ та НАЙРАНІШІ), які отримують значення з поточного рядка та використовують це значення, виконуючи операцію над усією таблицею.
Контекст запиту – це підмножина даних, які неявно створюються для кожної клітинки зведеної таблиці залежно від заголовків рядків і стовпців.
Контекст фільтра – це набір значень, дозволених у кожному стовпці на основі обмежень фільтра, застосованих до рядка або визначених виразами фільтра у формулі.
Контекст рядка
Якщо створити формулу в обчислюваному стовпці, контекст рядка для цієї формули включатиме значення з усіх стовпців поточного рядка. Якщо таблиця пов'язана з іншою таблицею, вміст також включає всі значення з цієї іншої таблиці, пов'язані з поточним рядком.
Наприклад, ви створили обчислюваний стовпець =[Вартість доставки] + [Податок], який підсумовує два стовпці з однієї таблиці. Ця формула схожа на формули в таблиці Excel, які автоматично посилаються на значення з одного рядка. Зверніть увагу, що таблиці відрізняються від діапазонів: ви не можете посилатися на значення з рядка перед поточним рядком за допомогою нотації діапазону, а також не можете посилатися на будь-яке довільне окреме значення в таблиці або клітинці. Потрібно завжди працювати з таблицями та стовпцями.
Контекст рядка автоматично враховує зв'язки між таблицями, щоб визначити, які рядки в пов'язаних таблицях пов'язано з поточним.
Наприклад, у наведеній нижче формулі функція RELATED використовується для отримання значення податку з пов'язаної таблиці на основі регіону, у який було доставлено замовлення. Податкова вартість визначається за значенням для регіону в поточній таблиці, пошуком регіону в пов'язаній таблиці й отриманням податкової ставки для цього регіону з пов'язаної таблиці.
= [Вартість доставки] + RELATED('Регіон'[Податкова ставка])
Ця формула просто отримує податкову ставку для поточного регіону з таблиці "Регіон". Вам не потрібно знати або вказувати клавішу, яка з'єднує таблиці.
Контекст кількох рядків
Крім того, DAX містить функції, які виконують ітераційне обчислення в таблиці. Ці функції можуть мати кілька поточних рядків і контекстів поточних рядків. Говорячи мовою програмування, ви можете створювати формули, які повторюються через внутрішній і зовнішній цикли.
Припустімо, що ваша книга містить таблиці "Products " і "Sales" ( Продажі ). Можна переглянути всю таблицю збуту, яка містить багато транзакцій із кількома продуктами, і знайти найбільшу кількість замовлених товарів для кожного товару в одній транзакції.
В Excel це обчислення вимагає наявності ряду проміжних підсумків, які довелося б побудувати заново в разі змінення даних. Досвідчені користувачі Excel можуть складати формули масивів, які найкраще впораються з цим завданням. Крім того, у реляційній базі даних можна записати вкладені вибірки.
Проте за допомогою формул DAX можна побудувати єдину формулу, яка повертає правильне значення, і результати автоматично оновлюються щоразу, коли ви додаєте дані до таблиць.
=MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])
Докладний огляд цієї формули див. у статті про функцію EARLIER.
Коротше кажучи, функція EARLIER зберігає контекст рядка операції, яка передувала поточній операції. У всі часи функція постійно зберігає в пам'яті два набори контексту: один набір контексту представляє поточний рядок для внутрішнього циклу формули, а інший набір контексту представляє поточний рядок для зовнішнього циклу формули. DAX автоматично подає значення між двома циклами, щоб можна було створювати складні агрегати.
Контекст запиту
Контекст запиту посилається на підмножину даних, які неявно отримуються для формули. Під час вставлення міри або іншого поля значення в клітинку зведеної таблиці ядро Power Pivot перевіряє заголовки рядків і стовпців, роздільники та фільтри звітів, щоб визначити контекст. Потім надбудова Power Pivot виконує необхідні обчислення, щоб заповнити кожну клітинку зведеної таблиці. Набір даних, які отримуються, – це контекст запиту для кожної клітинки.
Оскільки контекст може змінюватися залежно від місця розташування формули, результати формули також змінюються залежно від того, чи вона використовується: у зведеній таблиці з багатьма групуваннями та фільтрами чи в обчислюваному стовпці без фільтрів і з мінімальним контекстом.
Наприклад, ви створили таку просту формулу, яка підсумовує значення в стовпці "Прибуток " таблиці "Продажі ":
=SUM('Продажі'[Прибуток])
Якщо ця формула використовується в обчислюваному стовпці таблиці "Збут", результати для неї будуть однаковими для всієї таблиці, оскільки контекст запиту для формули – це завжди весь набір даних таблиці "Продаж". Результати міститимуть прибуток для всіх регіонів, усіх продуктів, усіх років тощо.
Однак, як правило, ви хочете бачити не один і той же результат сотні разів, а замість цього ви хочете отримати прибуток за певний рік, певну країну або регіон, конкретний продукт або якусь їх комбінацію, а потім отримати загальний підсумок.
У зведеній таблиці можна легко змінити контекст, додаючи або видаляючи заголовки стовпців і рядків, а також додаючи або видаляючи роздільники. Ви можете створити формулу, подібну до наведеної вище, у мірі, а потім вставити її у зведену таблицю. Додаючи заголовки стовпців або рядків до зведеної таблиці, ви змінюєте контекст запиту, у якому обчислюється міра. Операції зрізу й фільтрування також впливають на контекст. Таким чином, одна й та сама формула, яка використовується у зведеній таблиці, обчислюється в різному контексті запиту для кожної клітинки.
Контекст фільтра
Контекст фільтра додається, коли за допомогою аргументів у формулі вказується обмеження фільтра для набору значень, дозволених у стовпці або таблиці. Контекст фільтра застосовується поверх інших контекстів, наприклад контексту рядка або контексту запиту.
Наприклад, зведена таблиця обчислює значення для кожної клітинки на основі заголовків рядків і стовпців, як описано в попередньому розділі про контекст запиту. Однак у мірах або обчислюваних стовпцях, які додаються до зведеної таблиці, можна вказати вирази фільтра, щоб керувати значеннями, що використовуються формулою. Також можна вибірково очистити фільтри в окремих стовпцях.
Докладні відомості про створення фільтрів у формулах див. у статті "Функції фільтра".
Приклад очищення фільтрів для створення загальних підсумків наведено у статті про функцію ALL.
Приклад вибіркового очищення та застосування фільтрів у формулах наведено в статті про функцію ALLEXCEPT
Тому необхідно переглянути визначення мір або формул, які використовуються у зведеній таблиці, щоб враховувати контекст фільтра під час інтерпретації результатів формул.
Визначення контексту у формулах
Коли ви створюєте формулу, надбудова Power Pivot для Excel спочатку перевіряє загальний синтаксис, а потім порівнює назви вказаних стовпців і таблиць із можливими стовпцями й таблицями в поточному контексті. Якщо надбудові Power Pivot не вдасться знайти стовпці й таблиці, визначені формулою, відобразиться повідомлення про помилку.
Контекст визначається, як описано в попередніх розділах, на основі доступних таблиць у книзі, зв'язків між таблицями та застосованих фільтрів.
Наприклад, якщо ви щойно імпортували дані до нової таблиці та не застосували фільтри, увесь набір стовпців у таблиці входить до поточного контексту. Якщо у вас кілька пов'язаних між собою таблиць і ви працюєте зі зведеною таблицею, відфільтрованою за допомогою заголовків стовпців і роздільників, контекст включатиме пов'язані таблиці та фільтри за цими даними.
Контекст – це потужне поняття, завдяки якому може також бути складно виправляти неполадки у формулах. Радимо почати з простих формул і зв'язків, щоб зрозуміти, як працює контекст, а потім експериментувати з простими формулами у зведених таблицях. У наступному розділі також наведено кілька прикладів того, як формули використовують різні типи контексту, щоб динамічно повертати результати.
Приклади контексту у формулах
- Функція RELATED розширює контекст поточного рядка, включаючи значення в пов'язаному стовпці. Це дає змогу виконувати підстановки. У наведеному в цій статті прикладі показано взаємодію фільтрування та контексту рядка.
- Функція FILTER дає змогу вказати рядки, які потрібно включити в поточний контекст. На прикладах у цій статті також показано, як вбудовувати фільтри в інші функції, які виконують агрегати.
- Функція ALL задає контекст у формулі. За її допомогою можна змінити фільтри, які застосовуються в результаті контексту запиту.
- Функція ALLEXCEPT дає змогу видалити всі фільтри, крім однієї, яку ви вказали. Обидві теми містять приклади, які дають змогу створювати формули та розуміти складний контекст.
- Функції EARLIER і EARLIEST дають змогу циклічно переходити між таблицями, виконуючи обчислення, посилаючись при цьому на значення з внутрішнього циклу. Якщо ви знайомі з концепцією рекурсії, а також з внутрішніми і зовнішніми циклами, ви оціните потужність, яку надають функції EARLIER і EARLIST. Якщо ви не знайомі з цими поняттями, вам слід уважно виконати кроки, описані в прикладі, щоб побачити, як внутрішній і зовнішній контексти використовуються в обчисленнях.
Цілісність зв'язків
У цьому розділі розглядаються деякі додаткові поняття, пов'язані з відсутністю значень у таблицях Power Pivot, пов'язаних між собою зв'язками. Цей розділ може бути корисним для тих, хто має книги з кількома таблицями та складними формулами та вам потрібна допомога, щоб зрозуміти результати.
Якщо ви не знайомі з поняттями реляційних даних, рекомендуємо спочатку прочитати вступну статтю " Огляд зв'язків".
Цілісність посилань і зв'язки Power Pivot
У надбудові Power Pivot не потрібно застосовувати цілісність даних між двома таблицями, щоб визначити припустимий зв'язок. Натомість на кінці зв'язку "один-до-багатьох" створюється пустий рядок, який використовується для обробки всіх рядків, що не збігаються, зі зв'язаної таблиці. Він ефективно працює як зовнішнє об'єднання SQL.
Якщо у зведених таблицях групувати дані за ознакою "один", усі незв'язані дані на боці "багато" групуються разом і включаються в підсумки із пустим заголовком рядка. Пустий заголовок приблизно еквівалентний заголовку "невідомий член".
Understanding the Unknown Member
Поняття невідомого члена, напевно, знайоме вам, якщо ви працювали з багатовимірними системами баз даних, такими як SQL Server Analysis Services Analysis Services (Служби аналізу SQL Server Analysis ServicesСлужби аналізу SQL Server Analysis Services). Якщо термін для вас новий, у наведеному нижче прикладі пояснюється, що таке невідомий елемент і як він впливає на обчислення.
Припустімо, потрібно створити обчислення, яке підсумовуватиме місячні обсяги збуту для кожного магазину, але в стовпці таблиці " Продаж" бракує значення назви магазину. Враховуючи, що таблиці " Магазин " і "Продаж" зв'язані за допомогою назви магазину, які зміни має відобразитися у формулі? Як має групувати зведену таблицю або відображати показники продажів, не пов'язані з наявним магазином?
Ця проблема часто зустрічається у сховищах даних, де великі таблиці фактичних даних мають бути логічно пов'язані з таблицями вимірів, які містять відомості про магазини, регіони та інші атрибути, які використовуються для категоризації та обчислення фактів. Щоб вирішити цю проблему, будь-які нові факти, не пов'язані з наявною сутністю, тимчасово призначаються невідомому учаснику. Саме тому непов'язані факти відображатимуться згрупованими у зведеній таблиці під пустим заголовком.
Порівняння пустих значень із пустим рядком
Пусті значення відрізняються від пустих рядків, які додаються, щоб вмістити невідомий член. Пусте значення – це спеціальне значення, яке використовується для позначення Null-значень, пустих рядків та інших відсутніх значень. Докладні відомості про пусте значення та інші типи даних DAX див. в статті "Типи даних у моделях даних".