Глава 15. IF(), SWITCH(), BLANK() и другие DAX-функции условий

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую начать сначала.

Предыдущая глава       Содержание    Следующая глава

Добавим условную логику в наши DAX-формулы. Рассмотрим рост по сравнению с предыдущим годом (Year-Over-Year, YOY) из предыдущей главы:

Значение за 2001 год возвращает ошибку, потому что продажи в прошлом году [Total Sales DATEADD 1 Year Back] равны 0. Это действительно ошибка деления на 0.

Ris. 15.1. Oshibka CHislo dlya 2001 goda delenie na nol

Рис. 15.1. Ошибка #Число! для 2001 года: деление на ноль

Скачать заметку в формате Word или pdf, примеры в формате Excel

Ситуацию легко исправить с помощью интуитивно понятной функции IF():

Ris. 15.2. Teper mera vozvrashhaet 0 vmesto oshibki

Рис. 15.2. Теперь мера возвращает 0% вместо ошибки

Функция BLANK()

Мы можем еще улучшить отображение. 0% означает, что у нас был нулевой рост, тогда как на самом деле этот расчет вообще не имеет смысла для 2001 года. Поэтому вместо 0 мы можем вернуть функцию BLANK():

Ris. 15.3. Teper 2001 god prosto ne otobrazhaetsya

Рис. 15.3. Теперь 2001 год просто не отображается

Это очень полезный трюк. Запомните такое использование функции BLANK()!

Если мы добавим в сводную таблицу второе поле Значений, которое для 2001 года возвращает значение, отличное от нуля, строка за 2001 год вновь проявится:

Ris. 15.4. 2001 j god budet otobrazhatsya esli hotya by odna mera vozvrashhaet nepustoj rezultat

Рис. 15.4. 2001-й год будет отображаться, если хотя бы одна мера возвращает непустой результат

И наоборот, если вам не нравится отсутствие значений в каком-либо поле, вы можете настроить сводную, и отражать там нечто иное. Для этого активируйте сводную таблицу, перейдите на вкладку Анализ, и в левой части ленты кликните Параметры (или кликните правой кнопкой мыши на сводной и выберите Параметры сводной таблицы). Установите значения, которые следует отражать вместо ошибок и пропусков:

Ris. 15.5. Nastrojka svodnoj tablitsy dlya otrazheniya oshibochnyh i pustyh znachenij

Рис. 15.5. Настройка сводной таблицы для отражения ошибочных и пустых значений

Функция DIVIDE()

DIVIDE(<числитель>; <знаменатель> [; <значение, если знаменатель равен нулю>])

DIVIDE() – безопасное деление; третий аргумент является необязательным, и по умолчанию возвращает BLANK(), что удобно в большинстве случаев. Можно записать:

Элегантно, не правда ли?

Шаблон IF…THEN…ELSE полезен не только при делении, и пригодится во многих иных ситуациях. Для деления же мы рекомендуем использовать DIVIDE(), и забыть про проблему с нулевым знаменателем.

Шаблон IF(<test>; <DAX выражение>; BLANK()) мы также рекомендуем запомнить.

Функция ISBLANK

В Excel есть функция ЕПУСТО(). Она возвращает значение ИСТИНА, если ячейка пуста, и значение ЛОЖЬ в иных ситуациях. Функция нужна, когда мы хотим отличить пустую ячейку от ячейки, содержащей пробелы, или пустой текстовой строки ="", или строки с формулой, возвращающей пустое/нулевое значение.

Если обратиться к примеру выше, функция ISBLANK() может быть полезна, если мы хотим отличить ситуацию, когда [Total Sales DATEADD 1 Year Back] возвращает законный 0 (то есть, строки продаж имелись, но сумма столбца SalesAmount = 0; редко, но возможно). Поэтому большую часть времени мы просто проверяем "=0". Но когда вы хотите отличить 0 от BLANK(), ISBLANK() – это то, что вам нужно.

HASONEVALUE()

Excel позволяет стандартными средствами показать в сводной таблице долю от суммы по столбцу. А вот долю в каждой категории – нет. Создадим следующую меру:

И поместим ее в сводную таблицу вместе с [Total Sales]:

Ris. 15.6. Prodazhi i ih dolya v kategorii

Рис. 15.6. Продажи и их доля в категории

Возможно, вы сочтете, что все показанные значения 100,0% подытогов и общих итогов бесполезны. Чтобы подавить их можно использовать функцию HASONEVALUE(). Она проверяет, содержит ли контекст фильтра одно наименование (и возвращает ИСТИНА) или несколько, т.е., относится к промежуточным/общим итогам (и возвращает ЛОЖЬ).

Ris. 15.7. Promezhutochnye i obshhie itogi podavleny

Рис. 15.7. Промежуточные и общие итоги для меры [Subcat pst of Cat Sales] подавлены

HASONEVALUE() эквивалентно IF(COUNTROWS(VALUES())=1.

Мы могли бы отключить промежуточные и/или общие итоги на вкладке Конструктор на ленте. Но это также отключит подытоги/итоги для столбца [Total Sales]. А мы хотели сделать это только для столбца [Subcat pct of Cat Sales].

IF() на основе полей сводной таблицы

Выше мы использовали IF() для сравнения со значением меры. Но что, если мы хотим проверить, где мы «находимся» с точки зрения контекста фильтра? Например, мы хотим вычислить что-то немного по-другому для конкретной страны? Добавим в модель таблицу подстановки SalesTerritory. Она содержит столбец Country, который мы поместим в строки сводной таблицы для меры Продажи родителям [Sales to Parents]

Sales to Parents = CALCULATE([Total Sales]; Customers[NumberChildrenAtHome] > 0)

Ris. 15.8. Summa dlya Kanady ne vyzyvaet doveriya

Рис. 15.8. Сумма для Канады не вызывает доверия

Допустим, у нас есть сомнения в данных по столбцу [NumberOfChildren]. Возможно, то, как мы собираем данные в Канаде, делает это число ненадежным. А этот столбец лежит в основе расчета меры [Sales to Parents]. Поэтому для Канады, и только для Канады, мы хотим заменить эту меру другой мерой, [Sales to Married Couples]. Поэтому мы введем новую меру:

Функция VALUES() возвращает контекст фильтра, установленный в сводной таблице. Если вы находить в обычной ячейке, он возвращает одно значение. Если вы в строке подытогов/итогов, он возвращает несколько значений:

Ris. 15.9. Formula VALUESSalesTerritoryCountry vernet dlya a znachenie Canada

Рис. 15.9. Формула VALUES(SalesTerritory[Country]) вернет для (а) значение Canada; для (b) – Australia, Canada, France, Germany, United Kingdom, United States

Мы не можем непосредственно проверить условие IF(SalesTerritory[Country]). Оно нарушает правило для мер – не использовать голые столбцы в качестве аргументов мер. Ранее мы «заворачивали» столбец в какую-нибудь арифметическую функцию. Но, поскольку Country – это текстовая строка, нам нужно использовать какую-то иную функцию. Вот почему мы выбрали VALUES(). В итоге условие проверки выглядит так: IF(VALUES(SalesTerritory[Country])="Canada".

Если мы выполним тест IF(VALUES(…)) ="Canada" в ячейке, которая вернет более одного значения, мы получим ошибку. Поэтому нам нужно предварительно проверить, что мы находимся в ячейке с единственным значением страны. Для этого мы используем конструкцию IF(HASONEVALUE(SalesTerritory[Country])

Вот что мы в итоге получим:

Ris. 15.10. Dve mery otlichaetsya tolko dlya Kanady

Рис. 15.10. Две меры отличается только для Канады

Мы также убрали итоговое значение из столбца Sales to Parents Adj for Canada. Оно было бы не корректным, так как значение для Канады мы взяли из другого столбца.

Значение, возвращаемое VALUES(), не зависит от того, выводится ли поле в сводную таблицу

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

Ris. 15.11. CHto imenno vozvrashhaet mera VALUESProductsColor dlya vydelennoj yachejki

Рис. 15.11. Что именно возвращает мера VALUES(Products[Color]) для выделенной ячейки?

Для выделенной на рис. 15.11 ячейки, мера VALUES(Products[Color]) возвращает количество продуктов всех цветов: {"Black", "Blue", "Red", "Silver", "Yellow"}. Обратите внимание, что «Grey» и «NA» не возвращаются для этой ячейки. Но эти два цвета возвращаются для категории Accessories. Это связано с тем, что Category и Color (поля в строках) являются столбцами таблицы Products, что означает, что фильтр категорий влияет на допустимость цвета. Категория велосипеды "Bikes" фильтрует таблицу Products, а велосипеды в цветовой гамме "Grey" или "NA" отсутствуют.

Если мы удалим поле Color из сводной таблицы, что вернет мера VALUES(Products[Color])?

Ris. 15.12. Itogovye znacheniya po kategoriyam ne izmenyatsya

Рис. 15.12. Итоговые значения по категориям не изменятся

Независимо от того, было ли поле Color выведено в сводную или нет, выделенная нами ячейка С10 на рис. 15.11 не имела никаких фильтров, связанных с цветом.

VALUES() может возвращать уникальные значения

Мы использовали меру [Product Count] для определения числа продуктов в категории (всего или в той или иной цветовой гамме). Например (см. рис. 15.11), имеется 35 продуктов в категории Accessories, принадлежащих к 6 различным цветам. Для подсчета числа цветов в категории используем меру

[Color Values] = COUNTROWS(VALUES(Products[Color]))

Ris. 15.13. Mera Color Values vozvrashhaet tolko unikalnye znacheniya dlya tsveta

Рис. 15.13. Мера [Color Values] возвращает только уникальные значения для цвета

Характерно, что итоговая строка на рис. 15.13 возвращает значение 10, а не сумму цветов по категориям. Если убрать фильтр со столбца Category, разных цветов у всех продуктов будет 10.

SWITCH()

Если мы хотим проверить несколько условий, вложенные IF() – один из способов решения, но функция SWITCH() намного проще и прозрачнее. В модели данных мы добавили вычисляемый столбец, чтобы увидеть работу функции SWITCH() в действии, но ее можно использовать и в мерах (рис. 15.14).

Ris. 15.14. Funktsii SWITCH opredelyaet kontinent na osnove strany

Рис. 15.14. Функции SWITCH() определяет континент на основе страны

Вот как работает SWITCH(). Первый аргумент – проверяемое выражение. Начиная со второго аргументы SWITCH() работают парами: нечетный аргумент сравнивается с проверяемым выражением и в случае совпадения возвращает следующий за ним четный аргумент. Например, если проверяемое значение "United States", то возвращается "North America", если "France" –возвращается "Europe". Если вы заканчиваете SWITCH() "четным" аргументом, он рассматривается как "ELSE" и работает без пары. Т.е., если среди четных аргументов {"United States"; "Canada"; …; "United Kingdom"}, не найдется проверяемое выражение, то функция SWITCH() вернет "Rest of the World".

SWITCH TRUE()

Функция SWITCH() может быть еще более универсальной, когда в качестве первого аргумента используется TRUE(). Далее можно указать условия для сопоставления, а не точные значения. Например, добавим еще один вычисляемый столбец в таблицу Products для указания диапазона цен прайс-листа:

Ris. 15.15. Konstruktsiya SWITCH TRUE pozvolyaet ukazat usloviya dlya proverki

Рис. 15.15. Конструкция SWITCH TRUE() позволяет указать условия для проверки

Здесь, как и раньше, начиная со второго, аргументы SWITCH() работают парами. Однако вместо соответствия определенному значению они оценивают условие; если оно истинно, то выбирается парное значение. Например, если значение [List Price] < 100, то возвращается "$". Если вы заканчиваете SWITCH() "четным" аргументом, это рассматривается как "ELSE". В этом случае "$$$$$" возвращается, если ни одно из условий не совпадает. Обратите внимание, что важен порядок условий: если первое из них выполняется, дальнейших проверок не будет. Поэтому, если вы указали [List Price] < 1000 в качестве первого условия, оно будет верно, и для изделия по цене $90, и $400.

Для ускорения работы модели данных лучше создавать вычисляемые столбцы за пределами Power Pivot (например, в Excel, базе данных, или с помощью Power Query), а затем импортировать их как часть исходной таблицы.

Комментарии: (1)

Как всегда, великолепно!


Прокомментировать