Динамический выбор мер, отображаемых на диаграммах 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”):

NameDatesScore
John01.01.20175
John01.02.20177
John01.03.20176
John01.04.20178
John01.05.20178
John01.06.20175
Jack01.01.20177
Jack01.02.20179
Jack01.03.20178
Jack01.04.20177
Jack01.05.20176
Jack01.06.20175
Jenny01.01.20179
Jenny01.02.201710
Jenny01.03.20176
Jenny01.04.20177
Jenny01.05.20178
Jenny01.06.20179

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

SlicerColumnSlicerOrder
Average Score2
Total Score1

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

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

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, и поместил «скромные» меры в область значений каждой диаграммы:

Two simple charts to test

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

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

Test for Line chart

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

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

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

Test for Bar Chart

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

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

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

Также я очень рекомендую отличный пост в блоге 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() )

 

Share this
Импорт данных в Power Query и Power BI из листа Excel: ловушка UsedRange

Добавить комментарий

Your email address will not be published / Required fields are marked *