Учебники

Excel DAX – выполнение сложных расчетов

Формулы DAX могут выполнять сложные вычисления, которые включают настраиваемые агрегации, фильтрацию и использование условных значений. Вы можете сделать следующее с DAX –

  • Создайте пользовательские расчеты для сводной таблицы.
  • Примените фильтр к формуле DAX.
  • Удалить фильтры выборочно, чтобы создать динамическое соотношение.
  • Используйте значение из внешнего цикла.

Создание пользовательских вычислений для сводной таблицы

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

РАСЧЕТ Функция

РАСЧЕТ (<выражение>, [<фильтр1>], [<фильтр2>]…)

Функция CALCULATE оценивает данное выражение в контексте, который изменен нулем или более указанными фильтрами.

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

пример

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

  • Определите вычисляемое поле – процент количества медалей, как показано на следующем снимке экрана.

Определите вычисляемое поле – процент количества медалей, как показано на следующем снимке экрана.

Расчетные поля

С помощью этой формулы DAX все строки в таблице Results учитываются в функции CALCULATE с фильтром, содержащим функцию ALL. Таким образом, у вас есть общее количество в знаменателе.

Ваша сводная таблица будет такой, как показано на следующем снимке экрана.

заголовки строк

На приведенном выше снимке экрана «Страна» отфильтрована в США, и в сводной таблице отображаются 18 лучших значений. Далее вы можете динамически фильтровать значения в сводной таблице. Тем не менее, расчеты будут правильными по пользовательской формуле DAX, которую вы использовали.

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

Фильтрация данных в формулах

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

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

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

пример

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

Фильтрация данных

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

Скриншот рассчитанного поля

Как вы можете видеть, значения в сводной таблице справа с новым вычисляемым полем совпадают со значениями в сводной таблице слева с явным применением фильтра по полю сезона.

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

Подробнее об этих функциях DAX см. Главу «Функции фильтра DAX».

Добавление и удаление фильтров динамически

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

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

Использование значения из внешнего цикла

Вы можете использовать значение из предыдущего цикла при создании набора связанных вычислений с помощью функции DAX EARLIER. Эта функция DAX поддерживает до двух уровней вложенных циклов.