Вирази аналізу даних (DAX) у надбудові Power Pivot

Вирази аналізу даних (DAX) спочатку трохи залякують, але не дозволяйте імені обдурити вас. Основи DAX дійсно досить легко зрозуміти. Перші речі по-перше - DAX не є мовою програмування. DAX – це мова формул. За допомогою daX можна визначити настроювані обчислення для обчислюваних стовпців і мір (також відомих як обчислювані поля). DAX містить деякі функції, які використовуються у формулах Excel, а також додаткові функції, призначені для роботи з реляційними даними та динамічного об'єднання.

Докладні відомості про формули DAX

Формули DAX дуже схожі на формули Excel. Щоб створити знак рівності, а потім – ім'я або вираз функції, а також обов'язкові значення або аргументи. Як і Excel, DAX надає різноманітні функції, які можна використовувати для роботи з рядками, виконання обчислень із використанням дат і часу або створення умовних значень.

Проте формули DAX відрізняються такими важливими способами:

Якщо потрібно настроїти обчислення по рядках, DAX включає функції, які дають змогу використовувати поточне значення рядка або пов'язане значення для обчислень, які залежать від контексту.
Функція DAX містить тип функції, яка повертає як результат таблицю, а не одне значення. Ці функції можна використовувати, щоб вводити дані в інші функції.
Функції часового аналізуу DAX дозволяють обчислення з використанням діапазонів дат і порівнюють результати в паралельні періоди.

Де використовувати формули DAX

Формули можна створювати в Power Pivot в обчислюваних колах або в обчислюванихf-ields.

Обчислювані стовпці

Обчислюваний стовпець – це стовпець, який додається до наявної Power Pivot таблиці. Замість того, щоб вставляєте або імпортує значення в стовпець, створіть формулу DAX, яка визначає значення стовпців. Якщо додати Power Pivot таблицю до зведеної таблиці (або зведеної діаграми), обчислюваний стовпець можна використовувати так само, як і будь-який інший стовпець даних.

Формули в обчислюваних стовпцях дуже схожі на формули, створені у програмі Excel. На відміну від Excel, ви не можете створити іншу формулу для різних рядків у таблиці; Натомість формула DAX автоматично застосовується до всього стовпця.

Якщо стовпець містить формулу, значення обчислюється для кожного рядка. Результати обчислюються для стовпця, щойно ви створите формулу. Значення стовпців переобчислюються, лише якщо оновлюються базові дані або використовуються переобчислення вручну.

Обчислювані стовпці можна створювати на основі мір та інших обчислюваних стовпців. Однак не використовуйте однакове ім'я для обчислюваного стовпця та міри, оскільки це може призвести до плутанини з результатами. Під час посилання на стовпець краще використовувати повне посилання на стовпець, щоб уникнути випадкового виклику міри.

Докладні відомості див. в статті Обчислювані стовпці в надбудові Power Pivot.

Заходи

Міра – це формула, створена спеціально для використання у зведеній таблиці (або зведеній діаграмі), яка використовує дані Power Pivot. Міри можуть базуватися на стандартних функціях агрегації, таких як COUNT або SUM, або ви можете визначити власну формулу за допомогою DAX. Міра використовується в області значень зведеної таблиці. Якщо потрібно розташувати обчислювані результати в іншій області зведеної таблиці, використовуйте натомість обчислюваний стовпець.

Коли ви визначаєте формулу для явної міри, нічого не відбувається, доки ви не додасте міру до зведеної таблиці. Коли ви додаєте міру, формула обчислюється для кожної клітинки в області Значення зведеної таблиці. Оскільки для кожної комбінації заголовків рядків і стовпців створюється результат, результат для міри може відрізнятися в кожній клітинці.

Визначення міри, яку ви створюєте, зберігається разом із вихідною таблицею даних. Він відображається в списку Полів зведеної таблиці та доступний для всіх користувачів книги.

Докладні відомості див. в статті Міри в надбудові Power Pivot.

Створення формул за допомогою рядка формул

Power Pivot, наприклад Excel, містить рядок формул, який спрощує створення та редагування формул, а також функції автозаповнення для згортання помилок вводу та синтаксичних помилок.

Введення імені таблиці Почніть вводити ім'я таблиці. Функція автозаповнення формул надає розкривний список із припустимими іменами, які починаються з цих букв.

Введення імені стовпця Введіть квадратну дужку, а потім виберіть стовпець зі списку стовпців у поточній таблиці. Для стовпця з іншої таблиці почніть вводити перші букви імені таблиці, а потім виберіть стовпець із розкривного списку Автозаповнення.

Докладні відомості та покрокові вказівки зі створення формул див. в статті Створення формул для обчислень у надбудові Power Pivot.

Поради з використання автозаповнення

Автозаповнення формул можна використовувати посередині наявної формули з вкладеними функціями. Текст безпосередньо перед точкою вставлення використовується для відображення значень у розкривному списку, а весь текст після місця вставлення залишається незмінним.

Визначені імена, створені для констант, не відображаються в розкривному списку автозаповнення, але їх можна вводити.

Power Pivot не додається закриваюча дужка функцій і не збігається з дужками. Переконайтеся, що кожна функція є синтаксично правильною, або ви не можете зберегти або використати формулу.

Використання кількох функцій у формулі

Ви можете вкладати функції, тобто використовувати результати однієї функції як аргумент іншої функції. В обчислювані стовпці можна вкласти до 64 рівнів функцій. Однак вкладення може ускладнити створення формул або виправлення неполадок.

Багато функцій DAX призначено для використання виключно як вкладені функції. Ці функції повертають таблицю, яку не можна зберегти безпосередньо в результаті; його слід вводити як ввід у функцію таблиці. Наприклад, функції SUMX, AVERAGEX і MINX вимагають створення таблиці як першого аргументу.

: Деякі обмеження на вкладення функцій існують у межах мір, щоб переконатися, що на продуктивність не впливає багато обчислень, необхідних для залежностей між стовпцями.

Порівняння функцій DAX і функцій Excel

Бібліотека функцій DAX базується на бібліотеці функцій Excel, але бібліотеки мають багато відмінностей. У цьому розділі підсумовано відмінності та подібності між функціями Excel і DAX.

Багато функцій DAX мають однакове ім'я та загальну поведінку, що й функції Excel, але їх було змінено для введення різних типів, а в деяких випадках може повертати інший тип даних. Зазвичай у формулі Excel не можна використовувати функції DAX або формули Excel у Power Pivot без внесення змін.
Функції DAX ніколи не приймають посилання на клітинку або діапазон як посилання, але замість цього функції DAX приймають стовпець або таблицю як посилання.
Функції дати й часу DAX повертають тип даних дати й часу. На відміну від цього, функції дати й часу в Excel повертають ціле число, яке представляє дату як числове значення.
Багато нових функцій DAX повертають таблицю значень або роблять обчислення на основі таблиці значень як вхідні. Натомість у програмі Excel немає функцій, які повертають таблицю, але деякі функції можуть працювати з масивами. Можливість легко посилатися на повні таблиці та стовпці є новою функцією в Power Pivot.
DaX надає нові функції підстановки, схожі на масив і векторні функції підстановки в Excel. Проте для функцій DAX потрібно встановити зв'язок між таблицями.
Очікується, що дані в стовпці завжди мають однаковий тип даних. Якщо дані мають не однаковий тип, DAX змінює весь стовпець на тип даних, який найкраще відповідає всім значенням.

Типи даних DAX

Дані можна імпортувати до моделі даних Power Pivot з багатьох різних джерел, які можуть підтримувати різні типи даних. Коли ви імпортуєте або завантажуєте дані, а потім використовуєте дані в обчисленнях або зведених таблицях, дані перетворюються на один із Power Pivot типів даних. Список типів даних див. в статті Типи даних у моделях даних.

Тип даних таблиці – це новий тип даних у DAX, який використовується як вхідний або вихідний у багатьох нових функціях. Наприклад, функція FILTER приймає таблицю як ввід і виводить іншу таблицю, яка містить лише рядки, які відповідають умовам фільтра. Поєднуючи функції таблиці з функціями агрегації, можна виконувати складні обчислення над динамічно визначеними наборами даних. Докладні відомості див. в статті Агрегації в надбудові Power Pivot.

Формули та реляційна модель

Вікно Power Pivot – це область, де можна працювати з кількома таблицями даних і з'єднати таблиці в реляційній моделі. У цій моделі даних таблиці з'єднуються між собою за зв'язками, які дають змогу створювати взаємозв'язки зі стовпцями в інших таблицях і створювати цікавіші обчислення. Наприклад, можна створити формули, які підсумовують значення для пов'язаної таблиці, а потім зберегти це значення в одній клітинці. Або, щоб керувати рядками з пов'язаної таблиці, можна застосувати фільтри до таблиць і стовпців. Докладні відомості див. в статті Зв'язки між таблицями в моделі даних.

Оскільки можна зв'язувати таблиці за допомогою зв'язків, зведені таблиці також можуть містити дані з кількох стовпців із різних таблиць.

Однак, оскільки формули можуть працювати з цілими таблицями та стовпцями, обчислюється не так, як у програмі Excel.

Загалом формула DAX у стовпці завжди застосовується до всього набору значень у стовпці (ніколи не лише до кількох рядків або клітинок).
Таблиці в Power Pivot мають завжди мати однакову кількість стовпців у кожному рядку, а всі рядки в стовпці мають містити однаковий тип даних.
Коли таблиці з'єднані зв'язком, очікується, що два стовпці, які використовуються як ключі, мають значення, які найбільше збігаються. Оскільки Power Pivot не забезпечує цілісність даних, у ключовому стовпці можна мати незв'язані значення та створити зв'язок. Однак наявність пустих або невідповідних значень може вплинути на результати формул і вигляд зведених таблиць. Докладні відомості див. в статті Підстановки у формулах Power Pivot.
Під час зв'язування таблиць за допомогою зв'язків збільшується область або текст c, у якому обчислюються формули. Наприклад, на формули у зведеній таблиці можуть впливати будь-які фільтри або заголовки стовпців і рядків у зведеній таблиці. Ви можете створювати формули, які зумовлюють контекст, але контекст також може призвести до змінення результатів у спосіб, який ви можете не передбачити. Докладні відомості див. в статті Контекст у формулах DAX.

Оновлення результатів формул

Дані r efresh і переобчислення – це дві окремі, але пов'язані операції, які слід розуміти під час розробки моделі даних, яка містить складні формули, великі обсяги даних або дані, отримані із зовнішніх джерел даних.

Оновлення даних – це процес оновлення даних у книзі новими даними із зовнішнього джерела даних. Дані можна оновлювати вручну через указані проміжки часу. Якщо книгу опубліковано на сайті SharePoint, можна запланувати автоматичне оновлення із зовнішніх джерел.

Переобчислення – це процес оновлення результатів формул для відображення будь-яких змін у формулах і відображення цих змін у базових даних. Переобчислення може вплинути на продуктивність такими способами:

Для обчислюваного стовпця результат формули завжди має переобчислюватися для всього стовпця під час змінення формули.
Для міри результати формули не обчислюються, доки міра не опиниться в контексті зведеної таблиці або зведеної діаграми. Формула також переобчислюватиметься, якщо змінити будь-який заголовок рядка або стовпця, який впливає на фільтри даних, або якщо оновити зведену таблицю вручну.

Виправлення неполадок із формулами

Помилки під час написання формул

Якщо під час визначення формули стається помилка, формула може містити синтаксичну помилку, семантичну помилку або помилку обчислення.

Найпростіше виправити синтаксичні помилки. Зазвичай у них відсутня дужка або кома. Довідку з синтаксису окремих функцій див. в статті Довідник із функцій DAX.

Інший тип помилки виникає, коли синтаксис правильний, але значення або стовпець, на які посилається формула, не має сенсу в контексті формули. Такі семантичні помилки та помилки обчислення можуть виникати через такі проблеми:

Формула посилається на ненаявний стовпець, таблицю або функцію.
Формула, як видається, правильна, але коли обробник даних отримує дані, вона знаходить невідповідність типів і викликає помилку.
Формула передає функції неправильне число або тип параметрів.
Формула посилається на інший стовпець із помилкою, тому його значення неприпустимі.
Формула посилається на стовпець, який не було оброблено, тобто він має метадані, але фактичні дані не використовуються для обчислень.

У перших чотирьох випадках DAX позначає весь стовпець, який містить неприпустиму формулу. В останньому випадку DAX затіяє стовпець, щоб позначити, що стовпець перебуває в необробленому стані.

Неправильні або незвичні результати під час ранжирування або впорядкування значень стовпців

Під час ранжирування або впорядкування стовпця, який містить значення NaN (не число), ви можете отримати неправильні або неочікувані результати. Наприклад, коли обчислення ділить 0 на 0, повертається результат NaN.

Це відбувається тому, що обробник формул виконує порядок і ранжирування, порівнюючи числові значення; однак не можна порівняти NaN з іншими числами в стовпці.

Щоб забезпечити правильні результати, можна використовувати умовні інструкції за допомогою функції IF, щоб перевірити значення NaN і повернути числове значення 0.

Сумісність із табличними моделями служб аналізу Analysis Services і режимом directQuery

Загалом формули DAX, які ви створюєте в Power Pivot, повністю сумісні з табличними моделями служб аналізу Analysis Services. Однак, якщо перенести модель Power Pivot до екземпляра служб аналізу Analysis Services, а потім розгорнути модель у режимі DirectQuery, існують деякі обмеження.

У разі розгортання моделі в режимі DirectQuery деякі формули DAX можуть повертати інші результати.
Деякі формули можуть спричиняти помилки перевірки під час розгортання моделі в режимі DirectQuery, оскільки формула містить функцію DAX, яка не підтримується в реляційному джерелі даних.

Докладні відомості див. в статті Табличне моделювання служб аналізу Analysis Services у SQL Server 2012 BooksOnline.