Полное подробное руководство по DAX: функции, формулы, примеры

Для кого эта статья:

• Специалисты по данным, включая BI-аналитиков
• Студенты и новички, желающие освоить DAX и аналитические инструменты
• Управляющие и руководители, заинтересованные в повышении эффективности бизнес-аналитики

DAX — это ядерная боеголовка в арсенале аналитика данных. Овладев этим языком формул, вы сможете решать задачи, которые раньше казались невыполнимыми в Power BI и Excel. За мощными отчетами и дашбордами, заставляющими руководство компаний принимать миллионные решения, почти всегда стоит грамотное использование DAX. Пора разобраться, как применять этот инструмент на полную мощность и превратить хаос данных в чистую аналитическую выгоду. 🚀

Хотите стать высокооплачиваемым BI-аналитиком и мастером DAX? Курс «BI-аналитик» с нуля от Skypro даст вам не только теоретические знания, но и реальные навыки работы с DAX формулами в Power BI. За 9 месяцев вы пройдете путь от новичка до востребованного специалиста, способного создавать продвинутую аналитику с зарплатой от 120 000 рублей. Программа разработана с учетом требований рынка и реальных бизнес-задач.

Что такое DAX: основы и принципы работы языка

DAX (Data Analysis Expressions) — это функциональный язык формул, разработанный Microsoft специально для анализа данных. Первоначально созданный для Power Pivot в Excel, сегодня он является фундаментом для аналитики в Power BI, Analysis Services и других инструментах Microsoft. По сути, DAX — это нечто среднее между формулами Excel и SQL, но с уникальными возможностями для работы с данными в табличных моделях.

Ключевые особенности DAX:

• Контекстно-зависимый — вычисления всегда происходят в контексте строки или фильтра
• Ориентирован на таблицы — операции выполняются над целыми таблицами или их частями
• Не процедурный — описывает что нужно получить, а не как это сделать
• Богат функциями — содержит 250+ функций для различных типов вычислений
• Оптимизирован для больших данных — эффективно работает с миллионами строк

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

Первое, что нужно понять в DAX — это концепция контекста. Контекст определяет, над какими данными выполняется формула в конкретный момент. В DAX существует два типа контекста:

1. Контекст строки — когда вычисление выполняется для каждой строки таблицы (как в вычисляемых столбцах)
2. Контекст фильтра — подмножество данных, к которому применяется формула (активен в мерах)

Для начала работы с DAX достаточно освоить базовый синтаксис создания мер и вычисляемых столбцов:

// Создание меры
Total Sales = SUM(Sales[Amount])

// Создание вычисляемого столбца
Profit = Sales[Price] – Sales[Cost]

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

Мощные функции DAX для анализа данных

Алексей Михайлов, ведущий BI-аналитик

Однажды наша команда получила срочную задачу от коммерческого директора — найти причину внезапного падения прибыльности в определенном сегменте продукции. Отчет в Power BI уже существовал, но не давал ясного ответа. Я решил применить функции RANKX и FILTER в комбинации с CALCULATE.

Создав меру для ранжирования продуктов по снижению маржинальности и применив временные интеллектуальные фильтры с SAMEPERIODLASTYEAR, мы быстро обнаружили паттерн: 23% наиболее прибыльных товаров стали закупаться у другого поставщика с более высокой ценой. Простая DAX формула помогла найти проблему за час, которая иначе потребовала бы недели анализа. После возврата к прежнему поставщику маржинальность восстановилась, сэкономив компании около 7 миллионов рублей в квартал.

Библиотека функций DAX — это настоящая сокровищница для аналитика. Здесь собраны инструменты для решения практически любого аналитического сценария, от базовой агрегации до сложного интеллектуального анализа данных. Разберем ключевые категории функций, которые должен знать каждый специалист. 🔍

1. Агрегационные функции

Основа любого анализа — это агрегация данных. DAX предлагает расширенный набор агрегационных функций:

• SUM, AVERAGE, MIN, MAX — базовые агрегации, знакомые из Excel
• SUMX, AVERAGEX — итеративные версии, которые выполняют выражение для каждой строки, а затем агрегируют результаты
• COUNTROWS, DISTINCTCOUNT — подсчет строк и уникальных значений

Пример использования SUMX для расчета выручки с НДС:

Revenue with Tax = 
SUMX(
Sales,
Sales[Quantity] * Sales[Price] * (1 + Sales[TaxRate])
)

2. Функции фильтрации и модификации контекста

Это самые мощные функции DAX, позволяющие управлять контекстом вычислений:

• CALCULATE — модифицирует контекст фильтра для вычисления выражения
• FILTER — возвращает таблицу, отфильтрованную по условию
• ALL, ALLEXCEPT — удаляют фильтры из контекста
• ALLSELECTED — удаляет фильтры, сохраняя выбор пользователя
• KEEPFILTERS — сохраняет существующие фильтры при добавлении новых

Пример расчета доли рынка:

Market Share = 
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(
SUM(Sales[Amount]),
ALL(Products)
)
)

3. Функции для работы со временем

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

• DATESBETWEEN — возвращает таблицу дат в указанном диапазоне
• SAMEPERIODLASTYEAR — смещает даты на год назад
• PARALLELPERIOD — находит параллельный период с указанным смещением
• DATEADD — смещает даты на указанный интервал
• DATESYTD, DATESQTD, DATESMTD — возвращают даты с начала года, квартала или месяца

Расчет роста год к году:

YoY Growth % = 
VAR CurrentSales = SUM(Sales[Amount])
VAR PreviousSales = CALCULATE(
SUM(Sales[Amount]),
SAMEPERIODLASTYEAR('Date'[Date])
)
RETURN
IF(
PreviousSales = 0,
BLANK(),
(CurrentSales – PreviousSales) / PreviousSales
)

4. Функции ранжирования и статистики

Для продвинутого анализа данных:

• RANKX — ранжирует строки таблицы по определенному выражению
• TOPN — возвращает указанное количество верхних строк
• PERCENTILE.EXC, PERCENTILE.INC — вычисляют перцентили в распределении
• STDEVX.P, VARX.S — статистические функции стандартного отклонения и дисперсии

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

Построение сложных формул в DAX для бизнес-задач

Реальная сила DAX раскрывается при построении сложных бизнес-вычислений. Чтобы создавать эффективные формулы, необходимо не только знать синтаксис, но и уметь мыслить в парадигме DAX, комбинируя функции для решения нетривиальных задач. Рассмотрим методики создания продвинутых вычислений. ⚙️

Техника переменных (VAR) для повышения читаемости и производительности

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

Profit Margin % = 
VAR TotalSales = SUM(Sales[Amount])
VAR TotalCosts = SUM(Costs[Amount])
VAR Profit = TotalSales – TotalCosts
RETURN
IF(
TotalSales = 0,
BLANK(),
DIVIDE(Profit, TotalSales)
)

Приемы работы с множественными таблицами

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

Customer Lifetime Value = 
SUMX(
VALUES(Customers[CustomerID]),
CALCULATE(
SUM(Sales[Amount]),
ALLEXCEPT(Sales, Sales[CustomerID])
)
)

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

Решение проблемы "многие ко многим"

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

Product Categories Revenue = 
CALCULATE(
SUM(Sales[Amount]),
TREATAS(
VALUES(ProductCategories[Category]),
Products[Category]
)
)

Динамические периоды сравнения

Бизнес-аналитика часто требует сравнения показателей за разные периоды. DAX предоставляет гибкий инструментарий для таких задач:

Dynamic Period Comparison = 
VAR SelectedPeriod = SELECTEDVALUE(PeriodSelector[Period], "MTD")
VAR CurrentValue = [Total Sales]
VAR ComparisonValue = 
SWITCH(
SelectedPeriod,
"MTD", CALCULATE([Total Sales], DATESMTD('Date'[Date])),
"QTD", CALCULATE([Total Sales], DATESQTD('Date'[Date])),
"YTD", CALCULATE([Total Sales], DATESYTD('Date'[Date]))
)
RETURN
IF(
ComparisonValue = 0,
BLANK(),
(CurrentValue – ComparisonValue) / ComparisonValue
)

Эта формула позволяет пользователю выбирать период сравнения (месяц, квартал, год) через срез в отчете.

Использование итераторов для сложных агрегаций

Итераторы (X-функции) позволяют выполнять последовательные вычисления по строкам таблиц, открывая новые возможности:

Weighted Average Price = 
SUMX(
Sales,
Sales[Price] * Sales[Quantity]
) / SUM(Sales[Quantity])

Это более точный способ расчета средневзвешенной цены с учетом объемов продаж.

Паттерны проектирования DAX для типичных бизнес-метрик

• Доля от общего (% of Total) — отношение значения к итогу по всему набору данных
• Нарастающий итог (Running Total) — кумулятивная сумма значений с начала периода
• Скользящее среднее (Moving Average) — среднее значение за последние N периодов
• ABC-анализ —categorization элементов по их вкладу в общий результат
• Когортный анализ — отслеживание групп клиентов, объединенных общим признаком

Пример формулы для ABC-анализа:

ABC Category = 
VAR CurrentProduct = SELECTEDVALUE(Products[ProductKey])
VAR ProductRevenue = CALCULATE([Total Sales], Products[ProductKey] = CurrentProduct)
VAR TotalRevenue = CALCULATE([Total Sales], ALL(Products))
VAR CumulativePercentage = 
SUMX(
FILTER(
ADDCOLUMNS(
ALL(Products),
"Revenue", CALCULATE([Total Sales], Products[ProductKey] = EARLIER(Products[ProductKey]))
),
[Revenue] >= ProductRevenue
),
[Revenue]
) / TotalRevenue
RETURN
IF(
CumulativePercentage <= 0.8,
"A",
IF(
CumulativePercentage <= 0.95,
"B",
"C"
)
)

Освоение искусства построения сложных формул в DAX — это ключ к созданию аналитических решений, которые действительно отвечают бизнес-потребностям. Комбинируя различные функции, используя переменные и понимая контекст вычислений, вы сможете трансформировать сырые данные в ценные бизнес-инсайты. 🧠

Практические кейсы использования DAX в Power BI и Excel

Мария Соколова, руководитель аналитического отдела

Наша розничная сеть столкнулась с проблемой: региональные менеджеры жаловались на устаревшие отчеты, которые обновлялись лишь раз в неделю и требовали часов для анализа. Я предложила перейти на Power BI с DAX для создания динамической аналитики.

Ключевой вызов: нам нужно было показать не только текущую ситуацию, но и прогнозировать выполнение планов продаж на конец месяца, с учетом сезонности и исторических паттернов. Для этого я создала серию DAX-мер с использованием CALCULATE, SAMEPERIODLASTYEAR и DATESINPERIOD.

Когда я представила интерактивный дашборд руководству, один из директоров сразу заметил, что два магазина с высокой посещаемостью показывали низкую конверсию. Благодаря DAX-формулам для когортного анализа мы выяснили, что проблема возникает только по выходным, когда штат сотрудников был недоукомплектован. После корректировки графиков работы конверсия выросла на 23%, а годовой оборот увеличился более чем на 40 миллионов.

Теория становится по-настоящему полезной только когда применяется на практике. Рассмотрим конкретные примеры использования DAX для решения типичных бизнес-задач в Power BI и Excel. 💼

Кейс 1: Финансовая аналитика для CFO

Задача: создать всеобъемлющий финансовый дашборд с ключевыми показателями, динамикой изменений и прогнозами.

Ключевые DAX-формулы:

// Маржинальность с динамикой
Gross Margin % = 
VAR CurrentGM = DIVIDE([Gross Profit], [Revenue], 0)
VAR PrevYearGM = CALCULATE(
DIVIDE([Gross Profit], [Revenue], 0),
SAMEPERIODLASTYEAR('Date'[Date])
)
RETURN CurrentGM

// Расчет YTD с процентом выполнения годового плана
Revenue YTD = 
VAR YTDValue = CALCULATE([Revenue], DATESYTD('Date'[Date]))
VAR AnnualTarget = CALCULATE([Revenue Target], 'Scenario'[Scenario]="Annual Target")
RETURN YTDValue

Annual Target % = DIVIDE([Revenue YTD], [Annual Target])

// Прогноз выполнения годового плана
Year-End Forecast = 
VAR DaysInYear = COUNTROWS(DATESINPERIOD('Date'[Date], MIN('Date'[Year]), 1, YEAR))
VAR DaysElapsed = COUNTROWS(DATESINPERIOD('Date'[Date], MIN('Date'[Year]), TODAY(), DAY))
VAR YTDRevenue = [Revenue YTD]
VAR DailyAverage = DIVIDE(YTDRevenue, DaysElapsed, 0)
VAR RemainingDays = DaysInYear – DaysElapsed
RETURN YTDRevenue + (DailyAverage * RemainingDays)

Кейс 2: Анализ клиентской базы для маркетологов

Задача: сегментировать клиентов, анализировать их поведение и оптимизировать маркетинговые кампании.

Ключевые DAX-формулы:

// Расчет RFM-сегментов
Recency Score = 
SWITCH(
TRUE(),
[Days Since Last Purchase] <= 30, 5,
[Days Since Last Purchase] <= 60, 4,
[Days Since Last Purchase] <= 90, 3,
[Days Since Last Purchase] <= 180, 2,
1
)

Frequency Score = 
SWITCH(
TRUE(),
[Purchase Count] >= 10, 5,
[Purchase Count] >= 7, 4,
[Purchase Count] >= 5, 3,
[Purchase Count] >= 3, 2,
1
)

Monetary Score = 
SWITCH(
TRUE(),
[Total Spend] >= 5000, 5,
[Total Spend] >= 3000, 4,
[Total Spend] >= 1000, 3,
[Total Spend] >= 500, 2,
1
)

RFM Segment = 
VAR RFMValue = [Recency Score] * 100 + [Frequency Score] * 10 + [Monetary Score]
RETURN
SWITCH(
TRUE(),
RFMValue >= 500, "Champions",
RFMValue >= 400, "Loyal Customers",
RFMValue >= 300, "Potential Loyalist",
RFMValue >= 200, "New Customers",
"Needs Attention"
)

Кейс 3: Операционная эффективность для розничной сети

Задача: оценить эффективность магазинов, управлять запасами и оптимизировать операции.

Ключевые DAX-формулы:

// Анализ продаж на квадратный метр
Sales per SQM = 
DIVIDE(
[Total Sales],
RELATED(Stores[Square Meters])
)

// Расчет Out-of-Stock ситуаций
Out of Stock % = 
DIVIDE(
CALCULATE(
COUNTROWS(Inventory),
Inventory[Stock Level] <= Inventory[Reorder Point]
),
COUNTROWS(Inventory)
)

// Оборачиваемость запасов
Inventory Turnover = 
DIVIDE(
[Cost of Goods Sold],
AVERAGEX(
VALUES('Date'[Month]),
CALCULATE([Average Inventory Value])
)
)

Кейс 4: HR-аналитика для руководителей отделов

Задача: анализировать текучесть кадров, эффективность найма и удовлетворенность сотрудников.

Ключевые DAX-формулы:

// Текучесть кадров по отделам
Employee Turnover % = 
DIVIDE(
[Employees Left],
[Average Headcount]
)

// Время до заполнения вакансии
Avg Time to Fill = 
AVERAGEX(
Hiring,
Hiring[HireDate] – Hiring[OpenDate]
)

// Соотношение затрат на персонал к выручке
Personnel Cost Ratio = 
DIVIDE(
[Total Personnel Costs],
[Total Revenue]
)

Кейс 5: Анализ воронки продаж для e-commerce

Задача: отслеживать путь пользователя, выявлять узкие места и оптимизировать конверсию.

Ключевые DAX-формулы:

// Расчет показателей конверсии
Conversion Rate % = 
DIVIDE(
[Orders],
[Sessions]
)

// Анализ отказов по этапам
Funnel Drop-off % = 
VAR CurrentStepValue = [Current Step Count]
VAR PreviousStepValue = [Previous Step Count]
RETURN
IF(
PreviousStepValue > 0,
1 – DIVIDE(CurrentStepValue, PreviousStepValue),
BLANK()
)

// Стоимость приобретения клиента
Customer Acquisition Cost = 
DIVIDE(
[Marketing Spend],
[New Customers]
)

Эти примеры показывают, как DAX может быть применён для решения конкретных бизнес-задач в различных отраслях и функциональных областях. Адаптируя эти формулы под свои нужды и комбинируя их, вы сможете создавать аналитические решения, максимально отвечающие потребностям вашего бизнеса. 🎯

Хотите понять, подойдет ли вам карьера в аналитике данных? Пройдите Тест на профориентацию от Skypro и узнайте, насколько ваши навыки и предпочтения соответствуют требованиям профессии BI-аналитика. Тест оценит ваши аналитические способности, внимание к деталям и логическое мышление — ключевые качества для работы с DAX и Power BI. Получите персональные рекомендации по развитию карьеры в сфере данных уже через 3 минуты!

От базовых до продвинутых техник DAX: план развития

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

Уровень 1: Основы DAX (1-2 месяца)

• Понимание модели данных и отношений между таблицами
• Освоение базового синтаксиса DAX и различий между мерами и вычисляемыми столбцами
• Использование простых агрегационных функций: SUM, AVERAGE, COUNT, MIN, MAX
• Создание базовых вычислений с использованием математических операторов
• Применение функций для работы с текстом: CONCATENATE, LEFT, RIGHT, UPPER

Практические задания для закрепления:

• Создание простых мер для суммирования и подсчета значений
• Объединение значений из нескольких столбцов
• Расчет простых KPI (выручка, количество клиентов, средняя покупка)

Уровень 2: Работа с контекстом и фильтрами (2-3 месяца)

• Глубокое понимание контекста строки и контекста фильтра
• Изучение функции CALCULATE и ее параметров
• Применение функций фильтрации: FILTER, ALL, ALLEXCEPT, ALLSELECTED
• Использование функций RELATED и RELATEDTABLE для работы с отношениями
• Изучение итераторов: SUMX, AVERAGEX, COUNTX

Практические задания для закрепления:

• Создание мер с изменением контекста фильтра
• Расчет процента от общего значения
• Реализация динамической сегментации данных

Уровень 3: Временной интеллект и продвинутые вычисления (3-4 месяца)

• Освоение функций временного интеллекта: DATEADD, DATESBETWEEN, SAMEPERIODLASTYEAR
• Работа с функциями DATESYTD, DATESQTD, DATESMTD
• Создание динамических периодов сравнения
• Изучение условных функций: IF, SWITCH, IFERROR
• Применение функции VAR для оптимизации производительности

Практические задания для закрепления:

• Построение сравнительного анализа "год к году", "месяц к месяцу"
• Создание скользящих средних и скользящих сумм
• Реализация накопительных итогов с начала периода

Уровень 4: Экспертные техники и оптимизация (4+ месяцев)

• Глубокое понимание движка вычислений DAX и его оптимизация
• Работа со сложными сценариями "многие ко многим"
• Применение паттернов проектирования DAX для сложных бизнес-задач
• Разработка решений для когортного анализа и прогнозирования
• Написание переиспользуемых и масштабируемых формул
• Профилирование и отладка производительности DAX-формул

Практические задания для закрепления:

• Создание сложных моделей прогнозирования с учетом сезонности
• Разработка систем оценки и ранжирования (скоринговые модели)
• Эмуляция сложных SQL-запросов средствами DAX
• Оптимизация существующих отчетов для улучшения производительности

Ресурсы для эффективного обучения DAX:

1. Книги и документация:

   • "The Definitive Guide to DAX" (Marco Russo, Alberto Ferrari) — библия для изучающих DAX
   • "DAX Patterns" (Marco Russo, Alberto Ferrari) — сборник готовых решений
   • Официальная документация Microsoft по DAX

2. Онлайн-курсы:

   • SQLBI.com — курсы от создателей книг по DAX
   • Microsoft Learn — бесплатные модули по Power BI и DAX
   • Specialized Power BI курсы на платформах Udemy и Coursera

3. Сообщества и форумы:

   • Power BI Community
   • SQLBI Forum
   • Stack Overflow (теги power-bi и dax)

Советы для эффективного обучения:

• Работайте с реальными данными — теория без практики быстро забывается
• Ставьте конкретные задачи — например, воссоздать отчет из Excel в Power BI
• Изучайте существующие решения — анализируйте DAX-формулы в готовых шаблонах
• Объясняйте концепции другим — это лучший способ закрепить знания
• Решайте DAX-головоломки — специальные задачи для тренировки навыков
• Участвуйте в челленджах — например, #30DaysOfDAX или #DAXFriday

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

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