Полевые работы – field parameters в Power BI

2151

И снова здравствуйте!

В этой статье речь пойдёт о нововведении Microsoft Power BI – “field parameters”. Для чего их вводили.

Наверняка, вы видели ситуации, когда кликая на слайсер, какое-то волшебство меняло графики: то у нас сумма показывается в разрезе месяцев, а потом в разрезе сотрудников, а можно и по цветам товара посмотреть; а бывало и такое, что сумма, а потом количество, а потом средняя. 

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

  1. генерации таблицы в Power Query или функциями CrossJoin и Union;
  2. обучение персонала делать это на свой вкус через облако и функционал персонализации визуалов;
  3. откровенная х… халтура в виде закладок.

Всё подробно описано в первом и во втором  видео на канале у “Йоооу” парней.
Замена же самого вычисления совершалась либо через те же закладки, либо более приемлемыми способами, например через SWITCH и CALCULATION GROUPS. Информации по этой теме также предостаточно. Например, в нашем блоге и в блоге Ильи Шелегина: первая и вторая статьи.

Однако, после июньского обновления Power BI, про все эти танцы с бубном трудоёмкие пути можно забыть. Появился функционал полей параметров.
Вдаваться в подробности в статье я не буду, это уже сделано до меня, например, “итальянцами“.

Вместо этого я решил, описать свой пользовательский опыт, связанный с обновлением шаблонного отчета по сделкам amoCRM, созданный на базовой выгрузке сервиса mybi Connect который ранее я делал для myBI Market. Код нового функционала более оптимизирован (даже появилась новая функция NAMEOF ). Кроме того, форматирование мер при замене вычисления через параметры поля не теряется, что позволяло избежать еще больших строчек кода в CALCULATION GROUPS.

Однако Microsoft не был бы Microsoft, если бы не было проблем 🙂 Изюминка терялась бы. Какие проблемы увидел я. Первое – теряется условное форматирование в таблицах и матрицах при переключении, а также ширина столбцов. Проблема решаема. Нужно переключать в  слайсере, настраивать условное форматирование и ширину столбцов для каждого варианта. Если вам не лень, можно в написать код через IF и SELECTEDMEASURE. Но последний способ неоправданно трудоёмкий. Подробнее тут.

Чуть позже я обнаружил ещё одну задачу, которая требовала решения. В отчете на странице “сделки” срез управляет сразу 5 карточками, гистограммой и барчартом. Сделать это несложно: 5 мер через SWITCH, и все ориентированы на 1 столбец таблицы, которая и помещена в срез. Чтобы кликать, вставим здесь сам отчёт (для масштабирования нажмите прямоугольник в нижнем правом углу):

В общем, требовалось найти решение. 

И, как разработчик моделей в первую очередь, я начал ориентироваться на улучшение модели данных, а не на фильтрацию через меры. Способов я нашёл целых 3. Давайте подробнее о них расскажу.

Первый основан на моей привычке. Я весьма строго называю меры. Вот как они выглядят:

Оставалось подыскать функцию, которая поможет реализовать вычисляемый столбец, по которому это можно было ими управлять через срез. Собственно, такой функцией является CONTAINSSTRING. Создал столбец, записал функцию, получил TRUE(). Обернул в SWITCH и получил:

Статус сделки или конверсия =
VAR a = [Выбранный период]
RETURN
    SWITCH (
        TRUE (),
        CONTAINSSTRING ( a"активных" )"Активные",
        CONTAINSSTRING ( a"новых" )"Новые",
        CONTAINSSTRING ( a"потерянных" )"Потерянные",
        CONTAINSSTRING ( a"успешных" )"Успешные",
        CONTAINSSTRING ( a"Конверсия" )"Конверсия"
    )

Аналогичная операция для управления типом параметра, и вот у меня уже полноценная таблица. 

Повторяю операции для двух оставшихся полей параметров, созданных для сравнения с выбранным периодом. 

Следующий этап – создание управляющих таблиц. Здесь, обычным VALUES создаём таблицы. Прокладываем связи:

Столбец таблицы я заложил в срез, а в визуал дополнительно наложил фильтр. Заработало.

Способ второй.  

Он основан на использовании стола столбца заказов. Понимание, того, что перевод является машинным, наводит меня на мысль, что в оригинале столбец называется order, что означает “порядок”.

Как выглядит код в данном решении:

Статус сделки или конверсия =
VAR a = [Заказ Выбранный период]
RETURN
    SWITCH (
        TRUE (),
        a IN { 0711 }, "Активные",
        a IN { 1812 }, "Новые",
        a IN { 2913 }, "Потерянные",
        a IN { 31014 }, "Успешные",
        a IN { 456 }, "Конверсия"
    )

Если вы не хотите использовать функцию TRUE() в условии SWITCH как один из антипаттернов, то всегда можно написать вот так:

Статус сделки или конверсия =
VAR a = [Заказ Выбранный период]
RETURN
    SWITCH (
        a,
        0"Активные",
        1"Новые",
        2"Потерянные",
        3"Успешные",
        4"Конверсия",
        5"Конверсия",
        6"Конверсия",
        7"Активные",
        8"Новые",
        9"Потерянные",
        10"Успешные",
        11"Активные",
        12"Новые",
        13"Потерянные",
        14"Успешные"
    )

Аналогично создаем второй столбец и дублируем эту операции в других полях параметров. Связи таблиц выглядят так же. 

Ну и последний способ я описывать подробно не буду, так как он очень похож на второй. Мы сразу создаем таблицу управляющие таблицы. Сделать можно через DATATABLE, или связку ADDCOLUMNS и VALUES:

Таблица =
ADDCOLUMNS (
    VALUES ( 'Выбранный период'[Заказ Выбранный период] ),
    "Значение",
        SWITCH (
            'Выбранный период'[Заказ Выбранный период],
            0"Активные",
            1"Новые",
            2"Потерянные",
            3"Успешные",
            4"Конверсия",
            5"Конверсия",
            6"Конверсия",
            7"Активные",
            8"Новые",
            9"Потерянные",
            10"Успешные",
            11"Активные",
            12"Новые",
            13"Потерянные",
            14"Успешные"
        )
)

Аналогично делается и вторая таблица. Связь выглядит так:

Обращаю внимание: связи автоматически проставляются как 1 к 1 двусторонними. С точки зрения содержащихся данных, это нормально, а вот с точки зрения модели – недопустимо. Тут берём инструмент “мышка” и меняем на те, которые положены (см скриншот). Добавлю, у вас и выхода-то не будет. После 1 связи, вторая сразу будет неактивной, а третья не установится, так как обнаружит циклическую зависимость. В общем, куда вы денетесь с подводной лодки – 1 ко многим и будет счастье.

Выводы, куда же без них 😉 В целом, любой способ жизнеспособен, но они разные. Отличаются в первую очередь гибкостью. Если функция CONTAINSSTRING автоматически распознает новые добавленные меры в поле параметров и обновит все управляющие таблицы, то самописные столбцы и таблицы этого делать не будут и придется дополнять руками. Хотя использование CONTAINSSTRING предполагает наличие определенной культуры (звучит гордо) обозначения мер. Опять же, если этих параметров много, то 1 функция быстрее сделает работу, нежели заполнение SWITCH для каждого случая самостоятельно. И в тоже время, если строк мало, то это будет стрельбой из пушек по воробьям. Выбирайте самостоятельно, исходя из своих целей и задач.

А обновлённую версию отчета можно ожидать уже скоро. В нём также будет добавлен анализ звонков и задач, чтобы вам не приходилось покупать 2 отчёта, а все задачи можно будет решить в одном.