Динамический выбор мер, отображаемых на диаграммах Power BI, при помощи среза

Несколько дней назад мой клиент поинтересовался, возможно ли динамически (при помощи среза) изменить набор рядов, отображаемых на диаграмме Power BI. Мой первый (инстинктивный) ответ был “Да, конечно, вы можете использовать срез, чтобы выбрать отображаемые на диаграмме ряды, просто поместите нужный столбец в срез”. Но затем клиент уточнил: выбирать на диаграмме нужно разные меры, а не разные категории из столбца. Второй (не менее инстинктивный) ответ был “Нет, это невозможно. Срез может только фильтровать столбец, и поместить меры в срез нельзя”.

Однако после некоторого обсуждения я засомневался, действительно ли это невозможно. Если мы поместим меру в область значений (“Value”) диаграммы, она будет показана как ряд (например, какая-то мера [Total Amount]). А что на самом деле нужно моему клиенту? Он хочет выбрать какие-то значения на срезе и, если  некоторое значение выбрано, показать соответствующую меру. Если значение не выбрано, то не показывать эту меру.

На самом деле элементы среза это уникальные значения какого-то столбца. Срез применяет фильтр к этому столбцу. Можем мы понять, что столбец отфильтрован? Да, конечно, мы можем это сделать при помощи формул DAX. Если нужное значение выбрано, то мы должны показать меру как ряд на диаграмме. Так как эта мера уже находится в области значений диаграммы, то нам нужно, попросту говоря, “ничего не делать”. Собственно, нам необходимо только как-то спрятать меру, если нужное значение не выбрано на срезе.

Я знаю только один способ “спрятать” значение меры: обычно Power BI (или Power Pivot и простая сводная таблица в Excel) ничего не показывает, если мера возвращает пустое значение (BLANK).

Таким образом, появилось решение:

Если некоторое значение “А” отмечено (выбрано) на срезе, тогда мера вычисляется и показывается как обычно. Если значение “А” не отмечено (не выбрано) на срезе, тогда мера должна возвращать BLANK().

После того, как эта логика прояснилась, её воплощение стало достаточно простым.

Во-первых, нам нужна специальная несвязанная таблица SlicerTable как минимум с одним столбцом [SlicerColumn], и [RawMeasure], которая на самом деле делает все необходимые вычисления. Затем мы сделаем новую (я ее беззастенчиво называл “скромной”) меру, которая будет себя вести как описано выше.

Вот один из возможных шаблонов для “скромных” мер:

Shy Measure for Slicing on a Chart =
IF (
    COUNTROWS (
        CALCULATETABLE (
            SlicerTable,
            SlicerTable[SlicerColumn] = "Красивое название меры"
        )
    )
        > 0,
    [Raw Measure],
    BLANK ()
)

Shy Measure for Slicing on a Chart =
IF (
    CONTAINS ( 'SlicerTable', SlicerTable[SlicerColumn], "Красивое название меры" ),
    [Raw Measure],
    BLANK ()
)

Для тестирования этих мер я создал в Power BI Desktop простую таблицу с уникальным именем ‘Table1’ (даты в формате ДД.ММ.ГГГГ) при помощи кнопки “Введите данные” (“Enter Data”):

Затем аналогично создал еще одну таблицу ‘SlicerTable’:

Связь между таблицами устанавливать не нужно!

После загрузки этих таблиц в модель данных, я создал две простые “сырые” (базовые) меры, которые считают какие-то показатели:

AvgScore =
AVERAGE ( Table1[Score] )

TotScore =
SUM ( Table1[Score] )

Теперь нам нужно создать “скромные” версии каждой из этих мер:

Average Score =
IF (
    CONTAINS ( 'SlicerTable', SlicerTable[SlicerColumn], "Average Score" ),
    [AvgScore],
    BLANK ()
)

Total Score =
IF (
    CONTAINS ( 'SlicerTable', SlicerTable[SlicerColumn], "Total Score" ),
    [TotScore],
    BLANK ()
)

Давайте добавим несколько диаграмм и срез.

Я выбрал для теста простую гистограмму (Bar chart), поместив поле Names в ось X, а также линейный график (“Line chart”) с датами на оси X, и поместил “скромные” меры в область значений каждой диаграммы:

Две простые диаграммы для теста

Всё, что нам осталось сделать – создать срез по столбцу [SlicerColumn] из таблицы ‘SlicerTable’ и протестировать:

Тест линейной диаграммы

Получилось! Наша новая мера исчезает, если соответствующее значение не отмечено на срезе, и появляется, если отмечено.

Такое же поведение демонстрируют и другие диаграммы:

Тест гистограммы (линейчатой диаграммы)

Видите, как просто :).

Единственная проблема, которую я обнаружил, это странное поведение легенды на линейной диаграмме (графике). Если мы выбираем на срезе одно значение, соответствующее мере, стоящей первой в области значений диаграммы, то легенда таинственным образом исчезает с визуального элемента, и нет никакой возможности вернуть ее назад (параметры легенды в настройке диаграммы также не отображаются). Но если выбрать другие значения среза, все значения или не выбирать ни одного, то легенда возвращается. Я думаю, что за счет правильного расположения среза близко к диаграмме и отключения отображения легенды можно решить эту проблему.

Также я очень рекомендую отличный пост в блоге Power BI Community (автор Sam McKay) “Dynamically change the information within a visual via a slicer”, (Динамическое изменение информации на визуальном элементе при помощи среза), описывающий способ переключения на диаграмме одной показываемой меры на другую. Этот способ хорош, когда мера на диаграмме только одна, но может быть несколько рядов, относящихся к разным категориям данных. Сэм описывает метод, при помощи которого можно изменить меру при помощи среза. Я обнаружил этот пост после того, как сделал собственное решение этой задачи. Мое решение аналогично опубликованному Сэмом, поэтому я не показываю его тут. Не изобретайте велосипед вслед за мной, гуглите “до”, а не “после”.

Код DAX в этом посте “сделан красиво” при помощи замечательного и восхитительного сервиса www.daxformatter.com

Как это работает

(этого раздела пока нет в английской версии)

Срез представляет собой таблицу из одного столбца, которая может быть определена следующей формулой DAX:

VALUES ( SlicerTable[SlicerColumn] )

Когда мы ставим “галочку” на срезе, мы применяем фильтр к указанному в срезе столбцу. Если выбрано одно значение, то применяется фильтр:

SlicerTable[SlicerColumn] =“А”.

Если выбрано несколько значений, то отдельные фильтры сочетаются по принципу “ИЛИ”. Если выбраны все значения, то это равнозначно тому, что никакой фильтр не применен. После применения фильтра из таблицы исчезают значения, не указанные нами напрямую.

Для того, чтобы проверить, выбрано ли значение в срезе, нам нужно убедиться, что мы его “видим”, то есть оно осталось в отфильтрованной таблице. Для этого применяем функцию CONTAINS (переводится как “содержит”):

=
CONTAINS ( ‘SlicerTable’, SlicerTable[SlicerColumn], “Красивое название меры” )

Она проверяет, содержит ли  в указанной таблице указанный столбец конкретное значение, и возвращает TRUE или FALSE (ИСТИНА или ЛОЖЬ).

Соответственно логика “скромной” меры очень проста:

= ЕСЛИ (СОДЕРЖИТ(ТаблицаСреза, ТаблицаСреза[СтолбецСреза], “Красивое название меры”), [Базовая мера], BLANK() )