Как выделить наибольшее / наименьшее значение в каждой строке или столбце?

DAX функции MAX, MAXA, MAXX и MIN, MINA, MINX (для Power BI и Power Pivot)

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье разберем несколько DAX функций в Power BI и Power Pivot, входящих в категорию статистических (агрегационных), а именно, функции MAX, MAXA, MAXX и MIN, MINA, MINX.

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

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

DAX функция MAX в Power BI и Power Pivot

DAX функция MAX () — возвращает максимум среди числовых значений. MAX работает в двух режимах и эту особенность необходимо всегда учитывать в ее работе. Режимы работы зависят от вида синтаксиса.

• MAX ([Столбец]) — возвращает максимум среди значений столбца (особенность — не учитывает контекст строки)
• MAX (Выражение 1; Выражение 2) — возвращает максимум среди значений из двух параметров функции. Если работа производится в таблице, то MAX работает построчно, вычисляя максимум в рамках только одной строки (особенность — учитывает контекст строки)

Пример работы формулы в Power BI на основе функции MAX: У вас есть исходная таблица с числовыми значениями в двух колонках.

Добавим в Power BI Desktop во вкладке «Моделирование» третий вычисляемый столбец на основе следующей DAX формулы с использованием MAX:

Если предположить, что формула MAX записана с использованием синтаксиса первого типа, то максимум вычисляется среди значений всего столбца, в данном примере по [Column1], и это значение равно 10. Эта формула будет вычисляться независимо от контекста строки, поэтому в нашем вычисляемом столбце результат функции MAX будет одинаковым во всех ячейках:

Если, с другой стороны, мы изменим формулу и запишем ее, используя второй тип синтаксиса MAX:

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

DAX функция MAXA в Power BI и Power Pivot

MAXA () — вычисляет максимум с обработкой пустых ячеек и ячеек, содержащих логический тип данных. Причем TRUE считается как 1, а FALSE — как 0.

Синтаксис: MAXA ([Столбец])

Пример: Имеется исходная таблица, в которой есть столбец, содержащий логические величины.

Если мы попытаемся вычислить максимум для этого столбца с помощью функции MAX, мы получим ошибку, потому что MAX не работает с логическими значениями

Но, если мы исправим формулу и пропишем там MAXA, которая учитывает логические значения, то максимум рассчитается, и он будет равен 1, так как MAXA считает TRUE, равным 1, а FALSE — равным 0:

DAX функция MAXX в Power BI и Power Pivot

MAXX () — выводит максимальное значение из результатов построчного вычисления выражения.

В матрице MAXX (‘Таблица’; Выражение), где :

• ‘Array’ — исходный массив или выражение массива, строки которого будут использованы для вычисления выражения для второго параметра функции
• Expression — любое выражение, которое будет выполнено над строками массива, включенными в первый параметр функции.

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

Пример: исходная таблица с одним столбцом числовых данных

Давайте запишем следующую формулу:

Для начала MAXX вычислит выражение «[Столбец1] + 2» по каждой строке таблицы, то есть к значению каждой строки [Столбец1] прибавит 2, и только после этого, на основе получившихся результатов, вычислит максимум, который будет равен 5:

DAX функции MIN, MINA, MINX в Power BI и Power Pivot

Функции DAX MIN (), MINA () и MINX () работают точно так же, как и соответствующие функции максимума, но они вычисляют наименьшие значения вместо наибольших. Поэтому я рекомендую перед работой с функциями группы MAX прочитать вышеизложенный материал, содержащий обзор и примеры функций группы MAX.

1. MIN () — вычисляет минимум среди числовых значений. Также, как и функция MAX, MIN работает в двух режимах, зависящих от вида синтаксиса.Синтаксис:
   • MIN ([Столбец]) — возвращает минимум среди значений столбца (не учитывает контекст строки)
   • MIN (Выражение 1; Выражение 2) — возвращает минимум среди значений из двух параметров функции. Если работа производится в таблице, то MIN работает построчно, вычисляя минимум в рамках только одной строки (учитывает контекст строки)
2. MINA () — возвращает минимум с обработкой пустых ячеек и ячеек, содержащих логический тип данных. TRUE считается как 1, а FALSE — как 0.

Примеры формул DAX на основе функций MIN не будут рассматриваться в Power BI или Excel (Powerpivot), поскольку они идентичны примерам, рассмотренным выше для функций MAX, за исключением того, что в данном случае вместо максимумов вычисляются минимумы.

Группы MAX, MIN и другие функции DAX (FILTER, DATE…)

Поскольку функции агрегирования являются одними из самых простых функций DAX, они редко используются только в формулах. DXF — это функция, которая используется в сочетании с другими функциями, такими как FILTER, CALCULATE, DATE и другими.

Например, MAXX может вычислить максимум из результатов выражения на основе массива, отфильтрованного функцией ФИЛЬТР.

Рассмотрим пример: есть исходная таблица заказов на товары

Максимальная прибыль заказа должна быть рассчитана только для продукта 1. MAXX является хорошим решением этой проблемы; он сначала вычисляет прибыль для каждой строки в таблице, а затем выводит максимальное значение прибыли. Тем временем FILTER отфильтрует исходную таблицу, оставив только строки с продуктом 1.

Подробнее о функции FILTER вы можете прочитать в статье нашего блога.

Формула будет выглядеть следующим образом:

И, в результате этой формулы, максимальная прибыль по заявкам среди Продукта 1 будет равна 35000:

Обратная ситуация может возникнуть и с более сложным кодом формулы DAX, когда MAX встраивается в FILTER в качестве условия фильтрации, а FILTER встраивается в функцию CALCULATE в качестве одного из параметров фильтрации.

В этом случае MAX выведет максимальное значение строки исходной таблицы, а FILTER сравнит это значение с текущим значением своей внутренней виртуальной таблицы.

Схема этого кода приведена ниже:

В этой статье мы описали группу функций максимума и минимума, присутствующих в Power BI и Power Pivot. Удачи вам!

Подробное ВИДЕО «MAX, MAXA, MAXX, MIN, MINA, MINX: DAX функции максимумов и минимумов в Power BI (Pivot)»

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

Обратите внимание на статью

1. 5
2. 4
3. 3
4. 2
5. 1

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

Если у вас есть вопросы по этой статье, пожалуйста, оставляйте их в разделе комментариев ниже. Я обязательно отвечу вам. И вообще, просто оставляйте там свои отзывы, я буду очень рад.

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

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

Что еще посмотреть / почитать?

Ограничение таблиц из дат в Power BI и Power Pivot: DAX функции DATESBETWEEN и DATESINPERIOD

DAX функции YEARFRAC, WEEKDAY и WEEKNUM в Power BI и PowerPivot

Функции для создания перечислимых колонок и таблиц в DAX: ADDCOLUMNS и DATATABLE в Power BI и Power Pivot.

Максимальное и минимальное

Здесь вы можете посмотреть, как найти максимальное значение в Excel:

1. Поместите маркер клетки в любое место.
2. Перейдите в меню Формулы.
3. Нажмите кнопку Вставить функцию.
4. Выберите «MAX» из списка. Или введите слово в поле «Поиск» и нажмите «Найти».
5. В поле «Аргументы» введите адреса диапазонов, максимальное значение которых вы хотите узнать. В Excel имена ячеек состоят из буквы и цифры («B1», «F15», «W34»). Имя диапазона — это первая и последняя ячейка в нем.
6. Вместо адреса можно ввести больше цифр. После этого система отобразит наибольшее число.
7. Нажмите кнопку «OK». Результат отображается в ячейке, где находился курсор.

Следующий шаг — укажите диапазон значений

Теперь вам будет легче понять, как найти минимальное значение в Excel. Алгоритм полностью идентичен. Для этого достаточно выбрать «MIN» вместо «MAX».