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

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

Загальні відомості про формули DAX

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

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

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

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

У надбудові Power Pivot можна створювати формули як в обчислюваних стовпцях, так і в обчислюваних полях.

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

Обчислюваний стовпець – це стовпець, який додається до наявної таблиці 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, але їх змінено, щоб приймати інші типи вхідних даних, і в деяких випадках вони можуть повертати інший тип даних. Зазвичай функції DAX не можна використовувати у формулах Excel або використовувати формули 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".
• Якщо зв'язати таблиці за допомогою зв'язків, збільшується область або контекст, у якому обчислюються формули. Наприклад, на формули у зведеній таблиці можуть впливати будь-які фільтри або заголовки стовпців і рядків у зведеній таблиці. Ви можете створювати формули, які маніпулюють контекстом, але контекст також може спричинити змінення результатів у спосіб, який ви можете не передбачити. Докладні відомості див. в статті "Контекст у формулах DAX".

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

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

Оновлення даних – це процес оновлення даних у книзі новими даними із зовнішнього джерела. Ви можете оновлювати дані вручну через заданий інтервал. Якщо книгу опубліковано на сайті 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, існують деякі обмеження.

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

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