Функция CALCULATE

Функция CALCULATE — это функция языка выражений анализа данных (DAX), используемая в таких продуктах Microsoft, как Power BI, Analysis Services и Power Pivot для Excel. Она предназначена для изменения контекста фильтрации при вычислении выражений и является одной из наиболее мощных и часто используемых функций в DAX. CALCULATE позволяет динамически переопределять фильтры, накладываемые на модель данных, и вычислять меры в заданном, изменённом наборе данных.

Синтаксис и параметры

Функция CALCULATE имеет следующий синтаксис:

`` CALCULATE( <Выражение>, <Фильтр1>, <Фильтр2>, ... ) ``

• Выражение (обязательный) — любое выражение DAX, возвращающее скалярное значение (например, число, дата, строка). Обычно это агрегатная функция, такая как SUM, AVERAGE, COUNTROWS, или ссылка на меру.
• Фильтры (необязательные) — один или несколько аргументов, задающих новые условия фильтрации. Фильтр может быть:
• Логическим выражением (например, 'Таблица'[Столбец] = "Значение").
• Табличным выражением, возвращающим таблицу с одним столбцом (например, ALL('Таблица')).
• Функцией-модификатором фильтра (например, ALL, ALLEXCEPT, FILTER, KEEPFILTERS, USERELATIONSHIP).

Принцип работы

CALCULATE изменяет контекст фильтрации, в котором вычисляется выражение. Контекст фильтрации — это совокупность всех активных фильтров, наложенных на модель данных в данный момент (например, фильтры из отчёта, среза, строки или столбца сводной таблицы). При вызове CALCULATE:

1. Создаётся новый контекст фильтрации. Исходный контекст фильтрации (внешний) сохраняется, но в него добавляются или заменяются фильтры, указанные в аргументах функции.
2. Выражение вычисляется в этом новом контексте. Результат возвращается.

Ключевая особенность: если фильтры, указанные в CALCULATE, относятся к тем же столбцам, что и внешние фильтры, то внешние фильтры переопределяются (заменяются) новыми. Это отличает CALCULATE от функции FILTER, которая лишь сужает набор строк, не заменяя существующие фильтры.

Пример работы

Предположим, есть таблица «Продажи» со столбцами «Дата», «Товар», «Сумма». В отчёте установлен фильтр по году «2023». Мера Общая сумма продаж = SUM('Продажи'[Сумма]) вернёт сумму только за 2023 год.

Мера: `` Сумма за 2022 = CALCULATE( SUM('Продажи'[Сумма]), 'Продажи'[Год] = 2022 ) `` В этом случае, несмотря на внешний фильтр по 2023 году, CALCULATE заменит его на фильтр по 2022 году, и мера покажет сумму продаж за 2022 год.

Применение

Динамические вычисления

CALCULATE используется для создания мер, которые вычисляют значения в изменяющихся условиях, не зависящих от текущих фильтров отчёта. Например, расчёт доли от общего итога, сравнение с предыдущим периодом или вычисление скользящего среднего.

Работа с временными периодами

В сочетании с функциями временной аналитики (такими как DATEADD, DATESYTD, SAMEPERIODLASTYEAR) CALCULATE позволяет легко вычислять показатели за прошлые периоды, нарастающие итоги и другие временные агрегаты.

Пример: `` Продажи за прошлый год = CALCULATE( SUM('Продажи'[Сумма]), SAMEPERIODLASTYEAR('Календарь'[Дата]) ) ``

Условные агрегации

С помощью CALCULATE можно вычислять суммы только для определённых категорий, игнорируя или заменяя фильтры по другим категориям.

Пример: `` Продажи только по товару "А" = CALCULATE( SUM('Продажи'[Сумма]), 'Товары'[Наименование] = "А" ) ``

Модификация активных связей

Функция USERELATIONSHIP внутри CALCULATE позволяет временно активировать неактивную связь между таблицами, что даёт возможность вычислять меры на основе альтернативных связей (например, по дате отгрузки вместо даты заказа).

Особенности и ограничения

• Переопределение фильтров. Как упоминалось, CALCULATE заменяет внешние фильтры по тем же столбцам. Это может приводить к неожиданным результатам, если не учитывать данный механизм.
• Взаимодействие с функциями ALL и ALLEXCEPT. Функции ALL и ALLEXCEPT внутри CALCULATE удаляют все или часть внешних фильтров, прежде чем применить новые. Это часто используется для вычисления общих итогов или долей.
• Производительность. Использование CALCULATE с большим количеством сложных фильтров (особенно с вложенными FILTER) может снижать производительность запросов. Рекомендуется по возможности использовать простые логические условия и табличные функции-модификаторы.
• Неявное преобразование контекста. CALCULATE является одной из немногих функций DAX, которая выполняет так называемое «преобразование контекста строки в контекст фильтра». Это позволяет использовать её внутри вычисляемых столбцов для получения агрегированных значений, основанных на текущей строке.

Примеры использования

Вычисление доли от общего итога

Мера: `` Доля от общих продаж = DIVIDE( SUM('Продажи'[Сумма]), CALCULATE( SUM('Продажи'[Сумма]), ALL('Продажи') ) ) ` Здесь ALL('Продажи')` внутри CALCULATE удаляет все фильтры с таблицы «Продажи», и знаменатель становится общим итогом по всем продажам.

Расчёт нарастающего итога

Мера: `` Нарастающий итог = CALCULATE( SUM('Продажи'[Сумма]), FILTER( ALL('Календарь'[Дата]), 'Календарь'[Дата] <= MAX('Календарь'[Дата]) ) ) `` Эта мера суммирует все продажи от начала периода до текущей даты в контексте отчёта.

Фильтрация по нескольким условиям

Мера: `` Продажи в Москве в 2023 = CALCULATE( SUM('Продажи'[Сумма]), 'Продажи'[Город] = "Москва", 'Продажи'[Год] = 2023 ) `` Оба фильтра применяются одновременно, заменяя любые внешние фильтры по этим столбцам.

Сравнение с FILTER

Хотя FILTER также может использоваться для фильтрации таблиц, между ней и CALCULATE есть принципиальное различие:

• FILTER возвращает таблицу, отфильтрованную по заданному условию, но не изменяет контекст фильтрации для внешних вычислений. Она работает как итератор, перебирая строки.
• CALCULATE изменяет контекст фильтрации, в котором вычисляется выражение, и может заменять или дополнять существующие фильтры.

На практике FILTER часто используется внутри CALCULATE для создания сложных условий фильтрации, которые невозможно выразить простым логическим выражением.

Источники

• Microsoft Learn: функция CALCULATE (DAX)
• Руководство по языку DAX (SQLBI) — Marco Russo, Alberto Ferrari
• «Definitive Guide to DAX» (Marco Russo, Alberto Ferrari)