Фільтрування даних у формулах DAX

У цьому розділі описано, як створювати фільтри у формулах виразів аналізу даних (DAX). У формулах можна створювати фільтри, щоб обмежити значення з вихідних даних, які використовуються в обчисленнях. Для цього потрібно вказати таблицю як вхідні дані для формули, а потім визначити вираз фільтра. Наданий вираз фільтра використовується для запитування даних і повернення лише підмножини вихідних даних. Фільтр застосовується динамічно щоразу, коли ви оновлюєте результати формули, залежно від поточного контексту даних.

У цій статті

• Створення фільтра на основі таблиці, використаної у формулі

• Фільтри, які видаляють дублікати

• Вплив контексту на фільтри

• Видалення фільтрів

  • Перевизначення всіх фільтрів за допомогою функції ALL
  • Перевизначення певних фільтрів за допомогою функції ALLEXCEPT

Створення фільтра на основі таблиці, використаної у формулі

Фільтри можна застосовувати у формулах, які приймають для введення таблицю. Замість імені таблиці використовується функція FILTER для визначення підмножини рядків із вказаної таблиці. Потім ця підмножина передається в іншу функцію для таких операцій, як користувацькі агрегації.

Наприклад, у вас є таблиця даних, що містить відомості про замовлення торговельних партнерів, і ви хочете обчислити, скільки кожного торговельного партнера продано. Проте потрібно відобразити обсяг збуту лише для тих торговельних партнерів, які продали кілька одиниць товару з вищою вартістю. У формулі нижче, створеній на основі зразка книги DAX, наведено приклад того, як створити це обчислення з використанням фільтра.

=SUMX(
     FILTER ('ResellerSales_USD', 'ResellerSales_USD'[Кількість] > : 5 &&
     'ResellerSales_USD'[ProductStandardCost_USD] > 100),
     'ResellerSales_USD'[SalesAmt]
     )

• Перша частина формули визначає одну з функцій збирання Power Pivot, яка використовує таблицю як аргумент. Функція SUMX обчислює суму над таблицею.

• Друга частина формули визначає SUMX дані, FILTER(table, expression),які слід використовувати. SUMX Потрібна таблиця або вираз, результатом яких є таблиця. Тут замість використання всіх даних у таблиці за FILTER допомогою функції можна вказати, який із рядків таблиці слід використовувати.
  Вираз фільтра складається з двох частин: перша частина називає таблицю, до якої застосовується фільтр. Друга частина визначає вираз, який потрібно використовувати як умову фільтра. У цьому випадку ви фільтруєте торговельних партнерів, які продали понад 5 одиниць товару, і товари вартістю понад 100 грн. Оператор && – це логічний оператор AND, який указує, що для того, щоб рядок належав до відфільтрованої підмножини, мають бути істинні обидві частини умови.

• Третя частина формули вказує SUMX функції, які значення потрібно підсумовувати. У цьому випадку використовується тільки обсяг збуту.
  Зверніть увагу, що функції, як-от FILTER, які повертають таблицю, ніколи не повертають таблицю або рядки безпосередньо, але завжди вбудовані в іншу функцію. Докладні відомості про функцію FILTER та інші функції фільтрування, зокрема про інші приклади, див. в статті "Функції фільтра" (DAX).

  Примітка.

  На вираз фільтра впливає контекст, у якому він використовується. Наприклад, якщо в мірі використовується фільтр, а міра застосовується у зведеній таблиці або зведеній діаграмі, на підмножину даних, що повертаються, можуть впливати додаткові фільтри або роздільники, застосовані користувачем у зведеній таблиці. Докладні відомості про контекст див. в статті "Контекст" у формулах DAX.

Фільтри, які видаляють дублікати

Ви можете отримати унікальний набір значень з іншої таблиці або стовпця, окрім фільтрування за певними значеннями. Це може стати в пригоді, якщо потрібно підрахувати кількість унікальних значень у стовпці або використати список унікальних значень для інших операцій. DAX надає дві функції для повернення різних значень: функцію DISTINCT і функцію VALUES.

• Функція DISTINCT перевіряє один стовпець, указаний як аргумент функції, і повертає новий стовпець, що містить лише значення DISTINCT.
• Функція VALUES також повертає список унікальних значень, але також повертає невідомий елемент. Це зручно, коли використовуються значення з двох таблиць, об'єднаних зв'язком, коли значення відсутнє в одній таблиці та наявне в іншій. Докладні відомості про невідомий член див. в статті "Контекст у формулах DAX".

Обидві ці функції повертають цілий стовпець значень; Таким чином, за допомогою цих функцій можна отримати список значень, які потім передаються в іншу функцію. Наприклад, за допомогою наведеної нижче формули можна отримати список окремих продуктів, які продає певний торговельний партнер, використовуючи унікальний ключ продукту, а потім підрахувати кількість продуктів у цьому списку за допомогою функції COUNTROWS:

=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))

На початок сторінки

Вплив контексту на фільтри

Коли ви додаєте формулу DAX до зведеної таблиці або зведеної діаграми, контекст може впливати на результати формули. Якщо ви працюєте в таблиці Power Pivot, контекстом є поточний рядок і його значення. Якщо ви працюєте у зведеній таблиці або зведеній діаграмі, під контекстом розуміється набір або підмножина даних, визначених такими діями, як вибірка або фільтрування. Макет зведеної таблиці або зведеної діаграми також накладає власний контекст. Наприклад, якщо створити зведену таблицю, у якій згруповано обсяги продажу за регіонами та роками, у зведеній таблиці відображатимуться лише ті дані, що стосуються цих регіонів і років. Таким чином, будь-які міри, додані до зведеної таблиці, обчислюються в контексті заголовків стовпців і рядків, а також будь-яких фільтрів у формулі міри.

Докладні відомості див. в статті "Контекст у формулах DAX".

На початок сторінки

Видалення фільтрів

Працюючи зі складними формулами, можливо, потрібно точно знати, які фільтри існують, або змінити частину фільтра у формулі. DAX надає кілька функцій, які дають змогу видаляти фільтри та керувати тим, які стовпці потрібно зберегти в контексті поточного фільтра. У цьому розділі в загальних рисах описано, як ці функції впливають на результати у формулі.

Перевизначення всіх фільтрів за допомогою функції ALL

За допомогою цієї ALL функції можна змінити будь-які раніше застосовані фільтри та повернути всі рядки в таблиці функції, яка виконує агрегатну або іншу операцію. Якщо ви використовуєте один або кілька стовпців замість таблиці як ALL аргументиALL, функція повертає всі рядки, ігноруючи будь-які контекстні фільтри.

Наприклад, припустімо, що у вас є таблиці "Збут" і "Продукти", і вам потрібно створити формулу, яка обчислюватиме суму продажу поточного продукту, поділену на обсяг продажу всіх продуктів. Слід враховувати, що, якщо формула використовується в мірі, користувач зведеної таблиці може використовувати роздільник, щоб фільтрувати дані за певним продуктом, у рядках якого вказується назва продукту. Тому, щоб отримати справжнє значення знаменника незалежно від будь-яких фільтрів чи роздільників, потрібно додати функцію ALL, щоб перевизначити всі фільтри. Наведена нижче формула демонструє, як за допомогою функції ALL замінити дію попередніх фільтрів.

=SUM (Продажі[Сума])/SUMX(Продажі[Сума], ФІЛЬТР(Продажі, ВСІ(Продукти)))

• У першій частині формули – SUM (Sales[Amount]) – обчислюється чисельник.
• У підсумку враховується поточний контекст, тобто якщо додати формулу до обчислюваного стовпця, буде застосовано контекст рядка, а якщо додати формулу до зведеної таблиці як міру, буде застосовано всі фільтри, застосовані у зведеній таблиці (контекст фільтра).
• Друга частина формули обчислює знаменник. Функція ALL перевизначає будь-які фільтри, які можуть застосовуватися до Products таблиці.

Докладні відомості, зокрема докладні приклади, див. в статті про функцію ALL.

Перевизначення певних фільтрів за допомогою функції ALLEXCEPT

Крім того, функція ALLEXCEPT замінює наявні фільтри, але деякі з наявних фільтрів можна вказати, що деякі з наявних фільтрів мають бути збережені. Стовпці, які ви називаєте як аргументи функції ALLEXCEPT визначають, які стовпці й надалі фільтруватимуться. Якщо потрібно перевизначити фільтри для більшості стовпців, але не для всіх, то функція ALLEXCEPT зручніша за значення ALL. Функція ALLEXCEPT особливо корисна, коли створюється зведена таблиця, яка може фільтруватися за багатьма різними стовпцями, і потрібно контролювати значення, що використовуються у формулі. Докладні відомості, зокрема детальний приклад використання функції ALLEXCEPT у зведеній таблиці, див. у статті про функцію ALLEXCEPT

На початок сторінки