Если ты увидел 3 незнакомых буквы и решил пролистнуть от греха подальше, то не спеши. Мы сейчас будем говорить про наши любимые сводные таблицы. Но не простые, а уже более профессиональные.
Привет, коллега, на связи ленивый аналитик, и сегодня мы коснемся темы Power Pivot в Excel и языка DAX, который используется для создания вычисляемых мер.
Если пока ничего не понятно (но очень интересно), то попробую разложить подробно.
Сводные таблицы в Excel бывают:
Про сводные таблицы у меня на канале есть целая подборка.
А также:
Чтобы таблица попала в модель данных, нужно при ее создании:
- как обычно, выделить диапазон данных источника
- перейти на вкладку Вставка - Сводная таблица
- Установить галочку "Добавить эти данные в модель данных"
Зачем нам это?
Глобально добавление данных в модель данных нужно... для создания моделей данных (неожиданно, да? 😉)
Но про модели данных поговорим в следующей статье.
Я же часто использую возможности модели данных просто для создания сводной таблицы. Без моделирования. Почему?
Из-за возможностей языка DAX!
DAX - это язык запросов (не программирования!), при помощи которого мы обращаемся к модели данных и составляем формулы и функции.
При помощи языка DAX можно создавать вычисляемые меры.
(кроме мер можно создавать еще вычисляемые столбцы и таблицы, но сейчас не о них, т.к. мы пока "по-простому")
Вычисляемая мера - это почти как вычисляемое поле в обычной сводной таблице.
Конечно, это не совсем одно и то же - но такое сравнение будет наиболее понятно для тех, кто все эти определения читает впервые.
И да, вычисляемые меры имеют более продвинутые возможности по сравнению с вычисляемыми полями.
Как устроен язык DAX
В целом, формулы в DAX похожи на обычные формулы в Excel.
Формулы в DAX всегда пишутся в англоязычном варианте - в отличие от формул в Excel, которые вы пишите кириллицей, если обладаете русифицированной версией офиса.
Математические операторы:
& + - / * = > < () и их сочетания работают так же, как и в Excel.
Также в DAX работают Логические операторы:
- && — аналог формулы И (AND)
- || — аналог ИЛИ (OR)
- IN – поиск элемента в списке
- NOT – логическое отрицание, аналог формулы НЕ.
Также же есть одно важное отличие в создании вычисляемых мер DAX и вычисляемых полей.
*исключением является, когда в создании мер используются другие меры - их не нужно оборачивать в агрегирующие функции, потому что они уже что-то агрегируют.
В отличие от DAX - при создании обычного вычисляемого поля агрегирующую функцию не всегда нужно указывать.
Как написать формулу DAX
Итак, мы создали сводную таблицу с добавлением ее в модель данных.
Первое, что видим - нам больше недоступна возможность создать вычисляемое поле, как в обычном своднике.
И чтобы создать вычисляемую меру:
В окне Поля сводной таблицы щелкнем правой кнопкой мыши на заголовке таблицы и выберем Добавить меру
Также создать меру можно из вкладки меню Power Pivot - Меры - Создать меру.
Поля в окне создания меры:
- Имя меры - обязательное поле.
Обратите внимание - имя меры не должно совпадать с названиями других столбцов.
- Описание значения - не обязательное поле. Используется для комментирования расчетов в мере. Удобно, если у вас большая модель данных (чтобы передать потомкам, что вы имели в виду, когда это считали 😆)
Саму формулу меры пишем в белом поле.
После написания формулу можно (я бы сказала, нужно) нажать кнопку - Проверить формулу DAX. Чтобы убедиться, что ваша формула не содержит ошибок.
Удобная особенность - после написания функции и открытия скобки выходят подсказки с наименованиями полей.
В DAX-формулах, так же, как и в формулах Excel, названия таблиц обычно пишут в одинарных кавычках (или без кавычек, если имя таблицы написано латинскими буквами без пробелов и цифр в начале). Названия столбцов пишут в квадратных скобках:
'Имя таблицы'[Название столбца] или TableName[Название столбца]
5 базовых функций DAX
В языке DAX есть множество функций, который подразделяются на типы, - математические, логические, функции даты и времени и т.д. Весь перечень функций с кратким описанием можно увидеть, если в окне создания меры нажаться кнопку fx:
SUM
SUM суммирует числа в столбце. Аналог в Excel – формула СУММ.
SUM(<столбец>)
Это самая базовая формула. Во-первых потому что операция суммирования - самая распространенная в работе с данными. Во-вторых потому что, как писала выше - нельзя напрямую обратиться к столбцу, сначала к нему нужно применить функцию агрегирования, и SUM подходит во многих случаях.
DIVIDE
DIVIDE – формула для улучшенного деления.
DIVIDE(<числитель>, <знаменатель> [, <альтернативный результат>])
В DAX есть привычный нам оператор деления / , но функция DIVIDE позволяет дополнительно вывести альтернативный результат деления в случае получения ошибки.
Если сравнить с функциями Excel, то DIVIDE по сути: ЕСЛИОШИБКА(<числитель>/<знаменатель>;<значение, если получено ошибка>)
IF
IF – это логическая формула, аналог ЕСЛИ в Excel.
IF(<условие>, <значение если истина>[, <значение если ложь>])
У этой функции есть аналог - SWITCH, который имеет очень интересное прикладное применение, о котором расскажу в следующих статьях.
DISTINCTCOUNT
DISTINCTCOUNT - подсчитывает количество уникальных значений в столбце таблицы.
DISTINCTCOUNT(<столбец>)
Прямых аналогов в Excel я не вспомнила (если знаете, пишите в комментариях).
AVERAGE
Эта функция находит среднее значение в столбце.
AVERAGE(<столбец>)
В Excel аналогом этой функции является СРЗНАЧ.
Как видите, язык DAX не страшный и очень похож на привычный excel-ный (ага, как же...)
Конечно, это все базовая база))) потому что - впереди нас ждет самая-самая часто используемая функция DAX. Без которой вообще не получится.
Поэтому подписывайся сюда))
И на тележку тоже можно. Там помимо оповещений о новых статьях есть еще полезности.