DAX-формулы или Data Analysis Expressions — выражения для анализа данных в Microsoft Power BI, в Analysis Services и Power Pivot в Excel. DAX-формулы позволяют, по аналогии с формулами Excel, выполнять вычисления и настраивать произвольную фильтрацию и представление данных в таблицах.
Язык DAX есть в следующих приложениях:
Power BI Desktop
Power Pivot в Excel
Visual Studio
SQL Server Management Studio
DAX Studio
Tabular Editor
Впервые DAX-формулы появились в Excel 2010 года во внешней надстройке Power Pivot. С тех пор этот язык становится все более популярным, и если раньше о DAX слышали только единицы, то сейчас он широко применяется в бизнес-аналитике и проектировании моделей. Поэтому DAX-формулы вам точно пригодятся для продвинутого анализа.
В этой статье описание DAX приводится для Power Pivot в Excel и Power BI.
Строение DAX-формул
DAX-формулы очень похожи на обычные формулы Excel. Многие из них записываются одинаково, например, «сумма»-SUM и «если»-IF. Но сами вычисления работают по-разному: в отличие от обычного Excel, в языке DAX нет расчетов по ячейкам. DAX-формулы обращаются сразу к таблицам и столбцам целиком. Примерно похожий способ вычислений есть и в «обычном» Excel – с помощью формул массивов. И если вы работали с форматированными smart-таблицами, то чтобы лучше понять DAX, вспомните, как в них выглядят ссылки – название столбца в квадратных скобках.
В DAX-формулах почти также: названия таблиц обычно пишут в одинарных кавычках. Или без кавычек, если имя таблицы написано латинскими буквами без пробелов и цифр в начале. Названия столбцов пишут в квадратных скобках:
'Имя таблицы'[Название столбца] или TableName[Название столбца]
Математические операторы & + — / * = > < () и их сочетания дают тот же эффект, что в Excel.
Логические операторы:
- && — аналог формулы И (AND)
- || — аналог ИЛИ (OR)
- IN – поиск элемента в списке
- NOT – логическое отрицание, аналог формулы НЕ.
Вычисляемые столбцы, меры и таблицы
С помощью DAX-формул в Power BI можно создавать вычисляемые столбцы, меры, вычисляемые таблицы. В Excel есть только вычисляемые столбцы и меры.
Понятия столбцов и мер – основы работы с DAX. Давайте разберемся, что это такое.
1. Вычисляемые столбцы
Вычисляемый столбец – это столбец, который добавляется в существующую таблицу, а DAX-формула определяет значения этого столбца.
Как и обычные столбцы в модели данных, вычисляемые столбцы можно использовать в других вычислениях. А также для создания связей между таблицами, для построения визуализаций и срезов. В сводных таблицах вычисляемые столбцы можно помещать в области фильтров, колонок, строк и значений.
Если данные в вашем файле загружаются в режиме импорта, то столбец рассчитывается и записывается в файл при загрузке и обновлении данных, увеличивая размер файла. Вычисляемые столбцы лучше использовать, когда нужен текст, дата или когда вычисление зависит от соседних колонок.
Вычисляемые столбцы создаются просто, как в Power Pivot, так и в Power BI: добавляется новый столбец, пишется «равно» и формула.
в Power Pivot
в Power BI
Чтобы обратиться к вычисляемому столбцу в других вычислениях, нужно написать имя таблицы, в которой он находится, и название самого столбца. Например, 'Таблица'[Столбец]
2. Меры
Меры – это динамические вычисления, результаты которых рассчитываются в зависимости от контекста. Результат вычисления меры можно увидеть в отчете, где мы задаем в каком именно контексте (в разрезе каких полей, фильтров и др.) нужно посчитать меру.
Как создать меру:
- В Excel меры записывают в окне Power Pivot в области для вычислений под таблицей: выберите ячейку, введите название меры и знак :=
Или в меню Power Pivot → Меры → Создать меру. - Чтобы создать меру в Power BI, выберите в меню Моделирование → Создать меру (или нажать правой кнопкой мышки в области полей по таблице → Создать меру).
в Power Pivot
в Power BI
При создании мер нужно обязательно использовать агрегирующие функции, например суммирования SUM. Мера не может быть создана просто как обращение к столбцу таблицы:
- Так не работает: Прибыль:= 'Данные'[Выручка] — 'Данные'[Расходы]
+ Так работает: Прибыль:= SUM ( 'Данные'[Выручка] ) – SUM ( 'Данные'[Расходы] )
Меры лучше создавать, когда нужны числовые вычисления, например, для промежуточных итогов, вычисления процентов, доли продукта в группе и так далее. Меры можно использовать для вычисления других мер и столбцов. При оформлении отчетов и сводных таблиц меры добавляются только в область значений.
Чтобы использовать меру в других вычислениях, ее название пишут в квадратных скобках. Пример:
МераВ = [МераА] + 100
Примечание о записи формул.
1. В Power BI формулы записывают с помощью знака равно = и разделителей-запятых. Пример:
Мера = IF ( [Показатель] > 100, [МераA], [МераB] )
2. В Power Pivot в Excel разделителем в формулах может быть запятая «,» или точка с запятой «;» в зависимости от региональных настроек.
Вычисляемые столбцы записывают с помощью знака =
При создании меры пишут её название и знак :=
Мера:= IF ( [Показатель] > 100; [МераA]; [МераB] )
Базовые DAX-формулы
В языке DAX существует множество формул или функций, позволяющих выполнять продвинутые аналитические вычисления. Эти функции относятся к разным группам — агрегирующие, логические, математические, для работы с текстом, со временем и др. Полный список функций можно посмотреть на сайте Microsoft. Для начала разберем наиболее часто встречающиеся (на наш взгляд) формулы.
1. SUM
SUM суммирует числа в столбце. Её аналог в Excel – формула СУММ.
Синтаксис формулы очень простой:
SUM ( <столбец> )
SUM — это базовая формула, а всё потому что вычисления, связанные с цифрами, в DAX делаются с помощью мер.
Нельзя просто так взять и обратиться к цифрам какого-то столбца напрямую. Придется это сделать с помощью какой-то агрегирующей формулы, чаще всего – с помощью SUM. Так что эта формула не только считает сумму, без нее в принципе мало какие расчёты работают )
2. BLANK
Формула BLANK возвращает пустое значение. Пустое значение в DAX – это отсутствие значения, а не привычный нам в Excel 0 (ноль) или пустая строка ("").
Записывается формула очень просто, никаких аргументов у нее нет:
BLANK ()
Формулы BLANK нет в «обычном» Excel, но в DAX она используется очень часто. Для чего нужна формула BLANK? Например, она может помочь скрыть в отчетах ненужные значения.
3. IF
IF – это логическая формула, аналог ЕСЛИ в Excel. Она проверяет условие и, если условие выполнено, возвращает одно значение, иначе – другое значение. Синтаксис формулы:
IF ( <условие>, <значение если истина> [, <значение если ложь>] )
Какой анализ данных может быть без логических формул?
При всей важности формулы IF, используется она не так часто, как может показаться — во многих DAX-вычислениях её заменяют формулы фильтрации, о которых расскажем позже.
4. DIVIDE
Формула DIVIDE – формула для улучшенного деления. Несмотря на то, что в DAX есть привычный нам оператор деления / , формула DIVIDE лучше. Она удобнее и в ней не надо делать проверку ошибки деления на ноль. Формула сама всё проверит и заменит ошибку на пустое значение.
Синтаксис формулы:
DIVIDE ( <числитель>, <знаменатель> [, <альтернативный результат>] )
<альтернативный результат> — это значение, которое будет выводиться, когда деление на ноль приводит к ошибке. Его указывать необязательно, по умолчанию формула возвращает пустое значение.
5. MIN и MAX
Формулы MIN и MAX – это агрегирующие формулы. Они находят минимальное и, соответственно, максимальное значение из столбца или из двух выражений (выражение должно вычислять единичное значение).
MIN ( <столбец> )
MIN ( <выражение1>, <выражение2> )
MAX ( <столбец> )
MAX ( <выражение1>, <выражение2> )
Если вы думаете, что эти формулы нужны для поиска наименьшего или наибольшего значения показателя, то вы правы. А еще MIN и MAX могут применяться в вычислениях, связанных с датами. То есть они вам точно пригодятся – выписываем и берем на вооружение!
6. DISTINCTCOUNT
DISTINCTCOUNT – полезная формула. Она подсчитывает количество уникальных значений в столбце таблицы. Синтаксис:
DISTINCTCOUNT ( <столбец> )
С помощью этой формулы можно узнать, например, сколько покупателей сделали покупки или количество уникальных заказов, по которым велась работа. И многое другое.
7. COUNTROWS
Формула COUNTROWS считает количество всех строк в таблице. В отличие от предыдущей формулы, она считает все подряд строки, а не только уникальные значения. С помощью этой формулы можно узнать, например, число всех транзакций за период.
COUNTROWS ( <таблица> )
Кстати, COUNTROWS умеет считать строки не только в простой таблице, но и в таблице, заданной каким-то выражением, например, с помощью фильтрации. Для подсчета пустых ячеек используется формула COUNTBLANK.
Давайте разберем, какие еще вычисления можно делать с помощью DAX.
Функции агрегирования
Как мы уже говорили, в DAX-формулах для обращения к данным нужно писать формулы агрегирования, такие как SUM, MAX и MIN. Также часто встречаются AVERAGE и COUNT – среднее и количество.
Кроме таких функций существуют еще другие, похожие на них с окончаниями «А» и «Х». Функции с «A» на конце обрабатывают непустые ячейки. Формулы с «X» позволяют выполнять вычисления по строкам.
| Что считать | Вычисления по таблице | Вычисления для непустых значений (A) | Вычисления для каждой строки таблицы (Х) |
| Сумма | SUM | SUMX | |
| Среднее | AVERAGE | AVERAGEA | AVERAGEX |
| Максимум | MAX | MAXA | MAXX |
| Минимум | MIN | MINA | MINX |
| Количество | COUNT | COUNTA | COUNTX |
Для чего нужны построчные вычисления в формулах с «X»? Если создать меру так:
Продажи = SUM ( 'Данные'[Цена] ) * SUM ( 'Данные'[Количество] ), вычисления будут некорректные.
Необходимы вычисления по строкам:
Продажи = SUMX ( 'Данные'; 'Данные'[Цена] * 'Данные'[Количество] )
Логические функции
Логические функции в DAX довольно просты для понимания. Они выполняют то же, что в «обычном» Excel. Чтобы вам было проще разобраться, собрали в таблице часто используемые логические функции.
| Формула | Что делает | Похожая формула Excel |
| IF | проверка выполнения условия | ЕСЛИ |
| AND, && | проверяет, все ли аргументы истинные | И |
| OR, || | проверяет, есть ли хотя бы один аргумент, равный TRUE | ИЛИ |
| NOT | меняет логическое значение на противоположное | НЕ |
| TRUE, FALSE | значения Истина и Ложь | ИСТИНА, ЛОЖЬ |
| SWITCH | аналог формулы IF, более удобный для множественных условий | ВЫБОР |
Формула SWITCH может выбрать 255 вариантов значений в зависимости от того, чему равна влияющая ячейка. Например, мы можем записать формулу для времени года так:
Время года = IF( MONTH([Дата])=1; "Зима"; IF( MONTH([Дата])=2; "Зима"; IF( MONTH([Дата])=3; "Весна"; IF(MONTH([Дата])=4; "Весна"; … )
и так далее – даже если мы используем OR, легче не станет.
Со SWITCH все проще:
Время года =
SWITCH(
MONTH([Дата]);
1; "Зима";
2; "Зима";
3; "Весна";
4; "Весна"; … )
Математические функции
Чтобы хорошо разобраться в математических формулах, вспомните, какие именно из них вы чаще всего применяете в вычислениях и найдите аналогичные в DAX. Про формулы SUM и DIVIDE мы уже писали, а далее в таблице собраны другие популярные формулы.
| Формула | Что делает | Похожая формула Excel |
| ABS | находит модуль числа | ABS |
| SIGN | определяет знак числа | ЗНАК |
| POWER | возведение в степень | СТЕПЕНЬ |
| SQRT | находит квадратный корень | КОРЕНЬ |
| QUOTIENT | возвращает только целую часть деления | ОТБР |
| RANDBETWEEN | возвращает случайное число в диапазоне между двумя числами | СЛУЧМЕЖДУ |
| ROUND | округление до заданного числа десятичных разрядов | ОКРУГЛ |
| ROUNDUP | округление в большую сторону | ОКРУГЛВВЕРХ |
| ROUNDDOWN | округление в меньшую сторону | ОКРУГЛВНИЗ |
Текстовые функции
Текстовые функции в DAX основаны на аналогичным списке функций в Excel. Наиболее часто используемые функции собраны в таблице.
| Формула | Что делает | Похожая формула Excel |
| CONCATENATE, CONCATENATEX и оператор & | объединяет текстовые строки в одну, оператор & используется для объединения строк текста | СЦЕПИТЬ, ОБЪЕДИНИТЬ и & |
| TRIM | удаляет лишние пробелы | СЖПРОБЕЛЫ |
| LOWER и UPPER | преобразует все буквы в строке в строчные / прописные | СТРОЧН и ПРОПИСН |
| LEFT и RIGHT | возвращает указанное количество символов с начала (конца) строки | ЛЕВСИМВ и ПРАВСИМВ |
| LEN | возвращает число символов в строке | ДЛСТР |
| FIND и SEARCH | возвращает номер начальной позиции искомого текста в строке (с учетом или без учета регистра) | НАЙТИ и ПОИСК |
| MID | возвращает строку из текста по начальной позиции и длине | ПСТР |
| FORMAT | преобразует значение в текст в соответствии с указанным форматом | ТЕКСТ |
Функции для работы с датами
В DAX часто встречаются вычисления, связанные с датами. Поэтому там много формул, позволяющих такие расчеты выполнять.
| Формула | Что делает | Похожая формула Excel |
| TODAY | определяет сегодняшнюю дату | СЕГОДНЯ |
| DATE | возвращает заданную дату | ДАТА |
| DAY, MONTH, YEAR | вычисляет день, месяц, год для заданной даты | ДЕНЬ, МЕСЯЦ, ГОД |
| WEEKDAY | возвращает номер дня недели, от 1 до 7 | ДЕНЬНЕД |
| WEEKNUM | определяет номер недели в году | НОМНЕДЕЛИ |
| EDATE | находит дату через указанное число месяцев от заданной даты | ДАТАМЕС |
| EOMONTH | находит дату последнего дня месяца до или после указанного числа месяцев | КОНМЕСЯЦА |
Кроме функций, перечисленных выше, в DAX существуют другие – функции связей, обработки таблиц, информационные, статистические, финансовые, функции операций со временем и т.д. Как видите, язык DAX позволяет выполнять самые разные вычисления. Примеры таких вычислений можно посмотреть в следующей статье — ТОП-20 DAX формул.
