Глава 27. Новые функции и переменные DAX

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

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

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

Всё, что описано в этой главе будет работать, начиная с Excel 2016, или в Power BI Desktop.

Ris. 27.1. Vychislyaemyj stolbtse na osnove funktsiya DATEDIFF v Power BI Desktop

Рис. 27.1. Вычисляемый столбце на основе функция DATEDIFF() в Power BI Desktop

Скачать заметку в формате Word или pdf, примеры в Power BI Desktop (в файле архива)

Функция DATEDIFF()

Возвращает количество единиц между двумя датами. Синтаксис:

DATEDIFF(<начальная дата>; <конечная дата>; <интервал>)

<интервал> может принимать значения: Second, Minute, Hour, Day, Week, Month, Quarter, Year

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

ProductLifeSpan (Months) = DATEDIFF(‘Product'[StartDate]; ‘Product'[EndDate]; MONTH)

Иногда мы не понимаем, каким должен быть правильный ответ, учитывая конкретные даты. Нам нравится, как документация объясняет возвращаемое значение: DATEDIFF возвращает количество границ интервалов, находящихся между двумя датами. Поэтому, если вы посмотрите на январь 2015 и границы недели (по умолчанию первый день недели – воскресенье)…

Ris. 27.2. Granitsy nedeli

… вы поймете результаты DATEDIFF, показанные ниже:

Ris. 27.3. Rezultat vozvrashhaemyj DATEDIFF dlya intervalov WEEK

Рис. 27.3. Результат, возвращаемый DATEDIFF для интервалов WEEK

Функции MEDIAN() и PERCENTILE

Синтаксис – MEDIAN(<столбец>). Например, Median Sales = MEDIAN(Sales[SalesAmount])

Ris. 27.4. Obratite vnimanie chto srednee i mediana otlichayutsya

Рис. 27.4. Обратите внимание, что среднее и медиана отличаются

Процентиль представлен парой функций PERCENTILE.INC(<столбец>, <k>) PERCENTILE.EXC(<столбец>, <k>). Что означают суффиксы функций, и почему их две, см., например, КВАРТИЛЬ: какие формулы расчета использует Excel.

Функция PRODUCT()

Возможность умножения значений в столбце ранее отсутствовала. Расчет кумулятивной отдачи, вероятно, является наиболее распространенным примером того, когда нам это нужно. Предположим, у нас есть ежемесячная доходность S&P500:

Ris. 27.5. Ezhemesyachnaya dohodnost SP500

Рис. 27.5. Ежемесячная доходность S&P500

Если месячная доходность за первый месяц равна R1, за второй месяц R2 и так далее, годовая доходность будет рассчитываться по формуле:

(1+R1) * (1+R2) *… * (1+R12) — 1

У нас уже есть значение 1+R, хранящееся в столбце Factor. Таким образом, мы можем определить нашу меру как:

Annual Return = PRODUCT(MonthlyReturn[Factor]) – 1

Ris. 27.6. Godovaya dohodnost rasschitannaya na osnove mesyachnoj dohodnosti

Рис. 27.6. Годовая доходность, рассчитанная на основе месячной доходности

Функции GEOMEAN() и GEOMEANX()

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

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

Annual Return Arithmetic Mean =
AVERAGEX(VALUES(MonthlyReturn[Year]); [Annual Return])

Annual Return Geometric Mean =
GEOMEANX(VALUES(MonthlyReturn[Year]); [Annual Return])

Ris. 27.7. Srednee geometricheskoe i srednee arifmeticheskoe razlichayutsya

Рис. 27.7. Среднее геометрическое и среднее арифметическое различаются

Подробнее о геометрическом среднем см. http://ppvt.pro/geomeanMath

Заметим, что целый ряд статистических функций имеют Х-версии. Например, MEDIANX, PERCENTILEX.INC, PERCENTILEX.EXC, PRODUCTX. Как и SUMX, все эти Х-функции дают возможность работать с мерами (в отличие от обычных функций, которые обрабатывают значения в столбцах).  Х-функции позволяют управлять <таблицами>, над которой выполняется расчет, в том числе и виртуальными таблицами, созданными табличными функциями, такими как ALL и FILTER (см. главу 24. Нюансы функций CALCULATE и FILTER). X версии функций сложнее для понимания, но они не менее, если не более важны (основные Х-функции DAX см. Глава 16. SUMX() и другие X функции (итераторы)).

CONCATENATEX

Эта функция полезна, когда нужно объединить в одной ячейке переменное количество текстовых значений. Чтобы проиллюстрировать идею, сначала мы определим меру Top N, которая считает продажи для лучших N продуктов (для определенности – трех).

Теперь определим меру, которая позволит записать три лучших продукта через запятую (рис. 27.8):

Ris. 27.8. CONCATENATEX vyvodit imena treh samyh prodavaemyh produktov

Рис. 27.8. CONCATENATEX выводит имена трех самых продаваемых продуктов; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

И, действительно, если отсортировать данные за июль 2001, то увидим:

Ris. 27.9. Top 3 produktov za iyul 2001

Рис. 27.9. Топ 3 продуктов за июль 2001

Синтаксис:

CONCATENATEX(<таблица>; <выражение>; [разделитель]; [сортировать по]; [порядок сортировки])

Разберем работу меры Top 3 Products Sales подробнее. Как и другие Х-функции, первый аргумент – <таблица>, над которой будут выполняться итерации. В нашем примере таблица – первые три строки с максимальными продажами за выбранный период (строка 3 формулы выше).

Второй аргумент (строка 4) – выражение, которое рассчитывается для каждой строки таблицы. В нашем случае – значения из столбца [ProductName]. Именно они будут сцепляться.

Разделитель (строка 5) – запятая.

Строка 5 и 6 – необязательные аргументы. Их использование позволяет вывести результаты в определенном порядке. Мы хотим отсортировать названия продуктов по объему продаж – [Total Sales] (строка 5) в порядке убывания – DESC (строка 7; подробнее см. описание функции RANKX() в Глава 16. SUMX() и другие X функции).

ISEMPTY()

Возвращает ИСТИНА, если указанная таблица (или табличное выражение) пуста. Синтаксис: ISEMPTY(<таблица или табличное выражение>). Определим меру ProductNotSold = ISEMPTY(Sales).

Ris. 27.10. ISEMPTY v rabote

Рис. 27.10. ISEMPTY() в работе

INTERSECT(), EXCEPT() and UNION()

Эти функции пригодятся, когда вы сравниваете списки или комбинируете их каким-то образом. Синтаксис: INTERSECT(<LeftTable >; <RightTable>), EXCEPT(<LeftTable >; <RightTable>), UNION(<table_expression1>; <table_expression2> [; <table_expression>]…)

INTERSECT: возвращает строки левой таблицы, которые присутствуют в правой таблице. EXCEPT возвращает строки левой таблицы, которых нет в правой таблице. UNION возвращает объединение двух таблиц, если их столбцы совпадают.

Ris. 27.11. Graficheskaya illyustratsiya raboty funktsij

Рис. 27.11. Графическая иллюстрация работы функций

Допустим, у нас есть:

  • Список 1: 10 лучших продуктов по объему продаж
  • Список 2: 10 лучших продуктов по размеру прибыли

Ris. 27.12. Top 10 produktov po prodazham i pribyli v kategorii odezhda

Рис. 27.12. Топ-10 продуктов по продажам и прибыли в категории «одежда»

INTERSECT позволит подсчитать число продуктов, присутствующие в обоих списках:

И таких продуктов 4. Используя EXCEPT, мы хотим определить количество продуктов, которые есть в списке 1, но нет в списке 2:

И таких продуктов 6. Используя UNION, мы хотим определить количество продуктов, которые отображаются в списке 1 или в списке 2. Это немного сложнее, так как UNION() сохраняет все строки обеих таблиц (в том числе и дубли):

VAR используется для объявления переменной (мы поговорим о них ниже). Наша мера осуществляет вычисления в два шага. 1). Функция UNION объединяет списки. 2). Функция SUMMARIZE возвращает временную таблицу в виде сводной с Product[ProductKey] в строках и пустотой в столбцах. А сводная, естественно, удаляет дубли. COUNTROWS подсчитывает строки в этой виртуальной сводной таблице. Результат – 16.

Переменные DAX

Переменные подобны магнитофону. Можно «записать» результат вычисления, а затем «воспроизвести» его несколько раз в других частях формулы.

Ris. 27.13. Funktsii DAX pozvolyaet zapisat znachenie v odnoj chasti formuly i analizirovat ego v neskolkih drugih mestah

Рис. 27.13. Функции DAX позволяет записать значение в одной части формулы, и анализировать его в нескольких других местах

Переменные дают три преимущества:

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

Представим на минуту следующую формулу:

Формула выполняет расчет, и если он больше нуля, то возвращаем тот же самый расчет. В противном случае возвращает «-1». К сожалению, это требует два раза вычислить выражение CALCULATE(COUNTROWS(…)). Это традиционный шаблон, который присутствует и в обычном Excel: ЕСЛИ(ЕОШИБКА(<выражение>); 0; <выражение>). В Excel этот шаблон был упрощен, начиная с Excel 2016 с помощью функции ЕСЛИОШИБКА(<выражение>; значение если ошибка), которая позволяет написать <выражение> только один раз. Аналогичная функция появилась и в DAX, также начиная с версии 2016 г. – IFERROR.

Но в нашем случае мы не проверяем, возвращает ли выражение ошибку, т.е., не можем использовать IFERROR, чтобы не дублировать вычисление. Можно определить переменную:

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

Ключевое слово VAR

Если вы набирали формулу с клавиатуры, то заметите, что ключевое слово VAR не отображается в автозаполнении:

Ris. 27.14. VAR ne poyavlyayutsya v spiske avtozapolneniya chto nemnogo stranno

Рис. 27.14. VAR не появляются в списке автозаполнения, что немного странно

Не волнуйтесь, VAR существует! Просто введите имя меры знак := и VAR, нажмите пробел и вуаля:

Ris. 27.15. Obratite vnimanie chto VAR vydelyaetsya tsvetom ukazyvaya chto DAX znaet chto eto slovo osobennoe

Рис. 27.15. Обратите внимание, что VAR выделяется цветом, указывая, что DAX знает, что это слово особенное

Как только механизм DAX увидит ключевое слово VAR, он ожидает, что вы далее введете блок <имя переменной> = <выражение>. Продолжайте вводить, как в примере выше:

VAR RowCount = CALCULATE(COUNTROWS(Sales))

…и посмотрите, что редактор формул сделает с этим:

Ris. 27.16. RowCount takzhe vydelen tsvetom ukazyvaya chto DAX raspoznal slovo kak peremennuyu

Рис. 27.16. RowCount также выделен цветом, указывая, что DAX распознал слово, как переменную

Вы можете создать несколько переменных в одной формуле, при этом каждому блоку <имя переменной> = <выражение> должно предшествовать собственное ключевое слово VAR.

Ключевое слово RETURN

Теперь, чтобы продолжить написание обычной формулы, нужно закрыть блок определения переменной ключевым словом RETURN.

Ris. 27.17. Klyuchevoe slovo RETURN takzhe ne otobrazhaetsya v avtozapolnenii no takzhe kak i VAR ono raspoznaetsya i formatiruetsya tsvetom

Рис. 27.17. Ключевое слово RETURN также не отображается в автозаполнении, но также, как и VAR, оно распознается и форматируется цветом

Ссылка на переменную

Для использование переменной просто введите ее имя.

Ris. 27.18. Imena peremennyh poyavlyayutsya v avtozapolnenii i dazhe imeyut originalnyj znachok

Рис. 27.18. Имена переменных появляются в автозаполнении и даже имеют оригинальный значок

После определения переменной внутри конструкции VAR … RETURN при каждом новом использовании она будет выделяться цветом, сигнализируя, что движок DAX ее распознал.

Переменная вместо функции EARLIER

Помните формулу из предыдущей главы для подсчета продаж по категории?

Оказывается, что применение переменной позволяет преодолеть трудности с контекстом строки:

Ris. 29.19. Peremennaya VAR i funktsiya EARLIER dayut odinakovyj rezultat

Рис. 29.19. Переменная VAR и функция EARLIER дают одинаковый результат

Напомним, что функция EARLIER требовалась нам, что в процессе работы функции-итератора FILTER сравнить контекст строки внутри FILTER и вне ее. Поскольку блок VAR оценивается один раз, причем вне функции FILTER, для получения исходного значения контекста строки достаточно просто сослаться на столбец. Значение переменной Category является статическим для остальной части формулы.

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

Переменная вместо ссылки на меру внутри функции FILTER

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

Напомним. Исходная (работающая) формула:

Однако, если сначала определить меру…

Highest Price = MAX(Products[ListPrice])

… то следующая формула переставала работать:

Использование переменной возвращает работоспособность формуле:

Использование переменной позволяет вам вернуться к лучшей практике – «я пишу каждую формулу только один раз». От этого правила вы были вынуждены отступить при работе внутри FILTER. Маленькая победа, но все же победа!


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