Методичка - Формулы и функции DAX. Павел Гришенковфункции и формулыпавел Гришенковфункции и формулы

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Проценты
Сравним показатели Тарасова с показателями других менеджеров (найдем долю менеджера). При этом, чтобы
не загромождать подсчеты, будем использовать в формуле ранее полученную меру.
Мера 9:=
SUM
('Демо'[Сумма])/[Мера 7]
Две дополнительные формулы:
Отличие менеджера Тарасова от других менеджеров :=
SUM
('Демо'[Сумма])-[Мера 7]
Приведенное отличие :=(
SUM
('Демо'[Сумма])-[Мера 7])/[Мера 7]
Теперь найдем процентную долю каждого менеджера в общем объеме. Данный показатель можно также получить с помощью дополнительных вычислений в сводной таблице без добавления меры.
Мера 10:=
SUM
([Сумма])/
SUMX
(
ALL
('Демо');[Сумма])
Работа с датами
Создадим меру, которая будет учитывать только данные за 2014 год
Мера 11: =
SUMX
(
FILTER
('Демо';
YEAR
('Демо'[Дата])=2014);[Сумма])

Гришенков Павел
Следующая мера будет учитывать только даты, которые попали в 1 или 2 квартал. является ли один из аргументов TRUE, и если да, то возвращает значение TRUE. Функция возвращает значение
FALSE, если оба аргумента имеют значение FALSE.
(
https://support.office.com/ru-ru/article/Логические
RU&rs=ru-RU&ad=RU
)
Мера 12: =
CALCULATE
(
SUM
([Сумма]);
FILTER
(Квартал)]=
"Квартал2"
)))
Накопительный итог
Посчитаем накопительный итог по дням. Для этого перейдем в раздел
(DAX) по ссылке (
https://support.office.com/ru e4e8-42a8-b205-e1f0f9301b26?ui=ru-RU&rs=ru
DATESBETWEEN (DAX) возвращает таблицу, содержащую ст
заканчивая значением end_date.
Вариант 1.
Мера 13:
=
CALCULATE
(
SUMX
('Демо';'Демо'[Сумма]);
grishenkov_p@mail.ru
будет учитывать только даты, которые попали в 1 или 2 квартал. Функция OR (DAX) п является ли один из аргументов TRUE, и если да, то возвращает значение TRUE. Функция возвращает значение оба аргумента имеют значение FALSE. Другие логические функции (DAX) по ссылке le/Логические-функции-DAX-3e6f6898-8d50-4d0f-
FILTER
('Демо';
Or
('Демо'[Дата (Квартал)]=
"Квартал1"
Посчитаем накопительный итог по дням. Для этого перейдем в раздел - Функции логики операций со временем https://support.office.com/ru-ru/article/Функции-логики-операций-со
RU&rs=ru-RU&ad=RU
) и выберем подходящую функцию.
DATESBETWEEN (DAX) возвращает таблицу, содержащую столбец с датами, начиная со значения start_date и
('Демо';'Демо'[Сумма]);
DATESBETWEEN
('Демо'[Дата];
"06.01.2014"
vk.com/pavelgrishenkov
Функция OR (DAX) проверяет, является ли один из аргументов TRUE, и если да, то возвращает значение TRUE. Функция возвращает значение по ссылке
-9e2c-99bb2aa1a8bc?ui=ru-
"Квартал1"
;'Демо'[Дата
Функции логики операций со временем со-временем-DAX-0571dcda- и выберем подходящую функцию. Функция
олбец с датами, начиная со значения start_date и
"06.01.2014"
;
LASTDATE
('Демо'[Дата])))

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Данная формула будет работать без добавления среза.
Вариант 2.
Требует добавления таблицы «Calendar». Данная таблица формируется автоматически при клике по кнопке –
«Таблица дат».
Мера 14:
=
CALCULATE
(
SUMX
('Демо';'Демо'[Сумма]);
DATESBETWEEN
('Calendar'[Date];
"06.01.2014"
;
LASTDATE
('Calendar'[Da
te])))

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
После этого следует связать столбец «Дата» таблицы «Демо» и столбец «Date»таблицы «Calendar».
Далее переходим в Excel и подключаем срез, выбирая поле «Date».

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Вариант 3.
Данный вариант реализуется аналогично предыдущему, за одним исключением - подключаемая таблица
«Календарь» формируется вручную (столбец «Дата» фильтруется на дубликаты в Excel, далее загружается в модель данных как отдельная таблица, после чего связывается с таблицей «Демо»).
Мера 15:
=
CALCULATE
(
SUMX
('Демо';'Демо'[Сумма]);
DATESBETWEEN
('Календарь'[Календарь];
"06.01.2014"
;
LASTDATE
('Кал
ендарь'[Календарь])))
Дата начала отсчета во всех трех формулах (06.01.2014) проставляется вручную. Чтобы полностью автоматизировать расчеты, немного меняем формулы:
Мера 13:
=
CALCULATE
(
SUMX
('Демо';'Демо'[Сумма]);
DATESBETWEEN
('Демо'[Дата];
MINX
(
ALL
('Демо');'Демо'[Дата]);
LASTDA
TE
('Демо'[Дата])))
Мера 14:
=
CALCULATE
(
SUMX
('Демо';'Демо'[Сумма]);
DATESBETWEEN
('Calendar'[Date];
MINX
(
ALL
('Демо');'Демо'[Дата]);
LAST
DATE
('Calendar'[Date])))
Мера 15:
=
CALCULATE
(
SUMX
('Демо';'Демо'[Сумма]);
DATESBETWEEN
('Календарь'[Календарь];
MINX
(
ALL
('Демо');'Демо'[Да
та]);
LASTDATE
('Календарь'[Календарь])))
Функция MINX (DAX) возвращает наименьшее числовое значение, полученное в результате вычисления
выражения в каждой строке таблицы.

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Сумма последней сделки в месяце, квартале
Работа строится на основе двух новых функций: функция ENDOFMONTH (DAX) возвращает последний день
месяца в текущем контексте для указанного столбца дат; функция ENDOFQUARTER (DAX) возвращает
последний день квартала в текущем контексте для указанного столбца дат. Другие функции логики операций со временем (DAX) (
https://support.office.com/ru-ru/article/Функции-логики-операций-со-временем-DAX-
0571dcda-e4e8-42a8-b205-e1f0f9301b26?ui=ru-RU&rs=ru-RU&ad=RU
)
Мера 16:=
CALCULATE
(
SUMX
('Демо';'Демо'[Сумма]);
ENDOFMONTH
('Демо'[Дата]))
Мера 17:
=
CALCULATE
(
SUMX
('Демо';'Демо'[Сумма]);
ENDOFQUARTER
('Демо'[Дата]);
ALLEXCEPT
('Демо';'Демо'[Дата
(Год)];'Демо'[Дата (Квартал)]))
Или
Мера 17:=
CALCULATE
(
SUMX
('Демо';'Демо'[Сумма]);
ENDOFQUARTER
('Демо'[Дата]);
ALL
('Демо'))

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Накопительный итог по месяцу, кварталу
Работа строится на основе двух новых функций: функция DATESMTD (DAX) возвращает таблицу, содержащую
столбец дат за месяц до текущей даты, в текущем контексте; функция DATESQTD (DAX) возвращает таблицу,
содержащую столбец дат за текущий квартал в текущем контексте.
Мера 18:=
CALCULATE
(
SUM
('Демо'[Сумма]);
DATESMTD
('Демо'[Дата]);
ALL
('Демо'))
Мера 19:=
CALCULATE
(
SUM
('Демо'[Сумма]);
DATESQTD
('Демо'[Дата]);
ALL
('Демо'))

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Установка «финансового» года
Предположим, что по условиям демонстрационного примера наш фин. год длится с 1.11.2014 по 31.10.2015. Как получить накопительный итог за указанный промежуток времени? Функция DATESYTD (DAX) возвращает
таблицу, содержащую столбец дат за текущий год в текущем контексте. Синтаксис DATESYTD(<даты>
[,<дата_окончания_года>])Столбец, содержащий даты. Строковый литерал, определяющий дату окончания года.
Значение по умолчанию — 31 декабря.
Мера 20:
=
CALCULATE
(
SUM
('Демо'[Сумма]);
DATESYTD
('Демо'[Дата];
"31.10.2014"
);
ALLEXCEPT
('Демо';'Демо'[Дата]))

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Округление
Посчитаем сумму сделок без НДС. Для этого добавим новый столбец с формулой = [Сумма с НДС]/118*100;0).
Полученный результат округлим с помощью встроенной функции DAX – ROUND (округляет число до указанного
количества разрядов).
=
ROUND
([Сумма с НДС]/118*100;0)
Еще несколько полезных функций для округления
Функция CEILING (DAX) округляет число в большую сторону до ближайшего целого или до ближайшего числа, кратного заданной значимости.
Функция DIVIDE (DAX) выполняет деление и возвращает только целую часть результата деления. Эта функция используется, когда нужно отбросить остаток от деления.
Функция FLOOR (DAX) округляет число в меньшую сторону (к нулю) до ближайшего числа, кратного заданной значимости.
Функция INT (DAX) округляет число в меньшую сторону до ближайшего целого.
Функция ISO.CEILING (DAX) округляет число в большую сторону до ближайшего целого или до ближайшего числа, кратного заданной значимости.
Функция MROUND (DAX) возвращает число, округленное до кратного заданной значимости.
Функция ROUNDDOWN (DAX) округляет число вниз, к нулю.
Функция ROUNDUP (DAX) округляет число в большую сторону, от 0 (нуля).
Функция TRUNC (DAX) усекает число до целого, удаляя дробную часть.

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Переименование столбцов Power Pivot
Всегда переименовывайте столбцы в таблицах Excel, а не в модели данных. При обновлении сводной
таблицы, внесенные вами изменения будут потеряны.
Операторы DAX
Существует четыре разных типа операторов вычисления: арифметические операторы, операторы сравнения,
операторы объединения текста и логические операторы (
https://support.office.com/ru-ru/article/Справочник-по- операторам-DAX-для-Power-Pivot-DAX-96f07a52-021c-435e-9249-9d3b6e90d9c9?ui=ru-RU&rs=ru-RU&ad=RU
).
Арифметические операторы
Используются для выполнения основных математических операций, таких как сложение, вычитание, умножение, объединение чисел и получения числовых результатов. Пример =[Сумма с НДС]-[Сумма без НДС]
Полностью соответствуют операторам Excel.
Операторы сравнения
Используются для сравнения двух значений. При сравнении двух значений с использованием этих операторов результат представляет собой логическое значение: TRUE или FALSE. Значения: больше, меньше, равно, больше или равно, меньше или равно, не равно.
Оператор объединения текста
Амперсанд (&) используется, чтобы объединить (сцепить) две или более текстовые строки для формирования единого фрагмента текста.
Логические операторы
Для объединения выражений и получения единого результата используются логические операторы (&&) и (||).
&& (двойной амперсанд) - создает между двумя выражениями, каждое из которых дает логический результат, условие логического И. Если оба выражения возвращают значение TRUE, то их сочетание также возвращает значение TRUE. В противном случае возвращается значение FALSE.

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
|| (двойная вертикальная черта) - создает между двумя логическими выражениями условие логического ИЛИ.
Если какое-либо из выражений возвращает значение TRUE, результат будет TRUE. Только когда оба выражения имеют значение FALSE, результат будет FALSE.
Вытягивание значений
Добавим в модель данных новую таблицу с количеством сделок
Рассчитаем сумму одной сделки по каждой записи (т.е. сумма с НДС/количество сделок). Сделать напрямую это не удастся, поэтому необходимо связать таблицы в Power Pivot. Связывание двух таблиц будет проходить по новому столбцу – ключу. Его формула:
=[Дата]&[Менеджер]&[Категория]&[Контрагент]
Значения, полученные с помощью этой формулы, будут уникальны в пределах своих таблиц

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Связываем таблицы
Вытягиваем значения с количеством сделок по каждой записи с помощью функции RELATED. Функция RELATED
(DAX) возвращает связанное значение из другой таблицы. Функция RELATED требует наличия связи между текущей таблицей и таблицей со связанными данными. Пользователь задает столбец, содержащий нужные данные, и функция проходит по существующей связи «многие к одному», чтобы получить значение из указанного столбца в связанной таблице. Когда функция RELATED выполняет подстановку, она проверяет все значения в указанной таблице независимо от фильтров, которые могли быть применены (
https://support.office.com/ru- ru/article/Функция-RELATED-DAX-5d0eee69-8acd-4c3e-a0af-ff23ba01a7bf?ui=ru-RU&rs=ru-RU&ad=RU
).
=
RELATED
('Демо_Сделки'[Количество сделок])

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Теперь можно использовать полученный столбец «Число сделок» для расчетов.
Внимание! Не пытайтесь использовать формулы типа =[Сумма без НДС]/'Демо_Сделки'[Количество сделок].
Они будут выдавать ошибки.
Чтобы исправить результат, модернизируем предыдущую формулу
=
ROUND
([Сумма без НДС]/
RELATED
('Демо_Сделки'[Количество сделок]);2)

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Текстовые функции. Фамилия, имя, отчество
Имеется таблица Excel, как на рисунке представленном ниже. Задача в Power Pivot получить столбец с данными типа – Фамилия И.О.
Для извлечения Фамилии нам потребуется формула
=
LEFT
([Менеджер];
FIND
(
" "
;[Менеджер])-1)
Функция LEFT (DAX) возвращает указанное количество символов с начала текстовой строки. Функция FIND (DAX)
возвращает начальную позицию одной текстовой строки в другой текстовой строке. Функция FIND учитывает регистр.

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Находим позицию первого пробела в строке, задаем количество символов на 1 меньше и извлекаем значение.
Теперь получим вспомогательный столбец, который будет содержать Имя и Отчество менеджера
=
MID
([Менеджер];
FIND
(
" "
;[Менеджер])+1;100)
Функция MID (DAX) возвращает строку символов из середины текстовой строки с учетом начальной позиции и длины.
Находим позицию первого пробела в строке, переходим к позиции первой буквы имени и извлекаем имя и отчество, задав заведомо большее количество символов чем надо – цифра 100.
Получаем Имя менеджера, формула работает аналогично формуле Фамилия, только применяется она к тех. столбцу «Имя Отчество»
=
LEFT
([Имя Отчество];
FIND
(
" "
;[Имя Отчество])-1)

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Извлекаем оставшееся значение – Отчество
=
RIGHT
([Имя Отчество];
LEN
([Имя Отчество])-
LEN
([Имя])-1)
Функция LEN (DAX) возвращает число символов в текстовой строке.
Функция RIGHT (DAX) возвращает последний символ или последние символы текстовой строки в зависимости от указанного количества символов.
Находим длину строки в столбце «Имя Отчество», далее из полученного значения отнимаем количество символов Имени, убираем пробел (-1), извлекаем справа Отчество. Альтернативный вариант формулы
=
MID
([Имя Отчество];
FIND
(
" "
;[Имя Отчество])+1;100)

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Завершаем построение искомого столбца «Фамилия И.О.» формулой
=[Фамилия]&
" "
&
CONCATENATE
(
Left
([Имя];1);
"."
)&
CONCATENATE
(
LEFT
([Отчество];1);
"."
)
Функция CONCATENATE соединяет две текстовые строки в одну. Соединяемые элементы могут быть текстом, числами, логическими значениями, представленными в виде текста, или сочетанием таких элементов. Также можно использовать ссылку на столбец, если столбец содержит подходящие значения. Функция CONCATENATE в
DAX принимает только два аргумента, в то время как функция CONCATENATE в Excel — до 255 аргументов. Чтобы объединить несколько столбцов, можно создать последовательность вычислений, но предпочтительнее использовать оператор объединения (&) для их соединения с помощью более простого выражения. Чтобы непосредственно использовать текстовые строки, а не ссылки на столбец, необходимо заключить каждую строку в двойные кавычки.
Текстовые функции. «Разбор строки»
Приступим у разбору столбца «Документ»

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Извлекаем Дату
=
VALUE
(
RIGHT
([Документ];10))
Полученное значение будет текстом, поэтому трансформируем его в число - функция VALUE (DAX)
Теперь в отдельный столбец выводим Наименование документа. Нам очень повезло: все записи содержат одно название «Накладная» (9 символов, зацепимся за это).
=
LEFT
([Документ];9)
Последний штрих – Номер документа
=
CONCATENATE
(
"№
№
№
№
"
;
MID
([Документ];
LEN
([Наим. документа])+1;
SEARCH
(
" "
;[Документ])-
LEN
([Наим.
документа])-1))
Формула содержит уже знакомые функции за исключением одной - функция SEARCH (DAX) возвращает номер позиции, начиная с которой обнаружен указанный символ или текстовая строка, если считать слева направо. При поиске не учитывается регистр.

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
О других полезных текстовых функциях DAX читайте по ссылке (
https://support.office.com/ru-ru/article/Текстовые- функции-DAX-4dcc8e50-c6a0-45c5-890b-b81c8af67a53?ui=ru-RU&rs=ru-RU&ad=RU
)
Функция FIXED (DAX) округляет число до указанного количества десятичных знаков и возвращает результат в виде текста. Можно задать, чтобы результат возвращался с пробелами или без них.
Функция FORMAT (DAX) преобразует значение в текст в указанном формате.
Функция LOWER (DAX) преобразует все буквы в текстовой строке в нижний регистр.
Функция REPLACE (DAX) функция REPLACE на основе указанного числа символов заменяет часть текстовой строки другой строкой.
Функция REPT (DAX) повторяет текст заданное количество раз. Используйте функцию REPT для заполнения ячейки определенным количеством экземпляров текстовой строки.
Функция SUBSTITUTE (DAX) заменяет существующий текст новым в текстовой строке.
Функция TRIM (DAX) удаляет все пробелы из текста, за исключением единичных пробелов между словами.
Функция UPPER (DAX) переводит все буквы текстовой строки в верхний регистр.

Гришенков Павел
grishenkov_p@mail.ru
vk.com/pavelgrishenkov
Полезные адреса
Справочник по функциям DAX (DAX)
В этом разделе приводится подробный синтаксис функций и операторов, используемых в формулах языка выражений анализа данных (DAX), и примеры. Общие сведения о языке DAX см. в разделе Справочник по выражениям анализа данных (DAX).
Текстовые функции (DAX)
Выражения анализа данных (DAX) включают набор текстовых функций, основанных на библиотеке строковых функций в Excel, которые были изменены для работы с таблицами и столбцами.
Функции даты и времени (DAX)
Многие функции даты и времени в DAX весьма схожи с функциями даты и времени Excel. Однако в функциях DAX используется тип данных datetime, и они могут принимать в качестве аргумента значения из столбца.
Функции фильтров (DAX)
Функции фильтра и значений DAX относятся к самым мощным и сложным, значительно отличаясь от функций
Excel. Функции поиска работают с использованием таблиц и связей, как в базе данных.
Краткое руководство. Обучение основам DAX за 30 минут
Данное краткое руководство предназначено для пользователей, начинающих работать с Power Pivot в Excel или с проектами табличной модели, созданными в SQL Server Data Tools.
Информационные функции (DAX)
Информационная функция проверяет ячейку или строку, указанные в качестве аргумента, и сообщает, соответствует ли значение ожидаемому типу. Например, функция ISERROR возвращает значение TRUE, если упоминаемое значение содержит ошибку.
Логические функции (DAX)
Логические функции обрабатывают выражение и возвращают сведения о значениях или наборах в выражении.
Например, с помощью функции IF можно проверить результат выражения и создать условные результаты.
Математические и тригонометрические функции (DAX)
Математические функции в выражениях анализа данных (DAX) весьма схожи с математическими и тригонометрическими функциями Excel. В этом разделе перечислены математические функции, поддерживаемые языком DAX.
Статистические функции (DAX)
Язык выражения анализа данных предоставляет множество функций для создания агрегатов, таких как суммы, количества и средние значения. Эти функции весьма схожи со статистическими функциями, используемыми в
Microsoft Excel.