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 года во внешней com-надстройке 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. Давайте разберемся, что это такое.
Вычисляемый столбец – это столбец, который добавляется в существующую таблицу, а DAX-формула определяет значения этого столбца.
Как и обычные столбцы в модели данных, вычисляемые столбцы можно использовать в других вычислениях. А также для создания связей между таблицами, для построения визуализаций и срезов. В сводных таблицах вычисляемые столбцы можно помещать в области фильтров, колонок, строк и значений.
Если данные в вашем файле загружаются в режиме импорта, то столбец рассчитывается и записывается в файл при загрузке и обновлении данных, увеличивая размер файла. Вычисляемые столбцы лучше использовать, когда нужен текст, дата или когда вычисление зависит от соседних колонок.
Вычисляемые столбцы создаются просто, как в Power Pivot, так и в Power BI: добавляется новый столбец, пишется «равно» и формула.
Чтобы обратиться к вычисляемому столбцу в других вычислениях, нужно написать имя таблицы, в которой он находится, и название самого столбца. Например, '
Таблица'
[Столбец]
Меры – это динамические вычисления, результаты которых рассчитываются в зависимости от контекста. Результат вычисления меры можно увидеть в отчете, где мы задаем в каком именно контексте (в разрезе каких полей, фильтров и др.) нужно посчитать меру.
Как создать меру:
- В Excel меры записывают в окне Power Pivot в области для вычислений под таблицей: выберите ячейку, введите название меры и знак :=
Или в меню Power Pivot → Меры → Создать меру. - Чтобы создать меру в Power BI, нажмите Главная → Создать меру (или нажать правой кнопкой мышки в области полей по таблице → Создать меру).
При создании мер нужно обязательно использовать агрегирующие функции, например суммирования SUM. Мера не может быть создана просто как обращение к столбцу таблицы:
-
Так не работает: прибыль:= '
Данные'
[выручка] —
'
Данные'
[расходы]
+ Так работает: прибыль:= SUM('
Данные'
[выручка]) – SUM('
Данные'
[расходы])
Меры лучше создавать, когда нужны числовые вычисления, например, для промежуточных итогов, вычисления процентов, доли продукта в группе и так далее. Меры можно использовать для вычисления других мер и столбцов. При оформлении отчетов и сводных таблиц меры добавляются только в область значений.
Чтобы использовать меру в других вычислениях, ее название пишут в квадратных скобках.
Пример: МераВ = [МераА] + 100
Примечание о записи формул и разделителей:
- В Power BI формулы записывают с помощью знака равно = и разделителей-запятых.
Пример: Мера = IF( [kpi]>100, [a], [b])
В настройках Power BI есть возможность выбрать, какой именно разделитель использовать в формулах – запятую или точку с запятой.
- В Power Pivot разделителем в формулах может быть запятая «,» или точка с запятой «;» в зависимости от региональных настроек.
Вычисляемые столбцы записывают с помощью знака =
При создании меры пишут её название и знак :=
Пример: Мера:= IF( [kpi]>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 | значения Истина и Ложь | ИСТИНА, ЛОЖЬ |
IFERROR | проверяет, нет ли ошибки | ЕСЛИОШИБКА |
SWITCH | аналог формулы IF, более удобный для множественных условий | ВЫБОР |
В пояснении нуждаются только две последние формулы — IFERROR и SWITCH.
Если формула в некоторых случаях выдает ошибку, ее можно «перехватить» с помощью IFERROR. Хотя лучше сразу проверять данные на ошибки — до выполнения расчетов.
БезОшибки = IFERROR( [Цена] * [Количество] ; BLANK() ) |
Формула 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 есть формулы фильтров, аналога которых в «обычном» Excel нет и быть не может. Потому что такие формулы позволяют ссылаться не просто на столбец, а целиком на таблицу. Формулы фильтрации можно подставлять в меры и тогда они будут выдавать «виртуальные» таблицы с заданными параметрами. Такие таблицы не дают видимого результата и используются как промежуточные функции внутри вычисления. Примером таких функций являются SUMMARIZE, ADDCOLUMNS и более часто используемые формулы FILTER, ALL.
В определениях DAX функция CALCULATE относится к функциям фильтрации. CALCULATE работает по аналогии с формулой СУММЕСЛИМН при указании в этой формуле суммы и условия отбора – фильтра:
продажи-2020 = CALCULATE( [факт]; ' Календарь' [Год] = 2020 ) |
Самыми яркими представителями функций фильтрации являются FILTER и ALL:
- Функция FILTER создает отфильтрованную таблицу. Другими словами, с помощью этой формулы можно извлечь список, соответствующий определенному критерию.
- Функция ALL снимает фильтры, примененные к таблице. Она используется, например, чтобы посчитать долю продаж товара:
Доля товара = DIVIDE( [выручка]; CALCULATE( [выручка]; ALL( '
Товары'
) )
Кроме функций, перечисленных выше, в DAX существуют другие – функции связей, обработки таблиц, информационные, статистические, финансовые, функции операций со временем и т.д. Как видите, язык DAX позволяет выполнять самые разные вычисления. Примеры таких вычислений можно посмотреть в следующих статьях.