РАСЧЕТ и фильтрация контекста в DAX, объяснение в шестнадцати строках

1. Что такое контекст фильтра, в одном предложении

Перед определением: ты ставишь меру Total Revenue = SUM(Orders[Revenue]) в таблицу, разделенную по продуктам. Рядом с «Столом» отображается 5060, а рядом со «Стулом» — 3230. Скажите, чем отличаются эти два числа, учитывая, что в показателе никогда не упоминается Продукт.

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

Таким образом, мера не имеет фиксированного ответа. SUM(Orders[Revenue]) составляет 9890 без каких-либо фильтров, 5060 в строке «Стол», 3040 в восточной строке и 1760 в ячейке, где пересекаются «Стол» и «Восток». Одна формула, один смысл и разное число в каждой ячейке, которая является всей точкой меры и покрыта с другой стороны в Столбцы против мер.

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

2. Правило: добавлено или перезаписано

Перед правилом: ты пишешь CALCULATE([Total Revenue], Products[Product] = "Desk") и поместите его в таблицу, нарезанную по продуктам. Прежде чем читать дальше, предскажите, что появится в строке «Стул».

5060, то же, что и строка «Стол», и это не ошибка. На справочной странице Майкрософт правило изложено в двух строках, и его стоит читать медленно:

В строке «Стул» «Продукт» уже находится в контексте фильтра, ограниченном «Стул». Ваш фильтр называет тот же столбец, поэтому он перезаписывает: «Продукт теперь доступен только для рабочего стола». Мера запускается и возвращает 5060 Рабочий стол, и это происходит в каждой строке таблицы, поскольку фильтр Продукт в каждой строке перезаписывается одним и тем же фильтром.

Скажите вслух, почему в итоговой строке также указано 5060, а не 9890. В итоговой строке нет собственного фильтра товаров, поэтому перезаписывать нечего. Вместо этого добавляется фильтр «Рабочий стол», и ответом является итоговое значение «Рабочий стол». То же правило, но на этот раз его «добавленная» ветвь.

Total Revenue = SUM ( Orders[Revenue] )

Desk Revenue =
CALCULATE ( [Total Revenue], Products[Product] = "Desk" )

3. Один и тот же размер в двух визуалах

Перед столом: возьмите то же самое Desk Revenue измерьте и поместите в таблицу, нарезанную Регион вместо. Предскажите, будет ли каждая строка снова отображать 5060.

Они этого не делают, и в этот момент правило перестает быть фактом и становится полезным.

Здесь визуальные фильтры «Регион» и меры фильтров «Продукт». Разные столбцы, поэтому ничего не перезаписывается и применяются оба фильтра. 1760 Иста — это два кассовых заказа Иста, 660 плюс 1100. А четыре региональных показателя в сумме дают 5060, что является проверкой того, что оба фильтра действительно действуют.

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

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

4. KEEPFILTERS, если вам нужно и то, и другое.

До исправления: в таблице продуктов вы на самом деле хотели, чтобы в строке «Стулья» отображался доход от столов и стульев, что представляет собой ничто, а не все столы. Скажите, что вам нужно РАССЧИТАТЬ, чтобы сделать по-другому.

Вам нужно, чтобы он пересекался, а не заменялся, и есть функция, вся работа которой заключается в этом. Майкрософт перечисляет KEEPFILTERS среди модификаторов фильтров с описанием «Добавить фильтр, не удаляя существующие фильтры в тех же столбцах».

Desk Revenue Kept =
CALCULATE ( [Total Revenue], KEEPFILTERS ( Products[Product] = "Desk" ) )

В строке «Стул» теперь запрашиваются строки, являющиеся одновременно «Стул» и «Стол». Их нет, мере нечего суммировать, и DAX возвращает пустое значение, а не ноль. Пустые строки по умолчанию скрыты в большинстве визуальных элементов, что обычно и является тем, что вам нужно.

Какое из двух правильных, полностью зависит от вопроса. «Сколько приносят письменные столы, независимо от того, о чем идет речь?» — это первая мера, и это действительно то, что вам нужно в качестве знаменателя акции. «Какая часть этого ряда занимает парты?» это второй. Решить, какое предложение вы имели в виду, — это работа; функция - это просто переключатель.

5. Удаление фильтров: процент от общего числа

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

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

Revenue % of Total =
DIVIDE (
    [Total Revenue],
    CALCULATE ( [Total Revenue], REMOVEFILTERS ( Orders[Region] ) )
)

Три заметки, которые сэкономят время позже. Использовать DIVIDE а не косую черту, потому что она возвращает пустое значение в нулевом знаменателе вместо ошибки. Удалите фильтры из конкретного столбца, по которому выполняется срез, а не из всей таблицы, иначе срез в другом столбце перестанет работать. И ALL в этой позиции выполняет ту же работу, что и REMOVEFILTERS; В собственном примечании Майкрософт говорится, что если ваш инструмент поддерживает REMOVEFILTERS, используйте его, поскольку ALL выполняет функцию возврата таблицы и ее легче неправильно прочитать.

Вариант, который стоит знать, ALLSELECTED, который удаляет фильтры, поступающие изнутри визуального элемента, но учитывает срезы и фильтры страниц. Проценты, построенные с помощью REMOVEFILTERS, всегда дают 100 процентов всего; проценты, созданные с помощью ALLSELECTED, добавляют к 100 процентам того, что пользователь выбрал в данный момент. Оба являются оправданными, и это разные отчеты.

6. Переход контекста, другое задание CALCULATE

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

Он преобразует контекст строки в контекст фильтра. На странице Майкрософт ясно сказано: «Использование функции CALCULATE без фильтров позволяет достичь определенного требования. Оно преобразует контекст строки в контекст фильтра».

Контекст строки — это то, что у вас есть, когда вы находитесь в одной строке: внутри вычисляемого столбца или внутри итератора, такого как SUMX. Это позволяет вам читать значения этой строки. Это делает нет фильтровать что угодно, и этот факт удивляет людей, потому что кажется, что так и должно быть.

Вот демонстрация. Добавьте вычисляемый столбец в таблицу «Продукты» из трех строк.

-- calculated column on Products, no CALCULATE
Revenue Wrong = SUM ( Orders[Revenue] )

-- calculated column on Products, with CALCULATE
Revenue Right = CALCULATE ( SUM ( Orders[Revenue] ) )

Без команды РАСЧЕТ вы находитесь в строке «Стол» и видите слово «Стол», и ничего не фильтруется, поэтому сумма равна всей таблице: 9890 в каждой строке. При использовании CALCULATE строка, в которой вы находитесь, становится фильтром «Продукты», этот фильтр перемещается вниз по отношению к «Заказам», и вы получаете 5060.

То же самое происходит внутри SUMX, и есть один ярлык, который стоит запомнить: когда вы ссылаетесь на меру модели внутри контекста строки, переход происходит автоматически. Майкрософт заявляет об этом прямо: «Когда вы используете показатель модели в контексте строки, переход контекста происходит автоматически». Поэтому SUMX(Products, [Total Revenue]) дает 9890, а не 29670. Ссылка на меру выполняет невидимый ВЫЧИСЛЕНИЕ.

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

7. Чего не может содержать логический аргумент фильтра

До правил: Products[Product] = "Desk" является логическим фильтром. Угадай, что произойдет, если ты напишешь Orders[Revenue] > [Average Order] вместо этого, где правая часть является мерой.

Это ошибки. На странице Майкрософт перечислены ограничения на логическое выражение фильтра, и они строгие:

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

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

Above Average Revenue =
VAR AvgOrder = AVERAGE ( Orders[Revenue] )       -- 618.125
RETURN
    CALCULATE (
        [Total Revenue],
        FILTER ( Orders, Orders[Revenue] > AvgOrder )
    )
-- 6,475, across the 8 orders above the average

Из этого стоит извлечь две вещи. VAR вычисляет среднее значение один раз во внешнем контексте фильтра, прежде чем FILTER начнет итерацию, что почти всегда именно то, что вы имели в виду. А FILTER — это табличный фильтр, а не логический, поэтому он заменяет фильтры всей таблицы в столбцах, которых он касается, поэтому FILTER(Orders, ...) обработка большой таблицы фактов медленнее, чем логический фильтр, и ее следует нацеливать на наименьшую подходящую таблицу.

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

Крайние случаи, которые застают людей врасплох

Шесть, каждая из которых стоила кому-то дня.

Два фильтра в одном столбце в одном РАСЧЕТ. Они комбинируются с помощью AND, поэтому Products[Product] = "Desk", Products[Product] = "Chair" возвращает пустое значение, а не оба. Для «или-или» напишите один аргумент с ||или используйте IN { "Desk", "Chair" }.

Фильтрация таблицы фактов, когда вы имели в виду измерение. Orders[Product] = "Desk" и Products[Product] = "Desk" дайте тот же ответ здесь и ведите себя по-другому, когда продукт существует в измерении без заказов. Фильтровать измерение по умолчанию; для этого и нужны размеры, т.к. руководство по звездообразной схеме крышки.

Ожидается, что пробел и ноль будут вести себя одинаково. Мера без строк возвращает пустое значение, пустые строки скрыты большинством визуальных элементов, а 0 + BLANK() равен 0, пока BLANK() + BLANK() пусто. Если вам нужен видимый ноль, оберните его: [Measure] + 0.

ВСЕ на таблице, когда вы имели в виду один столбец. ALL(Orders) удаляет все фильтры в каждом столбце таблицы фактов, поэтому срез, установленный пользователем, игнорируется. Назовите столбец.

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

ФИЛЬТРАЦИЯ по таблице фактов на большой модели. Правильно и медленно. Он повторяет каждую строку. Отфильтруйте измерение или используйте логический фильтр, если только условие действительно не требует строк фактов.