SWITCH TRUE в DAX: сегментация и полоса, которая никогда не появляется

1. Сколько стоит вложенный IF

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

Order Size =
IF (
    Orders[Revenue] < 500,
    "Small",
    IF (
        Orders[Revenue] < 800,
        "Medium",
        "Large"
    )
)

Это правильно, и на трёх полосах уже трудно читать. При пяти полосах это пять уровней отступов, закрывающие скобки накапливаются, а добавление полосы посередине означает повторное вложение всего, что находится под ней. Логика в порядке; форма – это проблема.

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

За вложение, выходящее за пределы читабельности, приходится платить реальную цену. Вложенный IF скрывает порядок условий внутри своей структуры, поэтому вопрос «какое условие проверяется первым» требует предварительного чтения. ПЕРЕКЛЮЧАТЕЛЬ помещает их в список сверху вниз, и порядок становится тем, что вы можете проверить, просканировав левый край. Четвертый раздел объясняет, почему это важно.

2. ВКЛЮЧИТЕ значение, простая форма

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

Простая форма SWITCH принимает выражение, а затем пары значения и результата с необязательным окончательным обобщением.

SWITCH ( <expression>, <value>, <result> [, <value>, <result> ] … [, <else> ] )

Product Line =
SWITCH (
    Products[Product],
    "Desk",  "Furniture",
    "Chair", "Furniture",
    "Lamp",  "Lighting",
    "Unclassified"
)

Прочтите как поиск: возьмите товар, найдите его в списке, верните то, что рядом. Последний аргумент сам по себе, не имеющий перед ним никакого значения, — это Еще: что возвращать, если ничего не найдено. Оставьте это, и в ссылке Майкрософт будет подробно описано, что происходит: «Если ни одно из значений не совпадает, а остальное не указано, возвращается BLANK».

Эта форма делает только равенство. Он сравнивает выражение с каждым значением и ищет точное совпадение, поэтому не может выразить «больше 500». Для этого вам понадобится форма в следующем разделе, которую вы будете использовать чаще всего.

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

3. ПЕРЕКЛЮЧИТЕ TRUE для условий вместо значений.

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

Помещать TRUE(). Затем каждая позиция «значения» содержит условие, которое оценивается как Верно или Неверно, и SWITCH сравнивает его с TRUE. Первое условие, которое является истинным, побеждает. В справочнике Майкрософт прямо упоминается этот шаблон: «Обычно эта функция используется для установки первого параметра в значение TRUE».

Order Size =
SWITCH (
    TRUE (),
    Orders[Revenue] < 500, "Small",
    Orders[Revenue] < 800, "Medium",
    "Large"
)

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

Вот что он выдает по нашим шестнадцати заказам в виде расчетного столбца, сгруппированного.

При любой сегментации стоит провести две проверки, и они требуют одного взгляда. В сумме их количество составит 16 — количество строк, с которых вы начали, поэтому ни один заказ не был удален. А выручка составит 9890, так что ни один заказ не будет засчитан дважды. Сегментация, которая не прошла ни одну проверку, имеет пробел или перекрытие в своих условиях.

Скажите вслух, почему состояние полосы «Среднее» записано как < 800 скорее, чем >= 500 && < 800. Это потому, что все, что меньше 500, уже было перехвачено линией выше и никогда не достигнет этой. В этом вся экономика паттерна, и именно это делает возможным следующий раздел.

4. Ошибка упорядочивания действительных чисел.

До ошибки: поменяйте местами первые две строки этой меры, так что < 800 тестируется перед < 500. Прежде чем читать дальше, предскажите, что произойдет с Малой группой.

Оно ничего не получает. Не ошибка, не предупреждение: ноль строк, навсегда.

Order Size Broken =
SWITCH (
    TRUE (),
    Orders[Revenue] < 800, "Medium",     -- catches everything under 800
    Orders[Revenue] < 500, "Small",      -- unreachable
    "Large"
)

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

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

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

Теперь полосы можно рассматривать в соответствии с их собственными определениями. Смолл достигает максимума в 480, ниже порога в 500. Средне работает с 510 по 765 внутри своего окна. Большой начинается с 850. Запустите это один раз, когда вы пишете сегментацию, и ошибка упорядочивания не может ее пережить.

Теперь представьте себе самый длинный SWITCH или вложенный ЕСЛИ. Сканируйте его условия сверху. Каждый из них уже, чем тот, что под ним, или кто-то добавил полосу позже, не перемещая ее?

5. Столбец или показатель: где живет сегмент

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

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

Различие такое же, как и с другой стороны. Столбцы против мер, и решается одним вопросом: зависит ли ответ от того, что отфильтровал зритель? Размер нашего заказа не имеет значения. Порядок 440 мал, независимо от того, нажимает ли зритель, поэтому это столбец.

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

Rep Tier =
SWITCH (
    TRUE (),
    [Total Revenue] >= 3000, "Top",
    [Total Revenue] >= 2000, "Middle",
    "Building"
)

Прочтите весь ряд, потому что это то, что цепляет людей. Без повторного фильтра, [Total Revenue] составляет 9890, что превышает 3000, поэтому в качестве меры указано «Верх». Это арифметически правильно и бессмысленно как ярлык для всей компании. Любая мера, возвращающая категорию, требует рассмотрения общей строки и часто подавляется с помощью проверки, например IF ( HASONEVALUE ( Reps[Rep] ), <the switch> ).

6. Бандинг по мере и отключенный стол

Перед проблемой: вам нужна гистограмма с полосами размера заказа на оси, где полосы основаны на показателе, а не на сохраненном столбце. Скажите, почему мерка из последнего раздела не пойдет по оси.

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

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

Bands =
DATATABLE (
    "Band",  STRING,
    "Sort",  INTEGER,
    "Min",   INTEGER,
    "Max",   INTEGER,
    {
        { "Small",  1,   0,  499 },
        { "Medium", 2, 500,  799 },
        { "Large",  3, 800, 99999 }
    }
)

Помещать Bands[Band] на оси, отсортировано по Bands[Sort], затем напишите меру, которая подсчитывает заказы, попадающие в ту строку полосы, в которой она находится в данный момент.

Orders in Band =
VAR Lo = MIN ( Bands[Min] )
VAR Hi = MAX ( Bands[Max] )
RETURN
    CALCULATE (
        COUNTROWS ( Orders ),
        FILTER ( ALL ( Orders[Revenue] ),
                 Orders[Revenue] >= Lo && Orders[Revenue] <= Hi )
    )
-- 6, 6, 4

Две вещи помогают этому работать. Таблица диапазонов не имеет связи, поэтому ее строки не фильтруют заказы самостоятельно, а MIN и MAX прочитайте из него пороговые значения текущей строки. И ALL(Orders[Revenue]) сначала очищает любой существующий фильтр дохода, поэтому каждая полоса учитывается по всему столбцу, а не по уже ограниченной оси. Это РАСЧЕТНАЯ техника от контекстное руководство фильтра делает именно то, для чего он был создан.

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

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

7. Типы данных, пробелы и ветка Еще

Перед правилами: вы пишете ПЕРЕКЛЮЧАТЕЛЬ, где один результат — строка, а другой — число. Предскажите, запустится ли он.

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

Еще три поведения, которые стоит знать, прежде чем они будут стоить вам дня.

Ни одно совпадение и никакое другое не дает ПУСТО. Обычно это не то, что вам нужно для сегментации, поскольку строки с пустыми метками скрыты многими визуальными элементами, а заказы внутри них исчезают из отчета, хотя все еще учитываются в общем итоге. Всегда пишите Еще, даже если там написано «Неклассифицировано», потому что явный сегмент, который вы видите, лучше молчаливого, который вы не можете видеть.

Пустой ввод соответствует неправильному диапазону. В DAX пустое число при числовом сравнении сравнивается с нулем, поэтому заказ без дохода удовлетворяет требованиям. < 500 и приземляется в Смолл. Если пустое значение означает «неизвестно», а не «ноль», сначала проверьте его: поместите ISBLANK(Orders[Revenue]), "Unknown" как самое первое условие, где его узость приносит ему первое место.

Условия могут сочетаться. Каждое условие представляет собой обычное логическое выражение, поэтому && и || работа: Orders[Revenue] >= 800 && RELATED ( Products[Category] ) = "Furniture", "Large furniture". Просто помните, что сложное условие обычно более строгое, поэтому оно выше простых.

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

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

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

Перекрытие порогов. <= 500 затем >= 500 помещает 500 только в первую полосу, что оправдано и часто не соответствует тому, что имелось в виду. Напишите полосы как «под X» до конца, чтобы границы не могли перекрываться по конструкции.

Сортировка групп по алфавиту. Большой, Средний, Маленький по оси. Добавьте столбец числовой сортировки и установите сортировку по столбцу точно так же, как и в случае с названиями месяцев.

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

ПЕРЕКЛЮЧИТЕ меру без защиты HASONEVALUE. В итоговой строке оценивается показатель без фильтра, который почти всегда попадает в верхнюю полосу и помечает всю компанию как «Топ».

Пороги взял из воздуха. 500 и 800 — круглые цифры, а не выводы. Если полосы должны что-то означать, извлеките их из распределения, а это и есть то, что нужно. Выбор порогов из данных и процентили предназначены для.