Використовуйте IsInScope, щоб отримати правильний рівень ієрархії в DAX

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

Сценарій

Давайте розглянемо сценарій, коли для бізнесу існують деякі спеціальні правила для підрахунку підсумків. Для кожного підсумку в ієрархії результати розраховуються заздалегідь (проста сума буде неправильною), не на відміну від одинарних операторів або спеціальних ролей, як ми їх маємо в AS Multi Dimensions.

Ми починаємо з такої ієрархії. Як уже згадувалося, підсумки вже обчислюються, тому нам знадобиться рядок у таблиці для кожного з них. Щоб мати змогу зробити правильний розрахунок, нам потрібен спосіб дізнатися, який рядок загальний чи ні, тому я додав стовпець IsTotal.

Для кожного рівня в ієрархії ми маємо значення в таблиці фактів:

Далі я створюю відносини та створюю ієрархію .

Нарешті я перетягую стовпець Значення та Ієрархію в матрицю (я також увімкнув значки +/-, щоб ми могли розгорнути згортання, що є ще однією особливістю листопада). Перше, що ми бачимо, це те, що матриця виглядає дивно із пробілами та іншим.

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

DAX

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

Як варіант, ми можемо перевірити, чи перебуваєте ви на рівні, підрахувавши кількість значень для певного стовпця. Якщо це 1, ви знаєте, що ви на цьому рівні. DAX буде виглядати так:

Захід 2 =
ЯКЩО (
ОБРАХУВАТИ (
ЗНАЧЕННЯ (ЦІННОСТІ (Продавці [Продавець])),

ALLEXCEPT (Продавці, Продавці [Продавець])
)
= 1,
ІСТИНА ()
)

або написаний простим скороченням (робить те саме, що вище, але інкапсульовано в єдину функцію):

Захід 2 =
ЯКЩО (HASONEFILTER (Продавці [Продавець]), ІСТИНА ())

Цей показник призводить до істини на найнижчому рівні, він використовує ALLExcept для очищення будь-яких фільтрів, встановлених у продавця ззовні (наприклад, слайсер).

Нещодавнє оновлення Power BI та функції IsInScope ми зробили це ще простіше. Замість ускладненого виразу вище ви можете написати це:

Захід 2 =
ЯКЩО (ISINSCOPE (Продавці [Продавець]), ІСТИНА ())

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

Повернення правильного розрахунку за рівень

Нарешті, якщо ви хочете протестувати будь-який інший рівень, тоді нижній вам потрібно буде протестувати для обох. Ось що відбувається, коли я тестую на Channel:

Як ви можете бачити, обидва повертають true, що зрозуміло, оскільки обидва вони мають обсяг. Для вирішення цього вам доведеться протестувати обидва. Ось так (я знову використовую трюк Switch True):

Захід 2 =
ПЕРЕКЛЮЧАТЕЛЬ (
ІСТИНА,
ISINSCOPE (Продавці [Продавець]), “продавець”,
ISINSCOPE (Продавці [Канал]), “канал”,
"інший"
)

Тепер це дозволяє нам побачити, на якому рівні я перебуваю:

Поклавши все це у вираз DAX, який повертає різні обчислення для кожного рівня.

Міра =
Загальна сума VAR =
ОБРАХУВАТИ ([Сума вартості], Продавці [IsTotal] = 3)
VAR sellersum =
ОБРАХУВАТИ ([Сума вартості], Продавці [IsTotal] = 0)
Вибрано продавця VAR =
ISINSCOPE (продавці [Продавець])
Вибрано канал VAR =
ISINSCOPE (продавці [канал])
VAR Countrysum =
ОБРАХУВАТИ (
[Сума вартості],
Продавці [IsTotal] = 1,
ALLEXCEPT (Продавці, Продавці [КРАЇНА])
)
Вибрано країну VAR =
ISINSCOPE (продавці [Країна])
VAR Regionsum =
ОБРАХУВАТИ (
[Сума вартості],
Продавці [IsTotal] = 2,
ALLEXCEPT (Продавці, Продавці [Регіон])
)
Вибрано регіон VAR =
ISINSCOPE (продавці [Регіон])
ПОВЕРНЕННЯ
ПЕРЕКЛЮЧАТЕЛЬ (
ІСТИНА (),
продавецьвідбирається, продавецьсум,
канал вибрано, ЧИСТО (),
вибрано країну, Countrysum,
вибраний регіон, Регіонсум,
загальна сума
)

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

Тепер він показує значення для кожного рівня на основі нашого нестандартного розрахунку, не використовуючи звичайну суму. Досить круто 🙂