У цьому розділі описано, як створювати фільтри у формулах виразів аналізу даних (DAX). Ви можете створювати фільтри у формулах, щоб обмежити значення з вихідних даних, які використовуються в обчисленнях. Для цього потрібно вказати таблицю як введену формулу, а потім визначити вираз фільтра. Указаний вираз фільтра використовується для запиту даних і повертає лише підмножину вихідних даних. Фільтр застосовується динамічно щоразу під час оновлення результатів формули залежно від поточного контексту даних.
У цій статті
Створення фільтра в таблиці, яка використовується у формулі
Ви можете застосовувати фільтри у формулах, які приймають таблицю як введені дані. Замість того щоб вводити ім'я таблиці, скористайтеся функцією FILTER, щоб визначити підмножину рядків із вказаної таблиці. Після цього ця підмножина передається іншій функції для таких операцій, як настроювані агрегації.
Наприклад, у вас є таблиця даних із відомостями про замовлення торговельних партнерів і потрібно обчислити обсяг проданих торговельних партнерів. Однак потрібно відобразити обсяг збуту лише для торговельних партнерів, які продали кілька одиниць товарів із вищою вартістю. Наведена нижче формула на основі зразка книги DAX показує один із прикладів того, як можна створити це обчислення за допомогою фільтра:
=SUMX(
FILTER ('ResellerSales_USD','ResellerSales_USD'[Кількість] > 5 &&
'ResellerSales_USD'[ProductStandardCost_USD] > 100),
'ResellerSales_USD'[SalesAmt]
)
-
Перша частина формули визначає одну з функцій агрегації Power Pivot, яка приймає таблицю як аргумент. Функція SUMX обчислює суму над таблицею.
-
Друга частина формули FILTER(table, expression),повідомляє SUMX , які дані слід використовувати. SUMX потрібна таблиця або вираз, результатом яких є таблиця. Тут замість того щоб використовувати всі дані в таблиці, скористайтеся функцією FILTER , щоб указати, які рядки з таблиці використовуються.
Вираз фільтра містить дві частини: перша частина – таблиця, до якої застосовується фільтр. Друга частина визначає вираз для використання як умову фільтра. У цьому випадку ви фільтруєте торговельних партнерів, які продали більше 5 одиниць і продуктів вартістю понад 100 грн. Оператор , &&, є логічним оператором AND, який вказує на те, що обидві частини умови мають бути істинними, щоб рядок належав до відфільтрованого підмножини.
-
Третя частина формули повідомляє функції SUMX , які значення слід підсумувати. У цьому випадку ви використовуєте лише обсяг збуту.
Зверніть увагу, що такі функції, як FILTER, які повертають таблицю, ніколи не повертають таблицю або рядки безпосередньо, але завжди вбудовані в іншу функцію. Докладні відомості про функцію FILTER та інші функції, які використовуються для фільтрування, зокрема інші приклади, див. в статті Функції фільтрування (DAX)."
Примітка.: На вираз фільтра впливає контекст, у якому він використовується. Наприклад, якщо в мірі використовується фільтр, який використовується у зведеній таблиці або зведеній діаграмі, на підмножину даних, які повертаються, можуть впливати додаткові фільтри або роздільники, застосовані користувачем у зведеній таблиці. Докладні відомості про контекст див. в статті Контекст у формулах DAX.
Фільтри, які видаляють повтори
Окрім фільтрування певних значень, можна повернути унікальний набір значень з іншої таблиці або стовпця. Це може бути корисно, якщо потрібно підрахувати кількість унікальних значень у стовпці або використати список унікальних значень для інших операцій. У DAX передбачено дві функції для повернення окремих значень: DISTINCT (функція DISTINCT) і VALUES (функція VALUES).
-
Функція DISTINCT аналізує один стовпець, указаний як аргумент функції, і повертає новий стовпець, який містить лише окремі значення.
-
Функція VALUES також повертає список унікальних значень, але також повертає невідомий елемент. Це зручно, якщо використовуються значення з двох таблиць, об'єднаних зв'язками, а значення відсутнє в одній таблиці та наявне в іншій. Докладні відомості про невідомий елемент див. в статті Контекст у формулах DAX.
Обидві ці функції повертають цілий стовпець значень; таким чином, ви використовуєте функції, щоб отримати список значень, які потім передаються до іншої функції. Наприклад, за допомогою наведеної нижче формули можна отримати список окремих продуктів, проданих певним торговельним партнером, за допомогою унікального ключа продукту, а потім підрахувати кількість продуктів у цьому списку за допомогою функції COUNTROWS:
=COUNTROWS(DISTINCT('ResellerSales_USD'[Ключ продукту]))
Вплив контексту на фільтри
Якщо додати формулу DAX до зведеної таблиці або зведеної діаграми, на результати формули може впливати контекст. Якщо ви працюєте в Power Pivot таблиці, контекстом буде поточний рядок і його значення. Якщо ви працюєте у зведеній таблиці або зведеній діаграмі, контекст означає набір або підмножину даних, визначених операціями, такими як нарізка або фільтрування. Макет зведеної таблиці або зведеної діаграми також накладає власний контекст. Наприклад, якщо створити зведену таблицю, у якій згруповано обсяги збуту за регіоном і роком, у зведеній таблиці відображатимуться лише ті дані, які застосовуються до цих регіонів і років. Тому будь-які міри, додані до зведеної таблиці, обчислюються в контексті заголовків стовпців і рядків і всіх фільтрів у формулі мір.
Докладні відомості див. в статті Контекст у формулах DAX.
Видалення фільтрів
Працюючи зі складними формулами, ви можете точно знати, що таке поточні фільтри, або змінити частину фільтра формули. У DAX передбачено кілька функцій, які дають змогу видаляти фільтри та визначати, які стовпці зберігаються в поточному контексті фільтра. У цьому розділі наведено огляд того, як ці функції впливають на результати у формулі.
Перевизначення всіх фільтрів за допомогою функції ALL
За допомогою функції ALL можна перевизначити всі раніше застосовані фільтри та повернути всі рядки таблиці до функції, яка виконує агрегатну або іншу операцію. Якщо використовується один або кілька стовпців замість таблиці як аргументи для ALL, функція ALL повертає всі рядки, ігноруючи будь-які контекстні фільтри.
Примітка.: Якщо ви знайомі з термінологією реляційної бази даних, можна вважати ALL створенням природного лівого зовнішнього об'єднання всіх таблиць.
Припустімо, наприклад, що у вас є таблиці, "Продажі" та "Товари", і потрібно створити формулу, яка обчислить суму збуту для поточного продукту, розділеного на збут для всіх продуктів. Слід враховувати той факт, що, якщо формула використовується в мірі, користувач зведеної таблиці може використовувати роздільник для фільтрування певного продукту з назвою продукту в рядках. Таким чином, щоб отримати значення true знаменника незалежно від будь-яких фільтрів або роздільників, потрібно додати функцію ALL, щоб перевизначити всі фільтри. Нижченаведена формула – це один із прикладів того, як за допомогою функції ALL перевизначити ефекти попередніх фільтрів:
=SUM (Продаж[Обсяг])/SUMX(Збут[Обсяг],FILTER(Збут; ALL(Продукти)))
-
Перша частина формули SUM (Продаж[Сума]) обчислює чисельник.
-
Сума враховує поточний контекст, тобто якщо додати формулу до обчислюваного стовпця, застосовується контекст рядка та якщо додати формулу до зведеної таблиці як міру, застосовуватимуться всі фільтри, застосовані у зведеній таблиці (контекст фільтра).
-
Друга частина формули обчислює знаменник. Функція ALL перевизначає всі фільтри, які можуть застосовуватися до таблиці Products .
Докладні відомості, зокрема докладні приклади, див. в статті Функція ALL.
Перевизначення певних фільтрів за допомогою функції ALLEXCEPT
Функція ALLEXCEPT також перевизначає наявні фільтри, але можна вказати, що деякі з наявних фільтрів слід зберегти. Стовпці, які ви називаєте аргументами функції ALLEXCEPT, визначають, які стовпці й надалі фільтруватимуться. Якщо потрібно перевизначити фільтри з більшості стовпців, але не всіх, allEXCEPT зручніше, ніж ALL. Функція ALLEXCEPT особливо корисна під час створення зведених таблиць, які можуть бути відфільтровані за багатьма різними стовпцями, і потрібно керувати значеннями, які використовуються у формулі. Докладні відомості, зокрема докладний приклад використання функції ALLEXCEPT у зведеній таблиці, див. в статті Функція ALLEXCEPT.
