Чтобы превратить отчеты Power BI и Excel в настоящий инструмент аналитики, вам потребуются DAX-формулы. Используя эти формулы, можно настраивать вычисления в отчетах один раз, а при изменении исходных данных в отчете показатели будут пересчитаны. В Excel для этого потребуется нажать только одну кнопку — «Обновить все».
В этой статье мы собрали самые полезные и часто встречающиеся DAX-формулы. Какие именно формулы выбрали? Все, что доступно пользователю, перечислять смысла нет. А вот несколько формул, которые позволят решить 80% задач аналитики и пригодятся для оформления отчетов, рассмотреть стоит. Например, как показать остатки на конец периода, а не сумму всех остатков, как обычно получается в сводной таблице. Или как посчитать нарастающие итоги.
Кстати, в предыдущей статье «Введение в вычисления DAX» есть примеры других полезных и более простых формул – SUM, MIN, MAX, DIVIDE, BLANK и др. Если вы ещё не читали эту статью, советуем начать с неё.
Язык DAX есть в следующих приложениях:
Power BI Desktop
Power Pivot в Excel
Visual Studio
SQL Server Management Studio
DAX Studio
Tabular Editor
Для удобства формулы в статье разделены на две части.
Часто применяемые DAX-формулы:
Формулы для развернутой аналитики:
1. CALCULATE
Функция CALCULATE используется в большинстве вычислений Power BI и Power Pivot. Она создаёт новый контекст фильтра на основе условий, заданных пользователем. Ее название так и переводится – «вычислить», поэтому без такой формулы отчеты точно сделать не получится.
Записывается формула просто:
CALCULATE ( <выражение> [, <фильтр1>, <фильтр2> … ] )
выражение — вычисляемое выражение, обычно мера. Если вы хотите обратиться напрямую к столбцам, делайте это через агрегирующие функции SUM, MAX, SUMX и др., но все-таки лучше сначала создать меру, а потом подставлять её;
фильтр 1, фильтр 2… (необязательно) — логические фильтры, табличные выражения или функции изменения фильтра — модификаторы.
Пример. У нас есть таблица с данными о доходах и расходах. Нужно показать выручку по городам. Мы можем настроить фильтрацию на уровне визуальных элементов, убрав галочки по статьям расходов. Или настроить фильтрацию на уровне формулы с использованием CALCULATE.
Выручка =
CALCULATE (
SUM ('Данные'[Значение] ),
'спСтатьи'[Статья] = "Выручка"
)
Это самый простой вариант вычисления с CALCULATE. Чаще эту функцию используют вместе с табличными функциями, например, FILTER или ALL. Или с модификаторами – USERELATIONSHIP, REMOVEFILTERS и др. Что это за функции и как они работают – смотрите дальше.
2. KEEPFILTERS
KEEPFILTERS – это модификатор CALCULATE и CALCULATETABLE. Она не влияет на работу CALCULATE, а управляет ее результатом.
При вычислениях формула CALCULATE перезаписывает существующие фильтры в столбцах отчета. А если перезапись фильтров не требуется её можно убрать с помощью KEEPFILTERS. Формула записывается так:
KEEPFILTERS ( <выражение> )
выражение — на сайте MS сказано «любое выражение». Уточним: условие фильтрации и в редких случаях табличная функция.
Пример. В предыдущем примере мы использовали CALCULATE, чтобы посчитать выручку. Если добавить получившуюся меру в таблицу, значения выручки отобразятся во всех строках, в том числе по статьям расходов. Это как раз и есть перезапись фильтров. С помощью KEEPFILTERS уберем выручку из строк со расходами:
Выручка KEEPFILTERS =
CALCULATE (
[Выручка],
KEEPFILTERS (
'спСтатьи'[Статья] = "Выручка"
)
)
3. FILTER
Функция FILTER относится к итераторам, результатом её выполнения является отфильтрованная таблица. Записывается просто – укажите таблицу и напишите, как вы хотите её отфильтровать:
FILTER ( <таблица>, <фильтр> )
таблица — таблица, из которой выбираются данные (или выражение, определяющее таблицу);
фильтр — логическое выражение, с помощью которого выполняется отбор.
Пример. Найти продажи по указанным городам.
Выручка FILTER =
CALCULATE (
[Выручка],
FILTER (
VALUES ('Регионы'[Город] ),
OR (
'Регионы'[Город] = "Пермь",
'Регионы'[Город] = "Красноярск" )
)
)
4. ALL
Функция ALL снимает примененные к таблице или столбцу фильтры, и позволяет создавать вычисления по всем строкам таблицы или по всем значениям столбца. Синтаксис:
ALL ( <таблица> или <столбец> [, <столбец1> ,<столбец2>, … ] )
таблица — таблица, в которой нужно очистить фильтры (не может быть выражением);
столбец, в котором нужно очистить фильтры (не может быть выражением).
Пример. Наиболее частый сценарий использования ALL – это расчёт процента или доли:
Доля, % =
DIVIDE (
[Факт],
CALCULATE ( [Факт], ALL ( 'Регионы' ) )
)
Если из таблицы с помощью среза убрать Москву и Санкт-Петербург, результат, рассчитанный с ALL все равно сохранит рассчитанные значения и в общем итоге покажет сумму оставшегося процента. Иногда требуется, чтобы процент пересчитывался. Задачу решает функция ALLSELECTED, которая учитывает фильтры, выбранные пользователем:
Доля ALLSELECTED =
DIVIDE (
[Факт],
CALCULATE ( [Факт], ALLSELECTED ( 'Регионы'[Город] ) )
)
Результат для ALLSELECTED:
Также существует функция ALLEXEPT, которая позволяет отключить фильтры по множеству столбцов и сохранить по небольшому количеству, и другие ALL*-функции, которые используются реже.
5. REMOVEFILTERS
Функция REMOVEFILTERS появилась в Power BI в 2019 году, она более точно формулирует действие – удаление фильтров. В отличие от ALL она не может возвращать таблицу, её используют только как аргумент CALCULATE.
REMOVEFILTERS ( [ <таблица> или <столбец>[, <столбец>[, …] ] ] )
таблица — таблица, в которой нужно очистить фильтры;
столбец — столбец, в котором нужно очистить фильтры.
Пример. Посчитать долю % продаж по городам от общей суммы.
Доля REMOVEFILTERS =
DIVIDE (
[Факт],
CALCULATE ( [Факт], REMOVEFILTERS ( 'Регионы' ) )
)
Результат вычислений получается такой же, как с формулой ALL.
Может возникнуть вопрос, что лучше использовать c CALCULATE – ALL или REMOVEFILTERS?
Функцию REMOVEFILTERS иногда называют «псевдонимом» функции ALL, её название делает результат более очевидным для чтения формулы. С другой стороны, авторы книги «DAX для профессионалов» Мишель Розема и Хенк Влотман отмечают, что предпочитают использовать более короткую функцию ALL и никогда не применяют REMOVEFILTERS.
6. SUMX
Функция SUMX является итератором, она «проходит» по строкам таблицы и выполняет вычисления для каждой строки по заданному выражению, а затем суммирует результаты.
По аналогии с этой функцией в Power Pivot и Power BI есть и другие «иксовые» функции MAXX, MINX, AVERAGEX. Они выполняют действие по строке, которое указал пользователь, а затем применяют агрегирование – расчет суммы, максимума, минимума и др. Эти функции выполняют итерации не только по обычной таблице, но и по таблице, созданной внутри функции, например «внутри» FILTER или ALL.
SUMX ( <таблица>, <выражение> )
таблица — таблица или выражение, определяющее таблицу;
выражение — выражение для каждой строки таблицы.
Если вам нужно просто посчитать сумму по столбцу, используйте формулу SUM. Если нужно рассчитать построчно показатели в таблице и затем сложить их, потребуется SUMX.
Пример. Посчитать продажи по формуле: Продажи = Количество х Цена.
Продажи =
SUMX (
'Продажи',
'Продажи'[Количество] * 'Продажи'[Цена]
)
В таблице в каждой строке количество умножается на цену, а полученные результаты суммируются. В данном случае формула SUMX сработала очень похоже на «обычную» формулу Excel – СУММПРОИЗВ.
7. CALENDAR
Справочник дат – Календарь – обязателен как для Power Pivot, так и Power BI. Он нужен для корректной работы функций временной аналитики, для удобства и фильтрации отчётов. В Power BI справочник дат создается функцией CALENDAR или CALENDARAUTO. Функция CALENDAR имеет простой синтаксис:
CALENDAR ( <Дата начало>, <Дата конец> )
То есть вы сами определяете, по каким критериям формируется диапазон дат. Например, можно записать следующее выражение:
Календарь = CALENDAR ( DATE ( 2023,1,1 ), DATE ( 2025,12,31 ) )
Но чаще такая функция записывается с учетом того, что даты постоянно обновляются. И например, дата на конец определяется на основе максимальной даты из таблиц с фактическими данными, прогнозом, планом и т. п.
Вторая функция для построения справочника дат в Power BI:
Календарь = CALENDARAUTO ( )
Функция CALENDARAUTO не требует явного указания дат, сама просматривает модель и формирует диапазон на основе имеющихся дат. Но при её использовании нужно контролировать диапазоны полученных дат, потому что если у вас в таблицах есть даты вне анализируемого в отчете диапазона — даты рождения сотрудников или другие похожие даты, формула создаст справочник с большим количеством лишних строк.
А вот в Power Pivot в Excel формулы для построения Календаря использовать невозможно. Потому что там нет возможности создавать вычисляемые таблицы. Поэтому календарь в Power Pivot можно сделать с помощью специальной кнопки «Таблица дат» или с помощью загрузки из Power Query.
8. FORMAT
Для создания таблицы дат с подписями дней недели, месяцев, кварталов, а также для управления подписями числовых значений вам пригодится FORMAT. Эта функция преобразует значение в текст в указанном формате. Это аналог формулы ТЕКСТ в «обычном» Excel.
Синтаксис:
FORMAT ( <значение>, <формат строки> [, <локаль> ] )
значение — значение или выражение, результат которого единственное значение;
формат строки — схематичное описание форматирования;
локаль — (необязательно) имя языковой схемы, под которую требуется форматирование.
В Power Pivot в Excel параметр локаль недоступен.
Пример: создать столбец с сокращенными названиями дней недели на основе столбца с датами.
День недели = FORMAT ( [Дата], "DDD", "en-US" )
С помощью формулы FORMAT и шаблона "DDD" в созданном вычисляемом столбце будут записаны даты в виде сокращенных названий дней недели – пн, вт, ср и т.д. Кроме шаблона форматирования "DDD" в формулу можно добавить другие форматы, например, "DDDD" для отображения дней недели полностью (понедельник, вторник и т. д.). Язык подписей будет зависеть от региональных настроек.
В Power BI Desktop с 2022 года доступен третий (необязательный) аргумент – локаль, с помощью которого можно явно задать язык:
"en-US"– дни недели всегда на английском,"ru-RU"– на русском.
Примеры форматов строк для дат, записываются в кавычках:
| D | день месяца в виде числа без ведущих нулей (например, 1) |
| DD | день месяца в виде числа с ведущими нулями (например, 01) |
| DDD | сокращенное название дня недели (пн, вт, ср, …) |
| DDDD | полное название дня недели (например, воскресенье) |
| M | месяц в виде числа без ведущих нулей (например, январь — число 1) |
| MM | месяц в виде числа с ведущими нулями (например, 01) |
| MMM | сокращенное название месяца (янв) |
| MMMM | полное название месяца (январь) |
| Q | номер квартала |
| YY | год в виде двух цифр |
| YYYY | год в формате из четырех цифр |
Результат формулы для дат:
Для настройки подписи числовых значений используются схемы, похожие на Excel – с помощью нулей и решёток (#). Например, Сумма -12 856 358,225 может быть приведена с помощью FORMAT в следующий текст:
День недели = FORMAT ( Сумма, "#,0.00" ) |
-12 856 358,23 | с разделителем разрядов |
День недели = FORMAT ( Сумма, "#,0.00;(#,0.00);-" ) |
(12 856 358,23) | отрицательное значение в скобках |
День недели = FORMAT ( Сумма, "#,0,,.00M" ) |
-12,86M | сократить 6 знаков |
День недели = FORMAT ( Сумма, "#,0.00", "en_US" ) |
-12,856,358.23 | записать число с заданной локалью |
Кстати, формула FORMAT также используется, чтобы создавать подробные подписи к диаграммам.
9. DATEADD
С помощью DATEADD можно сравнивать показатели разных периодов, например, текущий и прошлый год. Эта функция возвращает таблицу с набором дат, смещенных в будущее или в прошлое на указанное число интервалов.
DATEADD ( <даты>, <количество интервалов>, <вид интервала> )
даты — столбец с датами;
количество интервалов — число интервалов, на которое нужно сдвинуть исходные даты;
вид интервала — интервал может быть следующим: YEAR, QUARTER, MONTH, DAY.
Пример. Посчитать продажи за предыдущий год.
Продажи прошлый год =
CALCULATE (
[Продажи],
DATEADD ( 'Календарь'[Date], -1, YEAR )
)
10. TOTALYTD
Формула TOTALYTD вычисляет значение выражения с начала года в текущем контексте.
То есть с помощью TOTALYTD можно посчитать нарастающий итог по показателю за год. Чтобы рассчитать нарастающие итоги по кварталам или месяцам, вам потребуются похожие формулы: TOTALQTD для квартала и TOTALMTD для месяца.
Записывается формула просто:
TOTALYTD ( <выражение>, <даты> [, <фильтр> ] [, <конец года>] )
TOTALQTD ( <выражение>, <даты> [, <фильтр> ] )
TOTALMTD ( <выражение>, <даты> [, <фильтр> ] )
выражение — выражение, рассчитывающее скалярное значение;
даты — столбец дат или выражение, его определяющее;
фильтр — (необязательно) выражение, задающее фильтр;
конец года (необязательно) дата окончания года, вводится в кавычках, по умолчанию 31 декабря.
Пример. Посчитать продажи нарастающим итогом за квартал и год.
Подробнее про расчеты показателей с нарастающим итогом — как рассчитать сквозной нарастающий итог, как его не показывать в периодах, где нет данных и т.д. — смотрите в статье «Нарастающие итоги».
Продажи QTD квартал =
TOTALQTD ( [Продажи], 'Календарь'[Date] )
Продажи YTD год =
TOTALYTD ( [Продажи], 'Календарь'[Date] )
11. LASTNONBLANK
LASTNONBLANK определяет последнее непустое значение в столбце. Чтобы найти первое непустое значение в столбце – FIRSTNONBLANK. Синтаксис формул:
FIRSTNONBLANK ( <столбец>, <выражение> )
LASTNONBLANK ( <столбец>, <выражение> )
столбец — столбец (или выражение, возвращающее один столбец);
выражение — выражение, в котором проверяется наличие пустых значений в столбце.
Эти функции могут быть пригодиться в случае, если у вас в исходных данных остатки идут не на последнюю дату. При этом нужно быть осторожным, потому что LASTNONBLANK будет «тянуть» остаток пока на ее пути не появится другое значение, см. рисунок с примером.
Подробнее про формулы остатков и способы вычислений в зависимости от исходных данных можно прочитать в статье «DAX-формулы остатков».
Пример. Найти остатки на конец периода.
Остаток =
CALCULATE (
SUM ( 'данные'[остаток ТМЦ] ),
LASTNONBLANK (
'Календарь'[Date],
CALCULATE ( SUM ('данные'[Дата] ) )
)
)
12. CLOSINGBALANCEYEAR
CLOSINGBALANCEYEAR вычисляет значения выражения для последней даты года.
Аналогично расчет для последней даты квартала – CLOSINGBALANCEQUARTER, для месяца — CLOSINGBALANCEMONTH. Так можно рассчитать остатки товаров или денег на счете на конец периода.
CLOSINGBALANCEYEAR ( <выражение>, <даты> [, <фильтр> ][, <конец года> ] )
CLOSINGBALANCEQUARTER ( <выражение>, <даты> [, <фильтр> ] )
CLOSINGBALANCEMONTH ( <выражение>, <даты> [, <фильтр> ] )
выражение — выражение, рассчитывающее скалярное значение;
даты — столбец дат или выражение, его определяющее;
фильтр — (необязательно) выражение, задающее фильтр;
конец года — (необязательно) дата окончания года, вводится в кавычках, по умолчанию 31 декабря.
Пример. Посчитать остатки по данным на последнюю дату месяца.
Подробное описание расчетов остатков с помощью функций CLOSINGBALANCE* и OPENINGBALANCE* смотрите в статье про формулы остатков.
Сумма на конец периода =
CLOSINGBALANCEMONTH ( SUM ( 'Данные'[Значение] ), 'Календарь'[Date] )
Обратите внимание: для функций группы CLOSINGBALANCE нужно, чтобы были заполнены исходные данные на последнее число периода.
13. TREATAS
Функция TREATAS применяет результат табличного выражения в качестве фильтров к столбцам из не связанной таблицы.
До этого примеры в статье приводились для связанных таблиц. Но бывают ситуации, когда нужно фильтровать данные таблицы, у которой нет связей. Это может быть, например, таблица, созданная с помощью выражения DAX без привязки к исходным данным.
Например, выражение
Таблица = { "Москва", "Пермь" }
вернет таблицу:
Эту таблицу можно использовать в формуле CALCULATE для извлечения данных с помощью TREATAS.
Синтаксис функции:
TREATAS ( <выражение>, <столбец> [, <столбец> [, … ] ] )
выражение — выражение, которое создает таблицу со столбцами для переопределения;
столбец — выходной столбец, не может быть выражением.
Пример: посчитать продажи по выбранным городам (Москва, Пермь) и вывести сумму во всех строках таблицы.
Продажи TREATAS =
CALCULATE (
[Выручка],
TREATAS ( { "Москва", "Пермь" }, 'Регионы'[Город] )
)
Важно: если столбцов несколько, их нужно указывать в одинаковой последовательности в обеих таблицах.
14. VALUES
Функция VALUES возвращает значения столбца или таблицы:
- если указать столбец, то VALUES вернет список его уникальных значений;
- если как аргумент указать таблицу, то формула вернет список всех строк таблицы с сохранением повторений.
При вычислениях с VALUES примененные фильтры сохраняются и возвращаются видимые в текущем контексте значения, а пустые строки учитываются в расчетах. Обычно VALUES используется как промежуточная формула внутри других вычислений. Записывается формула просто:
VALUES ( <таблица или столбец> )
таблица или столбец — cтолбец, из которого должны возвращаться уникальные значения, или таблица, из которой должны возвращаться строки.
Пример. Посчитать, сколько менеджеров работало в каждом городе.
Менеджеры =
COUNTROWS ( VALUES ( 'Данные'[Менеджер] ) )
Для сравнения в таблицу добавлен столбец с похожими вычислениями с помощью формулы ALL:
Менеджеры, неправильный результат =
COUNTROWS ( ALL ( 'Данные'[Менеджер] ) )
Формула ALL игнорирует фильтры, поэтому в строках одинаковые цифры. Формула VALUES сохраняет фильтры и выдает правильный результат.
15. SELECTEDVALUE
Функция SELECTEDVALUE возвращает отфильтрованное значение. Если столбец отфильтрован по одному отдельному значению, то его можно получить с помощью SELECTEDVALUE, в противном случае функция возвращает «альтернативный результат». С помощью этой формулы можно создавать комментарии или делать вычисления, которые зависят от выделенного (отфильтрованного) значения.
Записывается формула просто:
SELECTEDVALUE ( <столбец> [, <альтернативный результат> ] )
столбец — имя столбца, не может быть выражением;
альтернативный результат — (необязательно) значение, если столбец не отфильтрован по одному значению, по умолчанию BLANK()
Пример. Создать заголовок, в котором будет указан выбранный год.
Заголовок =
"Продажи за "
& SELECTEDVALUE ('Календарь'[Год] ) & " год"
16. HASONEVALUE
HASONEVALUE проверяет, содержит ли контекст столбца только одно значение (или контекст должен быть отфильтрован до одного значения). Если это верно, то функция возвращает значение TRUE. Эту функцию удобно применять, если вам нужно сделать расчет только по единичным значениям. А также для написания комментариев и пояснений.
HASONEVALUE ( <столбец> )
столбец — имя столбца, не может быть выражением.
Пример. Если выбран один город, в заголовке написать его название и сумму продаж. Если не выбран один город, показать в заголовке сумму по всем городам.
Заголовок =
VAR _fsales = FORMAT ( [Продажи], "#,##0" )
RETURN
"Продажи " &
IF (
HASONEVALUE ( 'спГород'[Город] ),
SELECTEDVALUE ( 'спГород'[Город] )
& ": " & _fsales,
_fsales
)
17. ISINSCOPE
Функция ISINSCOPE возвращает значение ИСТИНА, если столбец является уровнем в иерархии уровней.
С помощью этой формулы можно управлять отображением итогов в Power BI (в Power Pivot в Excel такой функции нет). Подробнее про управление отображением итогов можно посмотреть статью.
ISINSCOPE ( <столбец> )
столбец — имя столбца, не может быть выражением.
Пример. В таблице показать для групп вывести значение одного показателя (в примере это слово «группа» и «итог», а вы можете подставить что-то другое — свою меру), а для товаров показать сумму продаж.
Уровень =
SWITCH (
TRUE (),
ISINSCOPE ( 'спТовары'[Товар] ), [Продажи],
ISINSCOPE ( 'спТовары'[Группа] ), "Группа",
"Итог"
)
18. USERELATIONSHIP
Функция USERELATIONSHIP является модификатором CALCULATE, который позволяет активировать неактивные связи между таблицами.
Например, у нас есть таблица с данными. В ней два столбца с датами: дата отгрузки и дата оплаты. Обе даты важны для отчетов, при этом активная связь между таблицей с данными и Календарем может быть только одна.
На рисунке две связи между таблицами: активная связь между столбцами «Дата» из Календаря и датой отгрузки. Вторая связь между Дата оплаты и Календарем отображается пунктирной линией. Пунктир означает, что связь неактивная, а значит, фильтрация по ней не выполняется.
Эту вторую пунктирную неактивную связь можно «включить» с помощью функции USERELATIONSHIP.
Её синтаксис следующий:
USERELATIONSHIP ( <столбец1>, <столбец2> )
столбец1 — имя столбца, который представляет собой одну сторону связи (не может быть выражением);
столбец2 — имя столбца с другой стороны связи (не может быть выражением).
Пример. Найти суммы оплаты от покупателей, если в таблице с данными есть несколько дат — даты отгрузки и даты оплат.
Оплата =
CALCULATE (
[Сумма],
USERELATIONSHIP (
'Данные'[Дата оплаты],
'Календарь'[Дата]
)
)
Формула будет работать. Вы сможете в одном отчете сопоставлять отгрузки и оплаты.
⚠️ Важный момент: при использовании в отчете безопасности на уровне строк (RLS) функция USERELATIONSHIP не поддерживается. Поэтому если у вас есть роли безопасности, попробуйте найти решение с использованием других функций, например TREATAS.
19. ADDCOLUMNS
ADDCOLUMNS добавляет вычисляемые столбцы к таблице. На выходе рассчитывается таблица с исходными и добавленными столбцами.
Синтаксис формулы:
ADDCOLUMNS ( <таблица>, "столбец", <выражение>[, "столбец2", < выражение2>]…)
таблица — название таблицы или выражение, возвращающее таблицу;
"столбец" — имя нового столбца, который вы добавляете к таблице, вводится в двойных кавычках;
выражение — выражение для расчета значений нового столбца.
С помощью формулы ADDCOLUMNS можно создавать вычисления на основе существующих таблиц. Например, сумму показателей, где ADDCOLUMNS создает новый столбец в таблице с названием «показатель», а SUMX суммирует его значения.
SUMX ( ADDCOLUMNS ( 'Данные', "Показатель", <выражение>), [показатель] )
20. SUMMARIZE
SUMMARIZE – одна из часто используемых DAX-формул. Она возвращает таблицу для запрошенных значений, объединенных в наборы групп. Обычно в расчетах эта формула используется для создания комбинации значений вместо таблицы со всеми данными.
SUMMARIZE ( <таблица>, <столбец> [, <столбец> ] … [, "имя", <выражение> ] … )
таблица — таблица или выражение, возвращающее таблицу;
столбец — столбец для создания групп на основе его значений (не может быть выражением);
"имя" — имя для столбца в двойных кавычках;
выражение — выражение, возвращающее одно скалярное значение, которое может вычисляться несколько раз (для каждой строки или контекста).
Если в «промежуточную» таблицу добавляется новый столбец, то это рекомендуется делать с помощью ADDCOLUMNS: сначала создается сгруппированная таблица с помощью SUMMARIZE, а затем в нее добавляется столбец в ADDCOLUMNS.
