Примеры разберём вот на таких данных - таблица Sales со сделками некоторых менеджеров в некоторых магазинах, и себестоимостью этих сделок:
Наш арсенал на сегодняшний пример - основные функции DAX для изменения контекста:
ALL – возвращает все строки в таблице или все значения в столбце, не учитывая применяемые фильтры.
ALLSELECTED – удаляет фильтры контекста из столбцов и строк в текущем запросе, сохраняя все остальные фильтры контекста или явные фильтры.
ALLEXCEPT – удаляет все фильтры контекста в таблице, за исключением фильтров, применённых к указанным столбцам.
И функция, которая позволяет применять вышеописанные: CALCULATE - вычисляет выражение в изменённом контексте фильтра.
Вот такое вычисление SUM(Sales[Income]) даст результат в соответствии с тем контекстом, который актуален для каждого конкретного визуального элемента. На скриншоте ниже во всех матрицах одна и та же мера:
Income sum = SUM(Sales[Income])
Мера не содержит каких-либо функций изменения контекста и подчиняется имеющемуся контексту:
Для изменения контекста необходимо вычисление “завернуть” в CALCULATE с указанием функции изменения контекста. CALCULATE в общем виде имеет следующие аргументы:
CALCULATE(
< Expression >,
< Filter1 >,
< Filter2 >,
…
)
С функциями изменения контекста вычисление может выглядеть, например, так:
CALCULATE(
SUM(Sales[Income]),
ALLSELECTED(Sales[Manager])
)
или так:
CALCULATE(
SUM(Sales[Income]),
ALLEXCEPT(Sales, Sales[Manager])
)
или вот так:
CALCULATE(
SUM(Sales[Income]),
ALL(Sales)
)
Функции изменения контекста имеют немного разный состав аргументов:
ALL(Table | Table[Column]) - в качестве аргумента выступает ничего, таблица или столбец (несколько столбцов), в которых необходимо убрать все фильтры. В ход вычисления идут все строки таблицы или все значения столбца.
ALLSELECTED(Table[Column], Table[Column1]...) - в качестве аргументов выступает один или несколько столбцов, с которых необходимо убрать фильтрацию. В ход вычисления идут все значения указанных столбцов. Фильтры по неуказанным столбцам сохраняются.
ALLEXCEPT(Table, Table[Column], Table[Column1]...) - принимает в качестве аргументов таблицу и перечень из одного или нескольких столбцов, по которым необходимо оставить фильтрацию. Имеет действие, обратное ALLSELECTED.
Посмотрим на практике, как это работает. Выведем на дашборд матрицу с доходами в разрезе магазинов и менеджеров:
Добавим меру - рассчитаем, какой процент от общего дохода магазина привнёс каждый менеджер. Логика её расчёта такова:
Доля дохода менеджера в доходе магазина = (доход менеджера в конкретном магазине) / (доход магазина).
Обратим внимание на контекст. В текущий момент нам интересны только два контекста: контекст менеджера и контекст магазина. Все вычисления, которые мы вынесем на вышеуказанную матрицу будут одновременно и в контексте менеджера, и в контексте магазина - потому что они оба присутствуют в матрице и влияют на вычисление. Числитель меры (доход менеджера в магазине) в данном контексте рассчитается верно. Тогда как знаменатель (доход магазина) - нет, потому что знаменатель должен быть рассчитан не в контексте каждого менеджера, а без привязки к менеджеру в принципе. Вывод - для расчёта знаменателя меры необходимо изменить контекст.
В данном примере мы можем воспользоваться одной из двух функций: ALLSELECTED или ALLEXCEPT. Они имеют противоположное действие - одна удаляет контексты, другая - оставляет.
Мера будет выглядеть следующим образом:
Part manager to store (ALLSELECTED) =
VAR manager_income = SUM(Sales[Income])
VAR store_income = CALCULATE(
SUM(Sales[Income]),
ALLSELECTED(Sales[Manager])
)
RETURN DIVIDE(manager_income, store_income, 0)
Либо так:
Part manager to store (ALLEXCEPT) =
VAR manager_income = SUM(Sales[Income])
VAR store_income = CALCULATE(
SUM(Sales[Income]),
ALLEXCEPT(Sales, Sales[Store])
)
RETURN DIVIDE(manager_income, store_income, 0)
Результат вычисления:
Числитель (доход менеджера в магазине) в обеих мерах вычисляется одинаково и результат выдаёт в соответствии с контекстом матрицы:
VAR manager_store_income = SUM(Sales[Income])
ALLSELECTED(Sales[Manager]) в знаменателе убирает контекст менеджера, вычисляя по всем менеджерам - даёт нам сумму дохода по магазину без деления по менеджерам.
Во втором случае ALLEXCEPT(Sales, Sales[Store]) в знаменателе, наоборот, оставляет контекст только магазина, убирая всё остальное, что тоже в нашем примере даёт нам сумму дохода по магазину без деления по менеджерам.
Вклад каждого менеджера в общий доход можно рассчитать с использованием функции ALL:
Part manager to all =
VAR manager_income = SUM(Sales[Income])
VAR all_income = CALCULATE(
SUM(Sales[Income]),
ALL(Sales)
)
RETURN DIVIDE(manager_income, all_income, 0)
Вопросы, комментарии? :)
