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