Как выделить наибольшее / наименьшее значение в каждой строке или столбце?
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.
- MIN () — вычисляет минимум среди числовых значений. Также, как и функция MAX, MIN работает в двух режимах, зависящих от вида синтаксиса.Синтаксис:
- MIN ([Столбец]) — возвращает минимум среди значений столбца (не учитывает контекст строки)
- MIN (Выражение 1; Выражение 2) — возвращает минимум среди значений из двух параметров функции. Если работа производится в таблице, то MIN работает построчно, вычисляя минимум в рамках только одной строки (учитывает контекст строки)
- 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 на русском языке: скачать
Обратите внимание на статью
- 5
- 4
- 3
- 2
- 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:
- Поместите маркер клетки в любое место.
- Перейдите в меню Формулы.
- Нажмите кнопку Вставить функцию.
- Выберите «MAX» из списка. Или введите слово в поле «Поиск» и нажмите «Найти».
- В поле «Аргументы» введите адреса диапазонов, максимальное значение которых вы хотите узнать. В Excel имена ячеек состоят из буквы и цифры («B1», «F15», «W34»). Имя диапазона — это первая и последняя ячейка в нем.
- Вместо адреса можно ввести больше цифр. После этого система отобразит наибольшее число.
- Нажмите кнопку «OK». Результат отображается в ячейке, где находился курсор.
Следующий шаг — укажите диапазон значений
Теперь вам будет легче понять, как найти минимальное значение в Excel. Алгоритм полностью идентичен. Для этого достаточно выбрать «MIN» вместо «MAX».