Сценарії DAX у Power Pivot - Служба підтримки Office
Цей розділ містить посилання на приклади, що демонструють використання формул DAX в наступних сценаріях.
Виконання складних обчислень
Робота з текстом та датами
Умовні значення та перевірка на наявність помилок
Використання логіки операцій з часом
Ранжування та порівняння значень
В цій статті
Початок роботи
Зразок даних
Якщо ви новачок у формулах DAX, вам, можливо, потрібно почати з вивчення прикладів даних прикладу для Power Pivot. Для отримання додаткових відомостей див. розділ Отримання зразків даних у навчальних курсах DAX та моделі даних.
Додаткові ресурси
Сценарії: виконання складних обчислень
У формулах DAX можна виконувати складні обчислення, що включають користувацькі агрегати, фільтрацію та використання умовних значень. У цьому розділі наводяться приклади того, як розпочати роботу з обчисленнями користувача.
Створення обчислень для зведеної таблиці
Застосування фільтра до формули
У більшості випадків, коли функція DAX приймає як аргумент таблицю, зазвичай можна передати відфільтровану таблицю, використовуючи функцію FILTER замість імені таблиці, або вказавши вираз фільтра як один з аргументів функції. У наступних розділах наведено приклади створення фільтрів та впливу фільтрів на результати формул. Для отримання додаткових відомостей див. Фільтрування даних у формулах DAX.
Функція FILTER дозволяє встановити умови фільтрації за допомогою виразу, а інші функції — спеціально для фільтрації порожніх значень.
Вибірковий видалення фільтрів для створення динамічних співвідношення.
Створення динамічних фільтрів у формулах дозволяє легко знаходитинаступні питання:
Який внесок продажу поточного товару у загальну суму продажів протягом року?
Наскільки цей поділ узяв участь у загальному прибутку за всіма робочими роками, порівняно з іншими підрозділами?
На формули, які використовуються у зведеній таблиці, може вплинути контекст зведеної таблиці, але ви можете вибірково змінити контекст, додавши або вилучивши фільтри. У прикладі розділу "все" показано, як це зробити. Щоб дізнатися про відношення продажів для певного торгового посередника до продажу для всіх торгових посередників, ви створюєте міру, яка обчислює значення поточного контексту, розділене на значення для всього контексту.
У розділі ALLEXCEPT міститься приклад вибіркового очищення фільтрів за формулою. В обох прикладах показано, як змінюється результат, залежно від структури зведеної таблиці.
Використання значення із зовнішнього циклу
Крім використання значень з поточного контексту, обчислення DAX можуть використовувати значення з попереднього циклу для створення набору пов'язаних обчислень. У наступному розділі описано, як створити формулу, яка посилається на значення із зовнішнього циклу. Функція більш ранньої версії підтримує до двох рівнів вкладених циклів.
Щоб отримати додаткові відомості про контекст рядків та пов'язані таблиці, а також про те, як використовувати цю концепцію у формулах, див. розділ контекст у формулах DAX.
Сценарії: робота з текстом та датами
У цьому розділі наводяться посилання на довідкові розділи DAX, що містять приклади поширених сценаріїв, пов'язаних з роботою з текстом, вилучення та складання значень дати та часу, а також створення значень залежно від умови.
Створення ключового стовпця шляхом об'єднання
Power Pivot не підтримує складовіключі; Таким чином, якщо у джерелі даних є складові ключі, вам, можливо, потрібно об'єднати їх в один ключовий стовпець. У наступному розділі наведено один із прикладів створення обчислюваного стовпця в залежності від складового ключа.
Складання дати з урахуванням частин дати, витягнутих із текстової дати
Power Pivot використовує тип даних "Дата/час" SQL Server для роботи з датами; Таким чином, якщо зовнішні дані містять дати, відформатовані по-іншому, наприклад, якщо ваші дати написані у стандартному форматі дати, який не розпізнається ядром даних Power Pivot, або якщо ваші дані використовують цілочисленні сурогатні ключі, можливо, потрібно використовувати формулу DAX для отримання частин дати та подальшого складання частин у припустиме подання дати та часу.
Наприклад, якщо у вас є стовпець дат, який був представлений у вигляді цілого числа, а потім імпортований у вигляді текстового рядка, то можна перетворити рядок на значення дати та часу за допомогою наступної формули:
= DATE (RIGHT ([значення1]; 4); LEFT ([значення1]; 2); MID ([значення1]; 2))
У наступних розділах наведено додаткові відомості про функції, які використовуються для отримання та створення дат.
Визначення формату користувача дати або числа
Якщо дані містять дати або числа, не представлені в одному зі стандартних форматів тексту Windows, можна визначити формат користувача, щоб переконатися, що значення обробляються правильно. Ці формати використовуються для перетворення значень у рядки або з рядків. У наступних розділах також наведено докладний список вбудованих форматів, які можна використовувати для роботи з датами та числами.
Зміна типів даних за допомогою формули
Power Pivot типданих виводу визначається вихідними стовпцями, і ви можете явно вказати тип даних результату, оскільки оптимальний тип даних визначається з допомогою Power Pivot. Проте можна використовувати неявні перетворення типів даних, які виконуються функцією Power Pivot, для роботи з типом вихідних даних. Додаткові відомості про перетворення типів див. у статті Отримання зразків даних у навчальних курсах DAX та моделі даних.
Для перетворення дати або числового рядка на число помножте його на 1,0. Наприклад, наступна формула обчислює поточну дату мінус 3 дні, потім виводить відповідне ціле значення.
Щоб перетворити значення дати, числа або грошової одиниці на рядок, об'єднайте значення з порожнім рядком. Наприклад, наступна формула повертає сьогоднішню дату у вигляді рядка.
Також можна використовувати такі функції для забезпечення певного типу даних:
Перетворення дійсних чисел на цілі числа
Перетворення дійсних чисел, цілих чисел чи дат у рядки
Перетворення рядків у реальні числа чи дати
Сценарій: умовні значення та перевірка на наявність помилок
Як і в Excel, DAX містить функції, що дозволяють перевіряти значення даних і повертати інше значення залежно від умови. Наприклад, ви можете створити стовпець, що обчислюється, який буде позначкам торгових посередників як переважні або величини в залежності від суми продажів за рік. Функції, які перевіряють значення також корисні для перевірки діапазону або типу значень, щоб уникнути непередбачених помилок при порушенні обчислень.
Створення значення за допомогою умови
За допомогою вкладених умов IF можна перевіряти значення та створювати нові значення умовно. Нижче описані деякі простіприклади умовної обробки та умовних значень.
Перевірка помилок у формулі
На відміну від Excel, значення в одному рядку стовпця, що обчислюється, не можуть бути допустимими і недопустимими значеннями в іншому рядку. Це означає, що якщо в будь-якій частині стовпця Power Pivot є помилка, весь стовпець помічається помилкою, тому необхідно завжди виправляти помилки у формулах, результатом яких є неприпустимі значення.
Щоб уникнути можливого повернення помилок у обчислюваному стовпці, ви використовуєте поєднання логічних та даних функцій для перевірки помилок і завжди повертають допустимі значення. У наступних розділах наведено прості приклади виконання цього завдання у DAX.
Сценарії: використання логіки операцій із часом
Функції логіки операцій з часом DAX дозволяють отримувати дати або діапазони дат даних. Потім можна використовувати ці дати або діапазони дат для обчислення значень протягом подібних періодів. Функції логіки операцій з часом також відносяться до функцій, які працюють із стандартними інтервалами дат, що дозволяє порівнювати значення за місяцями, роками або кварталами. Ви також можете створити формулу, яка порівнює значення для першої та останньої дати зазначеного періоду.
Список усіх функцій логіки операцій з часом див. у розділі функції логіки операцій з часом (DAX). Поради щодо ефективного використання дат та часу в Power Pivot аналізі див. у статті дати Power Pivot.
Обчислення сукупного продажу
У наступних розділах містяться приклади розрахунку закритих та відкриваючих балансів. У прикладах можна створювати баланси для різних інтервалів, таких як дні, місяці, квартали чи роки.
Порівняння значень з часом
Наступні розділи містять прикладипорівняння сум за різні періоди часу. За замовчуванням DAX підтримуються місяці, квартали та роки.
Обчислення значення в діапазоні дат, що настроюється
Якщо ви використовуєте функції логіки операцій з часом для отримання настроюваного набору дат, то можете використовувати цей набір дат як вхідні дані для функції, що виконує обчислення, для створення агрегатів протягом декількох періодів часу. Приклад виконання цього завдання див. у наступному розділі.
Сценарії: ранжування та порівняння значень
Щоб відобразити лише перші n числових елементів у стовпці або зведеній таблиці, можна використати кілька варіантів:
Для створення верхнього фільтра можна скористатися функціями Excel 2010. Крім того, у зведеній таблиці можна вибрати кілька верхніх чи нижніх значень. У першій частині цього розділу описано, як фільтрувати перші 10 елементів зведеної таблиці. Для отримання додаткових відомостей див. документацію Excel.
Ви можете створити формулу, яка динамічно ранжирує значення, а потім відфільтрувати їх за ранжованими значеннями або використовувати значення ранжирування як зріз. У другій частині розділу описано, як створити цю формулу, а потім використовувати її в зрізі.
Кожен метод має свої переваги і недоліки.
Фільтр Excel Top простий у використанні, але фільтр призначений лише для відображення. Якщо базові дані змінюються у зведеній таблиці, необхідно вручну оновити зведену таблицю, щоб переглянути зміни. Якщо вам потрібно динамічно працювати з ранжируваннями, можна використовувати DAX для створення формули, яка порівнює значення з іншими значеннями у стовпці.
Формула DAX потужніша; Крім того, шляхом додавання значенняранжування в зріз можна просто клацнути зріз, щоб змінити кількість верхніх значень, що відображаються. Однак обчислення є ресурсомісткими, і цей метод може не підходити для таблиць з великою кількістю рядків.
Відображення лише десяти перших елементів у зведеній таблиці
Відображення найбільших чи найменших значень у зведеній таблиці
У зведеній таблиці клацніть стрілку вниз у заголовку Мітки рядків.
Виберіть фільтри за значенням _гт_ перші 10.
У діалоговому вікні перші 10 фільтрів _Лт_колумн наме_гт_ виберіть стовпець для ранжування та число значень наступним чином.
Натисніть кнопку зверху , щоб відобразити комірки з найвищими значеннями або знизу , щоб відобразити комірки з найменшими значеннями.
Введіть число верхніх або нижніх значень, які потрібно відобразити. Значення за замовчуванням – 10.
Виберіть спосіб відображення значень:
Виберіть цей параметр, щоб відфільтрувати зведену таблицю, щоб відобразити лише список найбільших або найменших елементів за їхніми значеннями.
Виберіть цей параметр, щоб відфільтрувати зведену таблицю, щоб відображатися лише елементи, які додаються до зазначеного відсотка.
Виберіть цей параметр для відображення суми значень для найбільших або найменших елементів.
Виділіть стовпець, який містить значення, які потрібно ранжувати.
Натисніть кнопку ОК.
Динамічне впорядкування елементів за допомогою формули
У наступному розділі міститься приклад використання DAX для створення ранжування, що зберігається в стовпці, що обчислюється. Так як формули DAX обчислюються динамічно, ви завжди можете переконатися, що ранжування є вірним, навіть якщо базові дані змінилися. Крім того, так як формула використовується вобчислюваному стовпці, можна використовувати ранжування у зрізах, а потім вибрати перші 5, 10 верхніх або навіть верхніх значень 100.