Kharkiv Power BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.

Динамическая относительная фильтрация в slicer с помощью виртуальных связей DAX

  • 1.  Динамическая относительная фильтрация в slicer с помощью виртуальных связей DAX

    Silver Contributor
    Posted Jul 25, 2019 05:46 PM
    Edited by Ruslan Zolotukhin Jul 25, 2019 05:58 PM
    Дорогие участники группы и просто посетители страницы,

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

    Ко мне обратился коллега и спросил, как в визуале slicer настроить фильтр дат таким образом, чтобы выбирая произвольную дату фильровался график и показывал данные на неделю назад с этой датой включительно. Например, выбирая 6-е августа 2009, мы должны видеть на графике период с 31-го июля до 6-го августа 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 - вступайте в эту группу (нужно зарегистрироваться на этом сайте и нажать кнопку Join Group).

    UPD. Добавил файл в библиотеку данного ресурса по ссылке - https://www.pbiusergroup.com/viewdocument/virtual-relationship-example?CommunityKey=06e98987-3abb-4d0a-ad17-7b92901043f2&tab=librarydocuments

    ------------------------------
    Ruslan Zolotukhin
    BI Engineer / Kharkiv Power BI User Group Leader
    ------------------------------