DAX - Контекст вычисления. Разбор ошибок реального примера.
Задача, посчитать сезонность помесячно по каналам сбыта. Создала таблицу для расчета сезонности - все чудесно отрабатывает. Теперь ТЗ немного обновилось - есть необходимость вывести фильтр по годам, с помощью которого определять базу для расчета сезонности (2-3-4-... лет). В расчетную таблицу не можем "забирать" значения из фильтра. Решила создать меру (по логике таблицы), но на выводе значения не отрабатывает - почему?
К вопросу был приложен хорошо подготовленный пример: как есть и как надо - поэтому решил разобрать здесь, где и какие допущены ошибки, как это исправить и как это работает.
Исходный пример можно скачать по ссылке.
Для начала посмотрим на вычисляемую таблицу и код, с помощью которого она была создана:
В рамках данной статьи не будем разбирать чистоту данного кода, сделаю только одно замечание: если столбец не несёт смысловой нагрузки, то не стоит его использовать - в первой переменной таблица группируется по столбцу 'спрКалендарь'[Месяц], который далее не используется, так как здесь достаточно номера месяца 'спрКалендарь'[MonthNumber].
А теперь сравним с созданной мерой:
Как видно на рисунке выше, добавилась ещё одна переменная table8. И, так как это мера, а мера должна возвращать скалярное значение, а не таблицу, после ключевого слова RETURN используется агрегирующая функция MINX для вывода единственного значения. В остальном же код остался прежним. И в этом кроется основная ошибка, которая приводит к неверным расчётам уже в первой переменной.
Когда мы создаём вычисляемую таблицу, у нас нет контекста вычисления - на таблицу sales не действуют никакие фильтры. Напротив, когда мы используем меру в визуальном элементе - возникает контекст вычисления: срезы и другие визуальные элементы, а также строки и столбцы визуального элемента, в котором вычисляется мера, становятся фильтрами.
Чтобы проверить работу контекста, изменим в мере и в вычисляемой таблице вывод конечного значения. Для меры просто посчитаем количество строк в первой переменной, в которую получаем таблицу - для этого воспользуемся функцией COUNTROWS, а в вычисляемой таблице просто вернём эту переменную.
А теперь посмотрим на вычисляемую таблицу:
Разница между результатом в мере и в вычисляемой таблице очевидна - вне контекста вычисления таблица из первой переменной содержит 180 строк, тогда как под воздействием фильтров мы получаем всего три строки.
Для того, чтобы в мере исключить влияние контекста и получить всю таблицу, добавим в выражение первой переменной функции ALL, поместив в неё таблицу sales.
Как видно на скриншоте, теперь переменная table1 в мере и в вычисляемой таблице возвращает одинаковый результат - таблицу из 180 строк.
Посмотрим как изменилось конечное значение меры:
Значение изменилось, но теперь в каждой ячейке показывает минимальное значение по строке. Здесь уже скорее всего просто невнимательность при написании кода - в выражении переменной table8 не указано к какому столбцу применить фильтр по месяцу.
И смотрим на итоговый результат:
Только понимая - где, когда и как возникает контекст вычисления, мы можем получить желаемый результат. И порой для этого достаточно всего одной функции)))
PS Тот же самый результат можно получить и более лаконичным способом, при этом не считывая полностью таблицу фактов, а она может быть очень большой, что негативно скажется на производительности.
PPS Файл с готовыми формулами по ссылке