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