Функция CALCULATE DAX: проблемы и решения

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

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

Оглавление

• Обзор функции CALCULATE DAX
• ВЫЧИСЛИТЬ Формулу DAX
• Код DAX для среднего значения по категории
• В чем проблема?
• Более простое решение
  • Использование функции SUMMARIZE
  • Использование функции GROUPBY
• Сравнение результатов: функции SUMMARIZE, GROUPBY и CALCULATE DAX
• Заключение

Обзор функции CALCULATE DAX

Несколько месяцев назад я представил LuckyTemplates на саммите . Это называлось контркультурой DAX, где я представил несколько альтернативных взглядов на DAX. 

Один из основных относится к использованию функции ВЫЧИСЛИТЬ. Я говорил о том, почему вам не следует использовать CALCULATE, особенно если вы новичок в DAX. 

CALCULATE — действительно сложная функция для понимания и использования. Он делает определенные предположения о вашей модели данных и может доставить вам массу неприятностей. 

Я также изучил быструю оценку Microsoft для Running Total, потому что она не работает в ситуациях с одной таблицей, и для ее правильной работы требуется схема «звезда».

Итак, я показал более простую альтернативу, которая работает независимо от того, является ли она звездообразной схемой или моделью с одной таблицей, без необходимости в CALCULATE. 

ВЫЧИСЛИТЬ Формулу DAX

В этом блоге мы рассмотрим быстрый показатель под названием « Среднее значение по категории» . Если вы работаете в LuckyTemplates Desktop, начните с перехода на панель «Поля» и создания новой быстрой меры .

Во всплывающем окне «Быстрая мера» выберите «Среднее значение по категории» в раскрывающемся списке «Расчет» . Потом перетащить ? Значение в текстовом поле «Базовое значение» и «Месяц» в поле «Категория» .

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

По оси X отложены четверти — 1, 2, 3 и 4 четверти. 

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

Наша модель данных для этого очень проста. У нас есть таблица с Dates , случайно сгенерированными значениями и Month , MonthSort и Quarters в отдельных столбцах. 

И у нас есть то же самое в нашей таблице дат с таблицей календаря, месяцем, сортировкой месяца и кварталом. Это означает, что мы можем выбрать квартал или месяц либо из таблицы дат, либо из первой таблицы. 

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

В этих изображениях мы используем квартал из первой таблицы…

 …в то время как этот использует квартал из нашей таблицы дат. Это работает в любом случае. 

Но проблема со средним значением по категории — это просто код DAX. 

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

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

Когда я их вижу, меня это поражает, потому что это сразу указывает на то, что они понятия не имеют, что делают с функцией ВЫЧИСЛИТЬ. Поэтому я рекомендую просто держаться подальше от него. 

Код DAX для среднего значения по категории

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

В коде также используются , с чем я не согласен. Они никогда не должны использовать VALUES, потому что разные значения имеют тенденцию возвращать пустую строку, если есть несоответствующая строка. Это может доставить вам много неприятностей, но это тема для другого видео.

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

Они также оставляют функцию CALCULATE для выполнения в контексте KEEPFILTERS. Из-за того, как работает функция AVERAGEX, она берет второе выражение и выполняет его в контексте первого выражения. 

В чем проблема?

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

Быстрые измерения — отличная идея, например: «Давайте напишем для вас несколько общих показателей с различными вычислениями, потому что вы новичок в DAX и не знаете всего, что нужно знать о DAX». Но зачем им строить их такими странными и сложными способами? 

Как кто-то новичок в DAX может взглянуть на это и понять, что происходит, когда даже, вероятно, профессионалы DAX немного ломают голову над этим? 

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

Более простое решение

Использование функции SUMMARIZE

Как я упоминал ранее, есть лучший и более простой способ сделать это.

Сначала создайте табличную переменную, используя VAR_Table . Затем мы будем использовать , чтобы взять таблицу, суммировать ее по месяцам, создать столбец Value и суммировать наши значения.

Наконец, мы возьмем среднее значение наших значений, используя функцию AVERAGEX . 

Этот код проще и намного логичнее. Он не включает CALCULATE, который вам в любом случае не нужен. 

Вы можете встретить статью в блоге, критикующую SUMMARIZE. В блоге говорится, что внутренняя работа SUMMARIZE довольно сложна.

Это также предполагает, что вы можете попасть в беду в одном конкретном случае, но вы никогда не столкнетесь с ней. Это произойдет только в том случае, если ваш расчет включает очень сложный расчет и очень большую таблицу. Только тогда СУММИРОВАНИЕ даст вам сомнительные результаты. 

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

Использование функции GROUPBY

Ни у кого нет проблем с использованием функции GROUPBY, поэтому давайте научимся использовать и ее. Снова создайте переменную таблицы, используя VAR_Table. Затем мы GROUPBY Month и создаем столбец Value.

Затем мы применяем с помощью CURRENTGROUP , как работает GROUPBY. Давайте просуммируем наше значение и снова возьмем его СРЗНАЧ . 

Наш код должен выглядеть так.

Сравнение результатов: функции SUMMARIZE, GROUPBY и CALCULATE DAX

Теперь давайте посмотрим, возвращают ли ярлыки те же результаты. 

Как вы можете видеть на изображениях ниже, как SUMMARIZE, помеченный как Better Average Per Category, так и GROUPBY, помеченный как Better Average Per Category 2, возвращают одинаковые числа. 

Обе наши формулы возвращают 3,4 тыс. для Q1, 3,6 тыс. для Q2, 3,4 тыс. для Q3 и 3,5 тыс. для Q4. И снова они работают в модели данных одной таблицы, используя Quarters для нашей таблицы.

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

Быстрые меры — как их использовать в моделях LuckyTemplates
Использование функции расчета внутри LuckyTemplates
Эффективные способы использования функции GROUPBY в LuckyTemplates

Заключение

Нам не нужно принуждать себя к извращенной логике контекста только для того, чтобы включить РАСЧЕТ в наши формулы. Просто используйте простые стандартные функции DAX, и вы сможете добиться того же. 

На самом деле, вероятно, в 80–90% случаев нет абсолютно никаких причин использовать CALCULATE для чего бы то ни было. Вместо этого вы можете использовать функции SUMMARIZE и GROUPBY, которые проще и логичнее.

Если вы хотите изучить этот файл PBIX, я уже опубликовал его в Галерее быстрых измерений и назвал файл Better Average Per Category . Просто прокрутите страницу вниз, чтобы найти файл PBIX, который вы можете загрузить, и поиграть с самим собой. 

Всего наилучшего,

Грег Деклер