У цьому розділі наведено посилання на приклади, у яких показано використання формул DAX у наведених нижче сценаріях.
-
Виконання складних обчислень
-
Робота з текстом і датами
-
Умовні значення та перевірка на помилки
-
Використання часового інтелекту
-
Ранжування та порівняння значень
У цій статті
Початок роботи
Відвідайте вікі-сайт центру ресурсів DAX, де можна знайти багато різноманітної інформації про DAX, зокрема блоґи, зразки, білі пакунки та відео від провідних галузевих фахівців і корпорації Майкрософт.
Сценарії: виконання складних обчислень
Формули DAX можуть виконувати складні обчислення, пов'язані зі спеціальними агрегаціями, фільтруванням і використанням умовних значень. У цьому розділі наведено приклади початку роботи з настроюваними обчисленнями.
Створення настроюваних обчислень для зведеної таблиці
CALCULATE і CALCULATETABLE – це потужні гнучкі функції, які корисні для визначення обчислюваних полів. Ці функції дають змогу змінити контекст обчислення. Крім того, можна настроїти тип агрегації або математичної операції. Приклади наведено в наведених нижче розділах.
Застосування фільтра до формули
У більшості випадків, де функція DAX використовує таблицю як аргумент, зазвичай можна пройти в відфільтрованій таблиці, використовуючи функцію FILTER замість імені таблиці, або вказавте вираз фільтра як один з аргументів функції. У наведених нижче статтях наведено приклади створення фільтрів і вплив фільтрів на результати формул. Докладні відомості див. в ційи: Фільтрування даних у формулах DAX.
Функція FILTER дає змогу вказати умови фільтрування за допомогою виразу, а інші функції призначені спеціально для фільтрування пустих значень.
Вибіркове вилучення фільтрів для створення динамічних пропорцій
Створюючи динамічні фільтри у формулах, можна легко відповідати на такі запитання:
-
Який внесок поточного продажу продукту в загальний обсяг збуту за рік?
-
Який внесок цього підрозділу в загальний прибуток за всі операційні роки порівняно з іншими підрозділами?
На формули, які використовуються у зведеній таблиці, може впливати контекст зведеної таблиці, але ви можете вибірково змінити контекст, додавши або видаливши фільтри. У прикладі з теми ALL показано, як це зробити. Щоб знайти співвідношення продажів для певного торгового партнера над продажами для всіх реселерів, створіть міру, яка обчислює значення для поточного контексту, поділеного на значення для контексту ALL.
У розділі ALLEXCEPT наведено приклад вибіркового очищення фільтрів у формулі. В обох прикладах описано, як результати змінюються залежно від макета зведеної таблиці.
Інші приклади обчислення процентних чи відсоткових показників див. в таких статтях:
Using a value from an outer loop
Окрім використання значень із поточного контексту в обчисленнях, 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 містять функції, які допомагають отримувати дати або діапазони дат із даних. Потім ці дати або діапазони дат можна використовувати для обчислення значень за схожі періоди. Функції часового інтелекту також містять функції, які працюють із стандартними інтервалами дат, що дають змогу порівнювати значення за місяцями, роками або кварталами. Також можна створити формулу, яка порівнюватиме значення для першої та останньої дати вказаного періоду.
Список усіх функцій часового аналетування див. в цій програмі. Поради з ефективного використання дати й часу в аналізі Power Pivot див. в статті Дати в надбудові Power Pivot.
Обчислення сукупного обсягу збуту
У наведених нижче статтях наведено приклади того, як обчислити закриття та відкриття балансу. У прикладах можна створювати баланси з нагромадженням за різні інтервали, наприклад дні, місяці, квартали або роки.
Порівняння значень із часом
У наведених нижче статтях наведено приклади порівняння сум за різні періоди часу. Стандартні періоди часу, які підтримуються в DAX, – це місяці, квартали та роки.
Обчислення значення за настроюваним діапазоном дат
Нижче наведено приклади отримання спеціальних діапазонів дат, як-от перші 15 днів після початку акції.
Якщо для отримання настроюваного набору дат використовуються функції часового інтелекту, цей набір дат можна використовувати як вхідні дані для функції, яка виконує обчислення, щоб створювати настроювані агрегати протягом різних періодів часу. У наведеному нижче розділі наведено приклади таких дій:
-
Примітка.: Якщо не потрібно вказувати спеціальний діапазон дат, але ви працюєте зі стандартними звітними одиницями, наприклад місяцями, кварталами або роками, радимо виконувати обчислення за допомогою функцій часового аналітика, призначених для таких цілей, як TOTALQTD, TOTALMTD, TOTALQTD тощо.
Сценарії: ранжування та порівняння значень
Щоб відобразити лише верхню кількість елементів у стовпці або зведеній таблиці, доступні кілька варіантів:
-
Щоб створити основний фільтр, можна Excel 2010 року. Ви також можете вибрати кількість найвищих або найменших значень у зведеній таблиці. У першій частині цього розділу описано, як фільтрувати 10 перших елементів зведеної таблиці. Докладні відомості див. в Excel документації.
-
Ви можете створити формулу, яка динамічно ранжує значення, а потім фільтрувати їх за ранжуванням значень або використовувати ранжування як роздільник. У другій частині цього розділу описано, як створити цю формулу, а потім використовувати цей рейтинг у роздільнику.
Кожен метод має свої переваги та недоліки.
-
Верхній Excel зручний у використанні, але фільтр використовується лише для відображення. Якщо дані, на які вказуватимуться зміни у зведеній таблиці, необхідно вручну оновити зведену таблицю, щоб побачити зміни. Якщо вам потрібно динамічно працювати з ранжуваннями, ви можете скористатися формулою DAX, щоб порівнювати значення з іншими значеннями в стовпці.
-
Формула DAX потужніша; Крім того, додаючи значення ранжування до роздільник, можна просто клацнути роздільник, щоб змінити кількість перших відображуваних значень. Проте обчислення обчислень є вагомими, і цей метод може не підійти для таблиць із багатьма рядками.
Відображення десяти верхніх елементів зведеної таблиці
|
Відображення верхніх або найменших значень у зведеній таблиці
|
Динамічне впорядкування елементів за допомогою формули
У наведеній нижче статті наведено приклад того, як за допомогою DAX створити ранжування, що зберігається в обчислюваному стовпці. Оскільки формули DAX обчислюються динамічно, ви завжди можете бути певні, що ранжування правильне, навіть якщо змінилися відповідні дані. Крім того, оскільки формула використовується в обчислюваному стовпці, можна скористатися ранжуванням у роздільнику, а потім вибрати перші 5, перші 10 або навіть перші 100 значень.
