Россия Нет

CALCULATE и CALCULATETABLE в Power BI и Power Pivot — самые важные функции языка DAX

 Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):


Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы разберем, пожалуй, самые важные и главные функции в языке DAX — это CALCULATE и CALCULATETABLE. Эти функции используются практически во всех формулах и вычислениях, которые мы составляем в Power BI или Excel (Powerpiwot).

А главные они потому, что это единственные функции в DAX, которые способны не то чтобы накладывать на выражения те или иные фильтры, так и полностью заменять или удалять предыдущие наложенные фильтры.

По всем свойствам обе функции CALCULATE и CALCULATETABLE полностью идентичны, и различаются лишь тем, что CALCULATE — работает с выражениями, возвращающими одно конкретное скалярное значение, а CALCULATETABLE — с табличными выражениями. Но, обо всем по порядку.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

 

DAX функция CALCULATE в Power BI и Power Pivot

CALCULATE () — вычисляет выражение, измененное внутренними фильтрами.

Синтаксис: CALCULATE (Выражение; Фильтр 1; Фильтр 2; …; Фильтр N), где:

  • Выражение — то выражение, которое нужно вычислить (обязательный параметр для CALCULATE, без него эта функция работать не будет)
  • Фильтр — условия фильтров (необязательный параметр, количество фильтров может быть от 0 до многих и все они сочетаются в режиме «и»)

Фильтры в CALCULATE не могут ссылаться на различные меры или вложенные функции CALCULATE. При этом, в условиях фильтров могут использоваться какие-либо другие функции DAX, вычисляющие одно скалярное значение или создающие запрос уточнения одного скалярного значения.

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

 

Пример использования функции CALCULATE

Для понимания функционирования и работы DAX формул на основе CALCULATE, разберем примеры ее работы в программе Power BI Desktop.

Имеется исходная таблица «Общие Продажи»:

 

Задача — вычислить сумму продаж только по первому отделу. Создадим в Power BI Desktop во вкладке «Моделирование» меру по следующему коду формулы с участием функций SUM и CALCULATE:

Сумма Продаж Отдел 1 = 
CALCULATE(
    SUM('ОбщиеПродажи'[Продажи]);
    'ОбщиеПродажи'[Отдел] = "Первый отдел"
)

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

Результатом выполнения этого кода формулы будет сумма продаж по первому отделу, равная 49000:

 

Причем созданный функцией CALCULATE фильтр, будет заменять любой другой фильтр, который может наложить пользователь во вкладке «Отчеты» в Power BI.

В этом можно убедиться на примере ниже, где на срезе «Отделы» установлен пользовательский фильтр «Второй отдел», но CALCULATE полностью заменяет его своим условием и формула высчитывает сумму опять же только по первому отделу, то есть сумма продаж равна 49000:

 

В качестве выражения в первом параметре CALCULATE можно рассчитывать очень многие формулы и вставлять туда какие-либо другие функции DAX, которые Вам понадобятся в конкретной ситуации.

Также, при этом, создавая хитрые переплетения фильтров из второго и последующих параметров CALCULATE, можно формировать очень сложные формулы и необычные вычисления в Power BI и Power Pivot.

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

Рассчитаем при помощи функции COUNT количество менеджеров, у которых сумма продаж больше 20000. При условии, что менеджеры не должны принадлежать первому отделу. Условия в этой формуле мы зададим при помощи фильтров CALCULATE:

Количество Менеджеров = 
CALCULATE (
    COUNT ('ОбщиеПродажи'[Продажи]);
    'ОбщиеПродажи'[Отдел] <> "Первый отдел";
    'ОбщиеПродажи'[Продажи] > 20000
)

В этой формуле COUNT считает количество менеджеров, находясь под действием сразу двух фильтров от CALCULATE, где столбец [Отдел] неравен значению «Первый отдел» и одновременно с этим в столбце [Продажи] все значения должны быть более 20000. Как итог, формула рассчитает количество менеджеров, равное 2:

 

Теперь давайте рассмотрим собрата CALCULATE — функцию CALCULATETABLE, которая, как я писал в самом начале по всем своим свойствам полностью аналогична первой функции и различия между ними в том, что первая функция работает с единичными скалярными значениями, а вторая — с табличными выражениями.

 

DAX функция CALCULATETABLE в Power BI и Power Pivot

CALCULATETABLE () — вычисляет табличное выражение, измененное внутренними фильтрами.

Синтаксис: CALCULATETABLE (Табличное выражение; Фильтр 1; Фильтр 2; …; Фильтр N), где:

  • Табличное выражение — то табличное выражение, которое нужно вычислить (обязательный параметр для CALCULATETABLE, без него эта функция работать не будет)
  • Фильтр — условия фильтров (необязательный параметр, количество фильтров может быть от 0 до многих и все они сочетаются в режиме «и»)

Функцию CALCULATETABLE нельзя путать с функцией фильтра таблиц в DAX — FILTER. Эта функция просто фильтрует таблицы по заданным условиям фильтра. А CALCULATETABLE, в свою очередь, не просто фильтрует, а заменяет или удаляет фильтры, что в итоге дает совершенно другой результат.

Рассмотрим небольшой пример формулы на основе CALCULATETABLE. В качестве исходной таблицы возьмем ту же таблицу по продажам менеджеров, которую брали в примерах выше:

 

В этом примере также рассчитаем сумму продаж, но только по второму отделу. В данном случае фильтры мы будем устанавливать функцией CALCULATETABLE, а сумму рассчитаем при помощи второй функции суммирования в DAX — SUMX (подробный разбор функции SUMX Вы можете прочитать в этой статье):

Сумма Продаж Отдел 2 = 
SUMX (
    CALCULATETABLE (
        'ОбщиеПродажи';
        'ОбщиеПродажи'[Отдел] = "Второй отдел"
    );
    'ОбщиеПродажи'[Продажи]
)

В данной формуле CALCULATETABLE возвращает таблицу, отфильтрованную по условию «Второй отдел», причем этот фильтр будет заменять любой другой пользовательский фильтр, наложенный на этот же столбец [Отдел]. А SUMX рассчитает по этой отфильтрованной таблице сумму по столбцу [Продажи]. В итоге, получится сумма продаж именно по второму отделу, равная 40500:

На этом, с разбором CALCULATE и CALCULATETABLE в Power BI и Power Pivot, в данной статье все.

Также, Вы можете изучить в этой статье формулу расчета в DAX количества новых, постоянных и потерянных клиентов с участием функций CALCULATE и CALCULATETABLE.

Помните, что BI аналитика, Power BI и язык функций и формул DAX, в частности, это действительно просто. Главное, разобраться во всем и не просто прочитать статью, но и повторить все самостоятельно на практике. Тогда результат не заставит Вас долго себя ждать!

Пожалуйста, оцените статью:

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1
(33 голоса, в среднем: 4.6 из 5 баллов)
[Экспресс-видеокурс] Быстрый старт в языке DAX

 
 
Антон БудуевУспехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
 
 
 
 

Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
 
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.

 
Понравился материал статьи?
Избранные закладкиДобавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

Автор статьи:
Категория: Язык функций и формул DAX для Power BI и Excel (Power Pivot)

Добавить комментарий

* Заполняя форму отправки комментария своими персональными данными (имя, email, сайт и др.), Вы автоматически подтверждаете свое согласие на обработку своих персональных данных


  1. Александр:

    Спасибо за статью, Антон

  2. Ярослав:

    Добрый день,

    подскажите пожалуйста, как просуммировать значения в столбце отвечающих условию содержания в них определенных символов?

    Статья Количество
    012 Иванов 5
    201 Петров 4
    204 Сидоров 3

    Мне нужно, чтобы посчитались только значения в столбце «Количество», для которых в столбце «Статья» содержаться символы «01»
    т.е. ответ должен быть 8

    заранее спасибо

    1. Антон Будуев:

      Здравствуйте, Ярослав.

      Посчитать можно так:

      Мера 21 = 
      CALCULATE(
          SUM('Таблица1'[Количество]);
          FILTER(
              'Таблица1';
              SEARCH("01";[Статья];1;0) <> 0
          )    
      )

      либо так:

      Мера 2 = 
      SUMX(
          FILTER(
              'Таблица1';
              SEARCH("01";[Статья];1;0) <> 0
          );
          [Количество]
      )
      
  3. Ольга:

    Здравствуйте,
    у меня есть два столбца — в одно адрес точки, а в другом название наименования.
    мне нужно определить сколько точек продает все наименования
    tt sku
    tt1 — sku1
    tt1 — sku2
    tt2 — sku1
    tt2 — sku2
    tt3 — sku1
    Ответ здесь 2 (tt1, tt2), на dax не получается.
    Думаю, надо для каждого тт посчитать кол-во наименований и если оно меньше, чем кол-во уникальных наименований, то не учитывать эту тт в подсчете кол-ва
    Помогите пожалуйста 🙂

    1. Антон Будуев:

      Добрый день, Ольга.

      Долго думать некогда было, написал код, который сразу пришел в голову. Возможно, данный код можно оптимизировать…
      Если исходить из данной таблицы
      Исходная таблица

      то, код будет таким, где unikal_sku = 2 — это количество уникальных наименований (в данном примере их 2 — sku1, sku2), если больше, то 2 меняем на другое число.

      Также, этот момент с unikal_sku = 2 можно автоматизировать так: unikal_sku = DISTINCTCOUNT(‘Таблица'[sku])


      Мера =
      VAR unikal_sku = 2
      RETURN

      COUNTX(
      GROUPBY(
      FILTER(
      'Таблица1';
      COUNTX(
      GROUPBY(
      FILTER('Таблица1';'Таблица1'[tt] = EARLIER('Таблица1'[tt]));
      'Таблица1'[sku]
      );
      'Таблица1'[sku]
      )
      >= unikal_sku
      );
      'Таблица1'[tt]
      );
      'Таблица1'[tt]
      )

      С учетом форматирования, код должен выглядеть так:
      форматирование кода

  4. Александра:

    Добрый день, может вы поможете мне в решении проблемы…

    Таблица1, которая заполняется по мере поступления денежных средств
    http://prntscr.com/ks2ty2
    Таблица, которая заполняется вручную, но потом в Power BI ее нужно дополнить на основании Таблица1:
    http://prntscr.com/ks2uts

    Добавляю новый столбец и пытаюсь вычислить полученные оплаты по объекту:
    http://prntscr.com/ks2vef
    Формула:
    Оплаты = CALCULATE(SUM(‘ ОПЛАТЫ'[Сумма]);’ ОПЛАТЫ'[Объект] = ‘ВСЕ_ОБЪЕКТЫ'[Объект])

    Т.е:
    CALCULATE(SUM(‘ ОПЛАТЫ'[Сумма]);
    просуммировать оплаты
    ОПЛАТЫ'[Объект] = ‘ ВСЕ_ОБЪЕКТЫ'[Объект]
    по объекту, который указан в объектах таблицы ВСЕ_ОБЪЕКТЫ

    ошибка: http://prntscr.com/ks2xnz

    если установлены связи, и объект прописан вручную в формуле
    CALCULATE(SUM(‘ ОПЛАТЫ'[Сумма]);’ ОПЛАТЫ'[Объект] = “Объект 1”)
    То напротив этого объекта считает оплаты….
    http://prntscr.com/ks2ytb

    П.С.: в екселе просто воспроизвела то, что происходит в Power BI

    ЗАДАЧА: что бы оплаты по каждому объекту суммировались и отображались в таблице ВСЕ_ОБЪЕКТЫ, которая частично заполняется вручную (в ексель), а потом должна дополняться в Power BI на основании таблицы ОПЛАТЫ …

    1. Антон Будуев:

      Здравствуйте. Ваше условие ОПЛАТЫ'[Объект] = ‘ВСЕ_ОБЪЕКТЫ'[Объект] в функции CALCULATE неверно. В данной функции в условии нельзя сравнивать один столбец с другим. Возможно только сравнение столбца с конкретным значением. Для того, чтобы вставить Ваше условие, воспользуйтесь функцией FILTER. В ней используйте Ваше условие. А затем этот FILTER уже вставить в CALCULATE. Это первое.
      Второе, опять же, сравнивать две связанные таблицы ОПЛАТЫ'[Объект] = ‘ВСЕ_ОБЪЕКТЫ'[Объект] так нельзя. Нужно использовать функцию RELATE, которая выводит значения из связанных таблиц

  5. Сергей:

    Антон, а скажите пожалуйста если формула calculate снимает все фильтры и работает в контексте строки, то что будет если попадется строка с полностью одинаковыми значениями ? Она не правильно отработает ?

    1. Антон Будуев:

      Сергей, calculate — не снимает все фильтры, она способна изменять контекст, в том числе и удалять фильтры (при помощи группы функций ALL).

      На счет Вашего вопроса — тут нужно уточнение… не совсем понятно, что за «одинаковые значения»? Если имеется ввиду, что в таблице есть полные дубликаты строк, то в результате переноса контекста строки, будут возвращены все дубликаты строк.

      Рекомендую записаться на курс https://biprosto.ru/kurs/dax-kurs-free В обоих частях курса мы будем детально разбирать DAX, функцию CALCULATE и все контексты DAX’а.