У цьому розділі наведено посилання на приклади, які демонструють використання формул DAX у наведених нижче сценаріях.
-
Виконання складних обчислень
-
Робота з текстом і датами
-
Умовні значення та перевірка на наявність помилок
-
Використання часового аналізу
-
Ранжирування та порівняння значень
У цій статті
Початок роботи
Відвідайте вікі-сайт Центру ресурсів DAX , де можна знайти всі відомості про DAX, включно з блоґами, зразками, білимипаперами та відео, які надають провідні професіонали галузі та корпорація Майкрософт.
Сценарії: виконання складних обчислень
Формули DAX можуть виконувати складні обчислення, які передбачають настроювані агрегації, фільтрування та використання умовних значень. У цьому розділі наведено приклади початку роботи з настроюваними обчисленнями.
Створення настроюваних обчислень для зведеної таблиці
CALCULATE і CALCULATETABLE – це потужні гнучкі функції, які використовуються для визначення обчислюваних полів. Ці функції дають змогу змінити контекст, у якому виконуватиметься обчислення. Крім того, можна настроїти тип агрегації або математичну операцію для виконання. Перегляньте наведені нижче статті, наприклад.
Застосування фільтра до формули
У більшості місць, де функція DAX приймає таблицю як аргумент, зазвичай замість неї можна переходити до відфільтрованої таблиці або за допомогою функції FILTER замість імені таблиці, або за допомогою виразу фільтра як одного з аргументів функції. У наведених нижче статтях наведено приклади того, як створювати фільтри та як фільтри впливають на результати формул. Докладні відомості див. в статті Фільтрування даних у формулах DAX.
Функція FILTER дає змогу вказати умови фільтра за допомогою виразу, а інші – спеціально для фільтрування пустих значень.
Вибіркове видалення фільтрів для створення динамічних пропорцій
Створюючи динамічні фільтри у формулах, ви можете легко відповісти на такі запитання:
-
Який внесок у продаж поточного продукту та загальний обсяг продажів за рік?
-
Наскільки цей підрозділ сприяв загальному прибутку за всі операційні роки, порівняно з іншими підрозділами?
На формули, які використовуються у зведеній таблиці, може впливати контекст зведеної таблиці, але можна вибірково змінити контекст, додавши або видаливши фільтри. У прикладі в розділі ALL показано, як це зробити. Щоб знайти співвідношення продажів певного торговельного партнера за продажами для всіх торговельних партнерів, створіть міру, яка обчислює значення поточного контексту, розділеного на значення контексту ALL.
У статті ALLEXCEPT наведено приклад того, як вибірково очистити фільтри за формулою. В обох прикладах описано, як змінюються результати залежно від структури зведеної таблиці.
Інші приклади обчислення пропорцій і відсотків див. в таких статтях:
Використання значення із зовнішнього циклу
Окрім використання значень із поточного контексту в обчисленнях, DAX може використовувати значення з попереднього циклу під час створення набору пов'язаних обчислень. У цій статті описано, як створити формулу, яка посилається на значення із зовнішнього циклу. Функція EARLIER підтримує до двох рівнів вкладених циклів.
Докладні відомості про контекст рядків і пов'язані таблиці та способи використання цього поняття у формулах див. в статті Контекст у формулах DAX.
Сценарії: робота з текстом і датами
У цьому розділі наведено посилання на довідкові статті DAX, які містять приклади типових сценаріїв, пов'язаних із роботою з текстом, видобуванням і складанням значень дати й часу або створенням значень на основі умови.
Створення ключового стовпця за допомогою об'єднання
Power Pivot не дозволяє складені клавіші; тому, якщо у джерелі даних є складені ключі, їх може знадобитися об'єднати в один ключовий стовпець. У цій статті наведено один із прикладів створення обчислюваного стовпця на основі складеного ключа.
Створення дати на основі дати, видобутої з текстової дати
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, можна визначити спеціальний формат, щоб забезпечити правильну обробку значень. Ці формати використовуються під час перетворення значень на рядки або рядки. У наведених нижче статтях також наведено докладний список попередньо визначених форматів, доступних для роботи з датами й числами.
Змінення типів даних за допомогою формули
У Power Pivot тип даних виводу визначається вихідними стовпцями, і ви не можете явно вказати тип даних результату, оскільки оптимальний тип даних визначається Power Pivot. Однак ви можете використовувати неявні перетворення типів даних, виконані Power Pivot, щоб керувати типом вихідних даних.
-
Щоб перетворити дату або числовий рядок на число, помножте його на 1,0. Наприклад, наведена нижче формула обчислює поточну дату мінус 3 дні, а потім виводить відповідне ціле значення.
=(TODAY()-3)*1,0
-
Щоб перетворити значення дати, числа або грошової одиниці на рядок, об'єднайте значення пустим рядком. Наприклад, наведена нижче формула повертає сьогоднішню дату як рядок.
=""& TODAY()
Щоб забезпечити повернення певного типу даних, можна також скористатися наведеними нижче функціями.
Перетворення дійсних чисел на цілі числа
-
Перетворення дійсних чисел, цілих чисел або дат на рядки
-
Перетворення рядків на дійсні числа або дати
Сценарій: умовні значення та перевірка на наявність помилок
Як і в Excel, DAX має функції, які дають змогу перевіряти значення в даних і повертати інше значення на основі умови. Наприклад, можна створити обчислюваний стовпець, який позначає торговельних партнерів як Основний або Значення залежно від обсягу збуту за рік. Функції, які перевіряють значення, також корисні для перевірки діапазону або типу значень, щоб запобігти неочікуваним помилкам даних через порушення обчислень.
Створення значення на основі умови
Вкладені умови IF можна використовувати для перевірки значень і умовного створення нових значень. У наведених нижче статтях наведено кілька простих прикладів умовної обробки та умовних значень.
Перевірка на наявність помилок у формулі
На відміну від Excel, в одному рядку обчислюваного стовпця не можна мати припустимі значення та неприпустимі значення в іншому рядку. Тобто, якщо в будь-якій частині стовпця Power Pivot є помилка, увесь стовпець позначається помилкою, тому потрібно завжди виправляти помилки формул, які призводять до неприпустимих значень.
Наприклад, якщо створити формулу, яка розділяється на нуль, ви можете отримати результат нескінченності або помилку. Деякі формули також завершаться помилкою, якщо функція виявляє пусте значення, якщо очікує числове значення. Під час розробки моделі даних краще дозволити відображення помилок, щоб можна було клацнути повідомлення та виправити неполадку. Однак під час публікації книг слід включити обробку помилок, щоб запобігти неочікуваним значенням, які спричиняють помилки обчислень.
Щоб уникнути помилок в обчислюваному стовпці, скористайтеся комбінацією логічних та інформаційних функцій, щоб перевірити наявність помилок і завжди повертати припустимі значення. У наведених нижче статтях наведено кілька простих прикладів того, як це зробити в DAX.
Сценарії: використання часового аналізу
Функції часового аналізу DAX включають функції, які допомагають отримати дати або діапазони дат із даних. Потім за допомогою цих дат або діапазонів дат можна обчислити значення в подібних періодах. Функції часового аналізу також включають функції, які працюють зі стандартними інтервалами дат, щоб дати змогу порівнювати значення в місяцях, роках або кварталах. Також можна створити формулу, яка порівнює значення для першої та останньої дати вказаного періоду.
Список усіх функцій часового аналізу див. в статті Функції часового аналізу (DAX). Поради з ефективного використання дат і часу в аналізі Power Pivot див. в статті Дати в надбудові Power Pivot.
Обчислити сукупний обсяг продажів
У наведених нижче статтях наведено приклади того, як обчислити закриття та відкриття залишків. У прикладах можна створити залишки на виконання через різні проміжки часу, наприклад дні, місяці, квартали або роки.
Порівняння значень із часом
У наведених нижче статтях наведено приклади порівняння сум за різні періоди часу. Стандартні періоди часу, які підтримує DAX, – це місяці, квартали та роки.
Обчислення значення за настроюваний проміжок часу
Перегляньте наведені нижче статті, щоб дізнатися, як отримати настроювані діапазони дат, наприклад перші 15 днів після початку акції.
Якщо ви використовуєте функції часового аналізу для отримання настроюваного набору дат, ви можете використовувати цей набір дат як вхідний для функції, яка обчислюється, щоб створювати настроювані агрегати в періоди часу. Перегляньте наведений нижче розділ, щоб дізнатися, як це зробити:
-
Примітка.: Якщо не потрібно вказувати настроюваний проміжок часу, але ви працюєте зі стандартними одиницями бухгалтерського обліку, наприклад місяцями, кварталами або роками, радимо виконувати обчислення за допомогою функцій часового аналізу, призначених для цієї мети, наприклад TOTALQTD, TOTALMTD, TOTALQTD тощо.
Сценарії: ранжирування та порівняння значень
Щоб відобразити лише n-велику кількість елементів у стовпці або зведеній таблиці, є кілька варіантів:
-
За допомогою функцій у програмі Excel можна створити фільтр Top. У зведеній таблиці також можна вибрати кількість перших або останніх значень. У першій частині цього розділу описано, як фільтрувати 10 перших елементів зведеної таблиці. Докладні відомості див. в документації Excel.
-
Ви можете створити формулу, яка динамічно рангує значення, а потім відфільтрувати за значеннями ранжирування або використовувати значення ранжирування як роздільник. У другій частині цього розділу описано, як створити формулу, а потім використовувати її в роздільнику.
Є переваги та недоліки для кожного методу.
-
Фільтр Excel Top простий у використанні, але фільтр призначений виключно для відображення. Якщо дані, що лежать в основі зведеної таблиці, змінюються, потрібно вручну оновити зведену таблицю, щоб переглянути зміни. Якщо вам потрібно динамічно працювати з рейтингами, можна скористатися daX, щоб створити формулу, яка порівнює значення з іншими значеннями в стовпці.
-
Формула DAX є потужнішою; Крім того, додавши значення ранжирування до роздільника, можна просто клацнути роздільник, щоб змінити кількість відображуваних найвищих значень. Проте обчислення обчислюються дорого, і цей метод може не підходити до таблиць із багатьма рядками.
Відображення лише десяти перших елементів у зведеній таблиці
|
Відображення перших або останніх значень у зведеній таблиці
|
Динамічне замовлення елементів за допомогою формули
У цій статті наведено приклад використання DAX для створення ранжирування, яке зберігається в обчислюваному стовпці. Оскільки формули DAX обчислюються динамічно, ви завжди можете бути впевнені, що ранжирування правильне, навіть якщо базові дані змінилися. Крім того, оскільки формула використовується в обчислюваному стовпці, можна використовувати ранжирування в роздільнику, а потім вибрати перші 5, перші 10 або навіть перші 100 значень.
