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

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

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

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

Докладніше про формули DAX

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

Але формули DAX мають такі суттєві відмінності:

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

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

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

Де використовуються формули DAX

Можна створювати формули в Power Pivot в обчислюваних columns або обчислюваних fields.

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

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

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

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

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

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

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

  • Функції DAX ніколи не використовують клітинку або діапазон клітинок як посилання, натомість, вони використовують стовпці чи таблиці.

  • Функції дати й часу DAX повертають тип даних datetime. На відміну від них, функції дати і часу 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.

  • Під час зв'язку з можливістю створювати зв'язки між таблицями, ви збільшити масштаб або context , в якому обчислюються формул. Наприклад, формули у зведеній таблиці можуть впливати будь-які фільтри або заголовки стовпців і рядків у зведеній таблиці. Можна записувати формули, які виконують операції з контексту, але контексті також може викликати результати таким чином, що може не потрібно змінити. Додаткові відомості про контекст у формулах DAX.

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

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

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

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

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

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

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

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

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

Синтаксичні помилки виправити найлегше. Вони зазвичай означають пропущену дужку або кому. Щоб отримати довідку із синтаксису окремих функцій, див. довідку з функцій DAX.

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

  • Формула посилається на стовпець, таблицю або функцію, які не існують.

  • Формула виглядає правильно, але коли обробник даних отримує дані, виявляється невідповідність типу та виникає помилка.

  • Формула містить неправильну кількість або тип параметрів функції.

  • Формула посилається на інший стовпець, який містить помилку, і тому її значення неприпустиме.

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

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

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

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

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

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

Сумісність між режимами таблиць служб Analysis Services та режимом DirectQuery

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

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

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

Щоб отримати додаткові відомості, див. матеріали щодо створення табличних моделей у службах Analysis Services в електронній документації для SQL Server 2012.

Примітка : Застереження про машинний переклад: Цю статтю перекладено комп’ютерною системою без втручання людини. Корпорація Майкрософт пропонує таку послугу, щоб іншомовні користувачі могли дізнаватися про продукти, служби й технології Microsoft. Оскільки статтю перекладено за допомогою служби машинного перекладу, вона може містити смислові, синтаксичні або граматичні помилки.

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×