У цьому розділі наведено посилання на приклади, які демонструють використання формул DAX у наведених нижче сценаріях.
- Виконання складних обчислень
- Робота з текстом і датами
- Умовні значення та перевірка на помилки
- Часовий аналіз
- Ранжування та порівняння значень
У цій статті
Початок роботи
Відвідайте вікі-сайт DAX Resource Center , де можна знайти інформацію про DAX, зокрема блоґи, зразки, технічні документи та відео, надані провідними професіоналами галузі та корпорацією Майкрософт.
Сценарії: виконання складних обчислень
За допомогою формул DAX можна виконувати складні обчислення, які передбачають настроювані агрегації, фільтрування та використання умовних значень. У цьому розділі наведено приклади початку роботи з настроюваними обчисленнями.
Створення спеціальних обчислень для зведеної таблиці
CALCULATE і CALCULATETABLE – це потужні та гнучкі функції, які корисні для визначення обчислюваних полів. Ці функції дають змогу змінити контекст, у якому виконуватиметься обчислення. Також можна настроїти тип агрегації або математичної операції. Див. наведені нижче статті.
Застосування фільтра до формули
У більшості розташувань, де функція DAX використовує таблицю як аргумент, замість неї зазвичай можна передати відфільтровану таблицю, використовуючи функцію FILTER замість імені таблиці або вказавши вираз фільтра як один з аргументів функції. У наведених нижче статтях розповідається про те, як створювати фільтри та як фільтри впливають на результати формул. Докладні відомості див. в статті "Фільтрування даних у формулах DAX".
Функція FILTER дає змогу визначити умови фільтра за допомогою виразу, тоді як інші функції створено спеціально для фільтрування пустих значень.
Вибіркове видалення фільтрів для створення динамічних пропорцій
За допомогою динамічних фільтрів у формулах можна легко знайти відповідь на наведені нижче запитання.
- Яким був внесок продажів поточного продукту в загальний обсяг продажів за рік?
- Який внесок цей підрозділ вніс у загальний прибуток за всі операційні роки в порівнянні з іншими підрозділами?
На формули, які використовуються у зведеній таблиці, може впливати контекст зведеної таблиці, але ви можете вибірково змінювати контекст, додаючи або видаляючи фільтри. У прикладі в розділі "УСІ" показано, як це зробити. Щоб знайти співвідношення обсягів продажу для певного торговельного партнера до рівня продажу для всіх торговельних партнерів, потрібно створити міру, яка обчислює значення для поточного контексту від ділення значення для контексту ALL.
У розділі "ALLEXCEPT – це приклад вибіркового очищення фільтрів у формулі". В обох прикладах показано, як результати змінюються залежно від макета зведеної таблиці.
Інші приклади обчислення коефіцієнтів і відсотків див. в таких статтях:
Використання значення із зовнішнього циклу
Окрім використання в обчисленнях значень із поточного контексту, формули DAX можуть використовувати значення з попереднього циклу для створення набору пов'язаних обчислень. У наступній статті наведено покрокові вказівки зі створення формули, що посилається на значення із зовнішнього циклу. Функція EARLIER підтримує до двох рівнів вкладених циклів.
Докладні відомості про контекст рядків і пов'язані таблиці, а також про використання цього поняття у формулах див. в статті "Контекст у формулах DAX".
Сценарії: робота з текстом і датами
У цьому розділі наведено посилання на довідкові статті DAX, які містять приклади типових сценаріїв, пов'язаних із роботою з текстом, видобуванням і складанням значень дати й часу або створенням значень на основі умови.
Створення ключового стовпця шляхом об'єднання
У надбудові Power Pivot не можна використовувати складні ключі. Таким чином, якщо в джерелі даних містяться складені ключі, може знадобитися об'єднати їх в один стовпець ключа. У цій статті наведено приклад того, як створити обчислюваний стовпець на основі складеного ключа.
ComposeCompose дата на основі частин дати, видобутих із текстової дати
У надбудові Power Pivot для роботи з датами використовується тип даних SQL Server дати й часу. Тому, якщо зовнішні дані містять дати, формат яких відрізняється, наприклад дати в регіональному форматі, який не розпізнається обробником даних Power Pivot, або якщо в даних використовуються сурогатні цілочисельні ключі, може знадобитись використати формулу DAX, щоб видобути частини дати, а потім скомпонувати ці частини в дійсну дату/ представлення часу.
Наприклад, якщо у вас є стовпець дат, які було представлено як цілі числа, а потім імпортовано як текстовий рядок, ви можете перетворити рядок на значення дати й часу за допомогою такої формули:
=DATE(RIGHT([Значення1];4);LEFT([Значення1];2);MID([Значення1];2))
| Значення1 | Результат |
|---|---|
| 01032009 | 1/3/2009 |
| 12132008 | 12/13/2008 |
| 06252007 | 6/25/2007 |
Ці статті містять докладніші відомості про функції, які використовуються, щоб видобувати та компонувати дати.
Визначення спеціального формату дати або числа
Якщо дані містять дати або числа, які не представлені в жодному зі стандартних текстових форматів Windows, ви можете визначити спеціальний формат, щоб забезпечити правильну обробку значень. Ці формати використовуються під час перетворення значень на рядки або з рядків. У наведених нижче статтях також надано докладний список попередньо визначених форматів, доступних для роботи з датами та числами.
- Попередньо визначені числові формати для функції FORMAT
- Користувацькі числові формати для функції FORMAT
- Попередньо визначені формати дати й часу для функції FORMAT
- Спеціальні формати дати й часу для функції FORMAT
Змінення типів даних за допомогою формули
У надбудові Power Pivot тип даних вихідних даних визначається вихідними стовпцями, і не можна вказати явно тип даних результату, оскільки оптимальний тип даних визначається в надбудові Power Pivot. Проте можна використовувати неявні перетворення типів даних, які виконує надбудова Power Pivot, щоб керувати типом вихідних даних.
- Щоб перетворити дату або рядок чисел на число, потрібно помножити його на 1,0. Наприклад, наведена нижче формула обчислює поточну дату мінус 3 дні, а потім виводить відповідне ціле значення.
=(TODAY()-3)*1.0 - Щоб перетворити значення дати, числа або грошової одиниці на рядок, об'єднайте значення з пустим рядком. Наприклад, наведена нижче формула повертає сьогоднішню дату як рядок.
=""& TODAY()
Щоб забезпечити повернення певного типу даних, можна використовувати наведені нижче функції.
Перетворення дійсних чисел на цілі
- Функція ROUND
- Функція CEILING
-
Функція FLOOR
Перетворення дійсних чисел, цілих чисел або дат на рядки - Функція FIXED
-
Функція FORMAT
Перетворення рядків на дійсні числа або дати - Функція VALUE
- Функція DATEVALUE
- Функція TIMEVALUE
Сценарій: умовні значення та перевірка на помилки
Як і Excel, формули DAX містять функції, які дають змогу перевіряти значення в даних і повертати інше значення на основі умови. Наприклад, можна створити обчислюваний стовпець, у якому торговельних партнерів буде позначено як "Пріоритет " або " Ціна" залежно від річного обсягу збуту. Функції, які перевіряють значення, також корисні для перевірки діапазону або типу значень, щоб запобігти неочікуваним помилкам даних, які можуть порушити обчислення.
Створення значення на основі умови
Вкладені умови IF можна використовувати для перевірки значень і створення нових умовних значень. Прості приклади умовної обробки та умовних значень наведено в таких статтях:
Перевірка формули на помилки
На відміну від програми Excel, в одному рядку обчислюваного стовпця не може бути припустимих значень, а в іншому – неприпустимих. Таким чином, якщо сталася помилка в будь-якій частині стовпця Power Pivot, помилка відображатиметься в увесь стовпець, тому потрібно завжди виправляти помилки формул, які призводять до неприпустимих значень.
Наприклад, якщо ви створюєте формулу, яка ділить на нуль, ви можете отримати результат нескінченності або помилку. Деякі формули також завершаться помилкою, якщо функція поверне пусте значення, яке очікується числове. Поки ви розробляєте модель даних, рекомендовано зачекати, як відображаються помилки, щоб можна було клацнути повідомлення та виправити неполадку. Проте під час публікації книг слід застосовувати обробку помилок, щоб запобігти невдалому обчисленню неочікуваних значень.
Щоб уникнути помилок в обчислюваному стовпці, слід використовувати поєднання логічних та інформаційних функцій для перевірки на помилки та завжди повертати правильні значення. Прості приклади виконання цих дій у DAX наведено в цих статтях:
Сценарії: використання часового аналізу
Функції часового аналізу DAX включають функції, які допомагають отримувати дати або діапазони дат із ваших даних. Потім ці дати або проміжки часу можна використовувати для обчислення значень у схожі періоди. Функції часового аналізу також включають функції, які працюють зі стандартними інтервалами дат і дають змогу порівнювати значення за місяці, роки або квартали. Ви також можете створити формулу, яка порівнює значення першої та останньої дати вказаного періоду.
Список усіх функцій часового аналізу див. в статті "Функції часового аналізу" (DAX). Поради з ефективного використання дат і часу в аналізі Power Pivot див. в статті "Дати в надбудові Power Pivot".
Обчислення сукупного обсягу продажів
У наведених нижче статтях наведено приклади обчислення залишків при закритті й відкритті. У прикладах можна створити поточні баланси для різних інтервалів, наприклад днів, місяців, кварталів або років.
- Функція CLOSINGBALANCEMONTH, функція CLOSINGBALANCEQUARTER, функція CLOSINGBALANCEYEAR
- Функція OPENINGBALANCEMONTH, функція OPENINGBALANCEQUARTER, функція OPENINGBALANCEYEAR
Порівняння значень із часом
У наведених нижче статтях наведено приклади порівняння сум у різні періоди часу. Стандартні періоди часу, що підтримуються DAX, – місяці, квартали та роки.
- Функція PREVIOUSMONTH, PREVIOUSQUARTER,PREVIOUSYEAR
- Функція TOTALMTD, функція TOTALQTD, функція TOTALYTD
- Функція PARALLELPERIOD
Обчислення значення в спеціальному діапазоні дат
У наведених нижче статтях розповідається про те, як отримати спеціальні проміжки часу, наприклад перші 15 днів після початку стимулювання збуту.
Якщо ви використовуєте функції часового аналізу, щоб отримати настроюваний набір дат, ви можете використовувати цей набір дат як вхідні дані для функції, яка виконує обчислення, щоб створити настроювані агрегати для різних періодів часу. Приклад того, як це зробити, наведено в цій статті:
-
Примітка.
Якщо вам не потрібно вказувати настроюваний діапазон дат, але ви працюєте зі стандартними розрахунковими одиницями, такими як місяці, квартали або роки, радимо виконувати обчислення за допомогою функцій часового аналізу, розроблених для цієї мети, як-от TOTALQTD, TOTALMTD, TOTALQTD тощо.
Сценарії: ранжирування й порівняння значень
Щоб у стовпці або зведеній таблиці відображалися лише перші n елементів списку, можна виконати такі дії:
- Ви можете використовувати функції в Excel, щоб створити фільтр Top. У зведеній таблиці також можна вибрати кілька найбільших або останніх значень. У першій частині цього розділу описано, як відфільтрувати перші 10 елементів зведеної таблиці. Докладні відомості див. в документації Excel.
- Ви можете створити формулу, яка динамічно ранжирує значення, а потім відфільтрувати їх за ними або використати значення ранжирування як роздільник. У другій частині цього розділу описано, як створити формулу та використовувати її в роздільнику.
У кожного методу є свої переваги і недоліки.
- Верхній фільтр Excel простий у використанні, але він призначений виключно для відображення. Якщо дані в основі зведеної таблиці змінилися, необхідно вручну оновити її, щоб ці зміни відбулися. Якщо потрібно динамічно працювати з рейтингами, можна скористатися DAX для створення формули, яка порівнює значення з іншими значеннями в стовпці.
- Формула DAX більш потужна; більше того, додавши значення ранжирування до роздільника, ви можете просто клацнути роздільник, щоб змінити кількість верхніх значень, які відображаються. Однак такі обчислення затратні з обчислювальної точки зору, тому цей метод може не підійти для таблиць із великою кількістю рядків.
Відображення лише перших десяти елементів у зведеній таблиці
Відображення найвищих або найнижчих значень у зведеній таблиці
|
|---|
Динамічне впорядкування елементів за допомогою формули
У цій статті наведено приклад використання формул DAX для створення рейтингу, який зберігається в обчислюваному стовпці. Оскільки формули DAX обчислюються динамічно, ви завжди можете бути впевнені в правильності ранжирування, навіть якщо базові дані змінилися. Також, оскільки формула використовується в обчислюваному стовпці, можна скористатися рейтингом у роздільнику, а потім вибрати перші 5, 10 або навіть 100 перших значень.