Функции DAX в Excel: первая мера в Power Pivot и подсчёт с условием

Задача обычная: в модели лежат продажи по регионам, а руководителю нужна выручка Севера и доля категории, которые пересчитываются при смене среза. Формулы листа здесь не помогут: функции модели никогда не принимают в качестве аргумента ссылку на ячейку или диапазон^[1]. Расчёт живёт в модели и работает со столбцами целиком.

Путь освоения короткий. Сначала поймите задачу — какой итог нужен и по каким срезам. Затем выберите инструмент: мера вместо формулы на листе. Освойте основы — запись меры, CALCULATE и контекст фильтра, — попробуйте написать меру в тренажёре и примените её к своей модели. Главная ошибка новичка — ждать, что мера увидит ячейку, как СУММ на листе.

Ключевые факты: функции DAX

• DAX — язык выражений формул для Analysis Services, Power BI и Power Pivot в Excel^[2].
• В вычисляемых столбцах поддерживается до 64 уровней вложенности функций DAX^[1].
• Функции DAX не принимают в качестве аргумента ссылку на ячейку или диапазон^[1].
• Мера отличается от вычисляемого столбца тем, что считается только для ячеек сводной, а не хранится в каждой строке^[3].
• Справка Microsoft о создании меры описывает шаблон «<Имя> меры:<Формула>» для строки формул окна Power Pivot^[4].

Что такое функции DAX и где их пишут в Excel

Функции DAX берут столбцы и таблицы модели данных, а не ячейки листа. Документация Microsoft называет DAX языком выражений формул для Analysis Services, Power BI и Power Pivot в Excel^[2]. В Excel формулы DAX пишут в двух местах: в мерах и в вычисляемых столбцах^[1]. Что умеет мера: сумму, среднее, долю и итог по условию, который пересчитывается при смене среза.

Справка о DAX в Power Pivot относится к Excel для Microsoft 365, Excel 2024, Excel 2021, Excel 2019 и Excel 2016^[1]; Excel для Mac в этом блоке не назван. В Google Таблицах DAX нет: документация Microsoft перечисляет среды языка — Analysis Services, Power BI и Power Pivot в Excel^[2]. Вычисляемый столбец хранит значение в каждой строке таблицы модели, а мера считается только для ячеек сводной^[3] — поэтому итоги и доли почти всегда пишут мерами. Контекст строки — это текущая строка: формула вычисляемого столбца видит значения всех столбцов этой строки^[2].

Разобравшись с тем, где живут формулы, переходим к записи первой меры.

Как записать первую меру в Power Pivot: имя, двоеточие и формула

Меру DAX создают в области вычислений: имя, двоеточие и формула. Возьмём таблицу «Продажи» из модели зоомагазина:

1. В окне Power Pivot включите область вычислений: Главная → Просмотр → область вычислений^[4].
2. Щёлкните пустую ячейку области под таблицей «Продажи».
3. В строке формул введите имя меры, двоеточие и формулу, затем нажмите Enter^[4]:

Выручка:=SUM('Продажи'[Выручка])

Русская справка показывает шаблон как «<Имя> меры:<Формула>», а сама формула начинается со знака равенства^[4] — отсюда запись Имя:=формула. Другой путь — диалог создания меры с полями имени таблицы, имени меры и формулы и кнопкой «Проверить формулу»^[4].

Имя меры должно быть уникальным: нельзя брать имена, уже заданные для столбцов^[4]. Неявные меры, которые сводная создаёт сама, умеют только SUM, COUNT, MIN, MAX, DISTINCTCOUNT и AVG^[4] — для условий и долей нужна явная мера.

Напишите первую меру в тренажёре: урок попросит выбрать правильную запись меры и сравнить её итог с формулой листа.

Однако сумма — простейший случай: условие в мере устроено иначе, чем в формуле листа.

CALCULATE и контекст фильтра: чем мера с условием отличается от формулы листа

Функция CALCULATE в DAX меняет контекст фильтра сводной и считает выражение заново. Документация определяет контекст фильтра как набор значений, разрешённых в каждом столбце или полученных из связанной таблицы^[2]. Строка «Юг» в сводной — это фильтр Регион = «Юг», и формула считает только эти строки.

Синтаксис по английской справке: CALCULATE(<expression>, [[<filter1>], <filter2>]…)^[5]. Выручка Севера на основе базового расчёта:

Выручка Север:=CALCULATE([Выручка], 'Продажи'[Регион] = "Север")

Если столбца фильтра ещё нет в контексте, CALCULATE добавляет фильтр, а если есть — перезаписывает существующий^[5]. Поэтому в строке «Юг» такой расчёт всё равно покажет выручку Севера. Несколько фильтров объединяются по И: все условия должны выполняться одновременно^[5]. Сверить итог можно формулой листа =СУММЕСЛИ(A2:A9;"Север";D2:D9) — она возвращает 75200.

Булев фильтр CALCULATE может ссылаться только на один столбец и не может ссылаться на меры^[5]; функции, которые сканируют таблицу с агрегатами вроде SUM, в нём тоже запрещены^[5].

Остаётся понять, в каких задачах мера лишняя.

Когда мера DAX не подойдёт

Мера DAX не подойдёт, если итог нужен в обычной ячейке листа. Расчёт возвращает значения только внутри сводной или отчёта по модели, а ссылку на ячейку язык не принимает^[1].

• Если данных нет в модели, мерам нечего считать — сначала добавьте таблицы в модель данных Power Pivot.
• Когда таблицу надо почистить — удалить повторы, поменять типы, — нужен запрос Power Query со своими шагами, а не формула.
• Если формула вычисляемого столбца глубже 64 уровней вложенности функций, её придётся разбить^[1].
• При условии, которое ссылается на меру или на два столбца сразу, булев фильтр CALCULATE не пройдёт проверку^[5].
• Если модель развёртывают в режиме DirectQuery, часть формул может давать ошибки проверки^[1].

Коротко: мера DAX не подойдёт, если итог нужен в ячейке листа, если данных нет в модели или условие фильтра ссылается на меру.

Справка добавляет важную оговорку: Power Pivot не обеспечивает целостность данных^[1]. Расчёт честно обработает то, что лежит в модели, поэтому перед отчётом сверьте итог с формулой листа на тех же строках. Соседние темы собраны среди всех уроков тренажёра по формулам модели данных.