[DAX] Динамічна відносна фільтрація в slicer за допомогою віртуальних зв'язків DAX



Для початку розповім про саме завдання. Потім перейдемо до мого варіанта її рішення.

До мене звернувся колега та запитав, як в візуалі slicer налаштувати фільтр дат так, щоб вибираючи довільну дату фільтрувався графік та показував дані на тиждень назад включаючи цю дату. Наприклад, вибираючи 6 серпня 2009 ми повинні бачити на графіку період дат з 31 липня до 7 серпня 2009 року. Ось приклад самого графіку - Продажи (TotalSales) у розрізі дат:



Звісно ми пішли дивитись стандартний функціонал в slicer. Там є опція "Відносне дата" (див. рисунок нижче)
Вибираємо пункт "Відносна дата".

Як ми бачимо - не має можливості вибрати довільну дату, так як "Відносне значення" передбачає, що дата відліку буде завжди рівна равна поточній даті в календарі.

РІШЕННЯ.

Крок 1. Створюємо додаткову таблицю тільки з одним стовпцем дат. Вона буде використовуватись для фільтрації. Варіантів її створення є декілька (через PowerQuery та DAX). Я вибрав опцію зі створення віртуальної DAX таблиці з наступною формулою:
Date2 = VALUES('Date'[Date])
, де
Date2 - назва нової таблиці
'Date'[Date] - поле з датами в існуючій таблиці "Date"

Крок 2. Створюємо slicer з датами із таблиці, створенної у Кроці 1. Вибираємо варіант відображення розкривний список.

Крок 3. У нас є попередньо створена міра, яка рахує наші продажі:

TotalSales = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])​

І тепер нам треба створити нову міру, яка буде рахувати TotalSales тільки для тих дат, котрі будуть в діапазоні тижня від вибраної дати в slicer із Кроку 2. Потім ми замінимо в графіку TotalSales на неї. Ось сам код нової міри:


TotalSales2
VAR CurrentDate = IF(HASONEVALUE('Date2'[Date]),SELECTEDVALUE('Date2'[Date]),0)
VAR ReportingDate = MAX('Date'[Date])

RETURN
    CALCULATE(
        [TotalSales],
        FILTER(
            DATESINPERIOD('Date'[Date],CurrentDate,-7,DAY),
            'Date'[Date] = ReportingDate
        )
    )​

Пояснюю код. Для початку створимо дві змінні:

1) CurrentDate - змінна, яка переменная, которая зчитує поточний контекст з поля 'Date2'[Date], якщо вибрано тільки одне значення. Іншими словами отримуємо значення обраної дати

2) ReportingDate - змінна, яка зчитує дату контексту в рядку візуала таблиці або в нашому прикладі точки графіка. Нагадаю, що у нас по осі X йдуть дати з поля 'Date' [Date]


Потім за допомогою функції CALCULATE ми накладаємо фільтр на дати по осі Х - якщо дата в точці графіка в списку CurrentDate-7, то показуй TotalSales, інакше буде порожня міра.

Завдяки можливості Power BI приховувати порожні значення заходів наш графік урізається до дат потрібного періоду.

Для зручності фільтрації дат я ще додав два slicer з роком і місяцем:


І ось фінальний звіт

У даному рішенні була використана мова DAX з технікою використання набору значень одного поля як фільтри до іншого без наявності фізичних зв'язків (між таблицями Date2 і Sales, де була наша міра TotalSales). 

Звіт з цього прикладу можете знайти нижче та за посиланням.

Коментарі

Популярні дописи з цього блогу

Power BI Reports Monitoring Tool in 5 minutes

[Power BI] Створена Power BI версія Vertipaq Analyzer. Додано автоматичне форматування коду