Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

• Создание таблицы ТОП-2 в модели данных на основе функции TOPN
• Создаем в Power BI отчет ТОП-2, используя меру
• Общая структура фильтрующей меры ТОП-2
• Алгоритм работы DAX функций в мере, фильтрующей визуализацию Power BI по значениям ТОП
• Подробное ВИДЕО «Как в Power BI рассчитать таблицу ТОП-3 не через вычисляемую таблицу, а через меру?»

---

Приветствую Вас, дорогие друзья, с Вами Будуев Антон.

В данной статье мы разберем решение достаточно распространенной проблемы для пользователей DAX в Power BI: как вывести любое значение ТОП, например, ТОП-3 не через таблицу в модели данных, а через меру.

Мы с вами прекрасно знаем функцию TOPN в DAX. Но, проблема заключается в том, что эта функция возвращает таблицу, а таблицу мы не можем вставить в меру.

Поэтому, для расчета ТОП нам приходится создавать таблицы в модели данных. Но, создавая таблицы в модели данных, мы загружаем оперативную память компьютера. Следовательно, чем больше мы создаем вычисляемых столбцов и таблиц в модели данных, тем ниже становится быстродействие компьютера и медленнее работают отчеты. Поэтому желательно по максимуму переносить все расчеты в меры.

Возникает вопрос. А как перенести таблицу, получаемую на выходе функции TOPN, в меру? Решению этого вопроса и посвящена текущая статья.

В разбираемой формуле нам понадобятся следующие DAX функции: IF, SUMX, TOPN, SEARCH.

Совет: скачайте себе «Справочник DAX функций для Power BI и Power Pivot» в PDF формате, чтобы подробное описание DAX функций и примеров формул у Вас всегда было под рукой.

А также, скачайте файлы к данной статье (для Power BI)

Да, и еще один момент, в рамках распродажи до 30 апреля 2024 г. у Вас имеется возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 50% (вместо 10000, всего за 5000 руб.)

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

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 50% (до 30 апреля 2024 г.): узнать подробнее

до конца распродажи осталось:

 

Создание таблицы ТОП-2 в модели данных на основе функции TOPN

Рассмотрим таблицу, которая является источником данных в Power BI для текущей задачи:

 
Исходная таблица содержит строки с продажами по менеджерам. Итак, давайте на ее основе с помощью DAX функции TOPN создадим новую таблицу с отображением ТОП-2 менеджеров по продажам. Для начала рассчитаем ее в модели данных, а затем уже перейдем к построению ТОП через меру.

DAX код будет следующим:

ТОП_2 =
TOPN(
    2;
    'ПродажиМенеджеров';
    'ПродажиМенеджеров'[Продажи];
    DESC
)

В первом параметре функции TOPN мы прописали значение ТОП, которое нужно в итоге получить (в нашем примере это 2).

Далее, во втором параметре прописали название таблицы, по которой необходимо рассчитать ТОП (в нашем примере это таблица ‘ПродажиМенеджеров’).

В третьем параметре — название столбца таблицы, по которому будет рассчитываться ТОП (в нашей формуле это столбец [Продажи]).

Ну и в четвертом параметре прописали метод сортировки значений (в нашем примере сортировка необходима «от большего к меньшему» — а значит нужно подставить DESC).

В итоге на основе этой DAX формулы получили следующую таблицу из двух строк ТОП-2:

 
Хорошо, ТОП-2 мы рассчитали. Но, вопрос, как получить значения этих строк, не создавая такой таблицы в модели данных и не задействуя оперативную память ПК? А произвести все расчеты через меру?

На самом деле этот вопрос решается достаточно просто!
 

Создаем в Power BI отчет ТОП-2, используя меру

Итак, для демонстрации у меня в Power BI уже подготовлен отчет, который определяет ТОП-2 менеджера на основании их продаж без создания вычисляемой таблицы в модели данных. Выглядит этот отчет, как обычная таблица из области отчета Power BI, с размещением в этой таблице столбцов «Менеджер», «Отдел» и «Продажи» из исходной таблицы:

 
Но несмотря на то, что эта обычая таблица, данный отчет действительно показывает только ТОП-2 менеджеров, а не всех. И стало это возможным благодаря тому, что в фильтрах данной визуализации используется фильтр на основе созданной заранее мною меры [МераФильтр]. Именно эта мера и фильтрует всю визуализацию нужным нам способом:

 

Давайте рассмотрим работу этой меры.
 

Общая структура фильтрующей меры ТОП-2

Итак, DAX формула меры, которая фильтрует визуализацию Power BI следующий:

МераФильтр =
IF(
    SUMX(
        TOPN(
            2;
            ALLSELECTED('ПродажиМенеджеров');
            'ПродажиМенеджеров'[Продажи];
            DESC
        );        SEARCH('ПродажиМенеджеров'[Менеджер];MAX('ПродажиМенеджеров'[Менеджер]);;0)
    )>0;
    "да";
    "нет"
)

Мера представляет собой логическую функцию IF, в условии которой, возвращаемое значение функцией SUMX должно быть строго больше 0. SUMX, в свою очередь, суммирует значения, являющиеся результатом последовательных вычислений функции SEARCH по каждой перебираемой строке таблицы ТОП, которую возвращает функция TOPN в первом параметре SUMX.

 
В итоге, вся эта мера каждому значению столбца [Менеджер] исходной таблицы присваивает значение «да», если менеджер в этой строке входит в ТОП, и присваивает «нет», если в ТОП не входит.

Но, мы не видим «да» и «нет» в таблице отчета! Да, правильно, фишка в том и заключается, что эти значения нам нужны только для фильтра визуализации, а в самой визуализации данная мера не нужна.

 
Устанавливая фильтр «является да», мы оставляем в отчете только тех менеджеров, которые входят в ТОП-2. Если же мы удалим этот фильтр, то в отчете отобразится полная исходная таблица.

 
Если добавить меру в отчет, то мы увидим эти значения меры.

 
Согласитесь, что информативности в отчет этот столбец не добавляет, поэтому мы его не будем включать в отчёт.

Если же мы в коде меры изменим первый параметр функции TOPN на «3»:

 
И установим фильтр визуализации отчета на «является да», а также отсортируем столбец Продажи «от большего к меньшему»:

 
То получим отчет ТОП-3, в котором видно, что лучший по продажам менеджер из ТОП-3 – это Соколова!

 

Теперь же давайте подробнее разберемся в том, как работает весь DAX код в нашей фильтрующей мере.
 

Алгоритм работы DAX функций в мере, фильтрующей визуализацию Power BI по значениям ТОП

Итак, функция TOPN нам возвращает таблицу ‘ПродажиМенеджеров’, в которой всего 3 строки, соответствующие значениям ТОП-3 менеджеров по продажам:

 
Данная возвращаемая таблица, встроена в функцию SUMX, которая является итерационной. То есть, SUMX по каждой строке таблицы, указанной в первом параметре (возвращаемая таблица ТОП-3), вычисляет свой второй параметр, в котором находится функция SEARCH.

Кратко разберем как работает DAX функция SEARCH на нашем примере:

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

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

Важно понимать, какие значения будут находиться в первом и втором параметре функции SEARCH.

Первый параметр – это столбец, следовательно на него будет действовать контекст строки, который создаст функция SUMX. Контекстом строки в данном случае будут являться перебираемые строки с ТОП-3 менеджерами.

Во втором параметре функции SEARCH размещена агрегационная DAX функция MAX. А агрегационные функции не видят контекста строки. Следовательно функция MAX вообще не видит того, что поступает от первого параметра функции SUMX в рамках контекста строки. Но MAX видит контекст пользовательского фильтра, поступающий от рассчитываемой визуализации:

 
Например, при расчете меры для первой строки менеджер «Соколова» является внешним контекстом фильтра для данной меры. Этот внешний контекст фильтра функция MAX видит и поэтому данный столбец фильтруется по менеджеру «Соколова».

То есть, в итоге, функция MAX при расчете 1 строки в визуализации будет возвращать во второй параметр SEARCH значение «Соколова». А значит SEARCH будет в своем первом параметре искать именно это значение.

Теперь посмотрим на первый параметр SEARCH, что находится там. Там, как мы это уже разбирали ранее, в рамках контекста строки SUMX, на каждой итерации будет находиться один из менеджеров ТОП-3.

Давайте рассмотрим все 3 итерации по шагам.

На 1 итерации SUMX из своего первого параметра (из таблицы ТОП-3) возьмет менеджера «Соколова», так как именно это значение и является первым значением в столбце, который перебирает SUMX.

В результате функция SEARCH найдет соответствие и возвратит положительное число (номер первого найденного символа в искомом тексте):

 
На второй итерации в контексте строки SUMX будет находиться значение «Сидоров». Соответственно, в первом параметре функции SEARCH будет значение «Сидоров».

Но во втором параметре SEARCH ведь по-прежнему будет значение «Соколова», так как сейчас мы рассматриваем пример расчета меры для первой строки визуализации в Power BI.

В итоге, на второй итерации SEARCH не найдет соответствия и возвратит 0:

 
Ну и на третьей итерации в контексте строки SUMX будет находиться последний менеджер из ТОП-3 – «Василькова». Соответственно, SEARCH опять-таки не найдет значения «Соколова» в столбце со значением «Василькова» и возвратит 0.

 
Все итерации SUMX закончены и на последней своей операции SUMX произведет сумму результатов всех вышеперечисленных вычислений:

 
Заключительную операцию по расчету меры для первой строки визуализации в Power BI выполнит функция IF, на вход которой придет значение от SUMX (в нашем случае 1). А значит выполнится условие в IF “>0” и IF возвратит значение «да»:

 
В общем итоге работы всей меры для первой строки визуализации в Power BI, будет возвращено значение «да»:

 
Точно такие же результаты мера выдаст для менеджеров «Сидоров» и «Василькова»:

 

 
А вот для Смирнова и Колесникова результаты будут иными:

 

 
Так как что для Колесникова, что для Смирнова во всех трех итерациях SUMX в первом параметре SEARCH будут находиться менеджеры из ТОП-3 (Соколова, Сидоров и Василькова), а во втором параметре в первом случае Смирнов и во втором случае Колесников.

Ну и естественно, SEARCH не сможет найти ни Смирнова, ни Колесникова среди значений Соколова, Сидоров и Василькова.

А раз SEARCH не найдет этих значений, то возвратит 0 на всех 3 итерациях SUMX. В результате SUMX произведет сумму этих нулей и возвратит опять-таки 0.

Ну и функция IF, а соответственно, и вся мера, возвратит значение «Нет», так как условие в IF не будет выполнено.

В общем итоге в нашей визуализации мы получим на против каждого менеджера значения «Да», удовлетворяющие ТОП-3 и значения «Нет»:

 
Нам всего лишь остается разместить эту меру не в составе столбцов визуализации, а в составе фильтров к этой визуализации, ну и назначить фильтр по этой мере «Является Да». И как результат, визуализация всегда будет нам показывать только тех менеджеров, которые являются ТОП-3 по продажам:

 
Вот такой нехитрый способ фильтрации визуализаций при помощи мер. Мы создали с Вами меру, которая не участвует в столбцах отчета Power BI, но участвует в фильтрах визуализации.

Теперь Вам не нужно создавать таблицы в модели данных при расчете TOPN. Всё это можно сделать в мере очень простым способом.
 

Подробное ВИДЕО «Как в Power BI рассчитать таблицу ТОП-3 не через вычисляемую таблицу, а через меру?

Ссылки из видео:
1) [Скачивайте] Файлы к уроку для Power BI: скачать
2) [Скачивайте PDF] Справочник DAX функций для Power BI и Power Pivot на русском языке: скачать

Также, напоминаю Вам, в рамках распродажи до 30 апреля 2024 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 50% (вместо 10000, всего за 5000 руб.)

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

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 50% (до 30 апреля 2024 г.): узнать подробнее

До конца распродажи осталось:


 
 
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
 
 
 
 

Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
 
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.

 
Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

до конца распродажи осталось: