Основы DAX в Power BI Desktop

Это руководство предназначено для пользователей, не знакомых с Power BI Desktop. Оно предоставляет краткие и простые вводные сведения о том, как можно использовать язык выражений анализа данных (DAX) для решения ряда задач анализа данных и основных вычислений. Здесь будут предоставлены некоторые концептуальные сведения, ряд задач, которые вы можете выполнить, и несколько контрольных вопросов для проверки, что вы узнали. После изучения этой статьи вы должны хорошо понимать наиболее важные основные понятия в DAX.

Что такое DAX

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

Почему так важен DAX?

Довольно просто создать новый файл Power BI Desktop и импортировать в него некоторые данные. Можно даже создать отчеты, показывающие ценные сведения, без использования каких-либо формул DAX вообще. Но что делать, если необходимо проанализировать процент роста по категориям продуктов, а также для разных диапазонов дат? Или если требуется вычислить годовой рост в сравнении с тенденциями рынка? Формулы DAX предоставляют такую возможность, как и многие другие важные возможности. Научившись создавать эффективные формулы DAX, вы сможете наиболее эффективно использовать свои данные. Получив нужную информацию, вы сможете начать решать реальные бизнес-задачи, влияющие на производительность. Это преимущество Power BI, и DAX поможет вам получить его.

Предварительные требования

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

Основное внимание мы уделим объяснению формул DAX, используемых в вычислениях, в частности в мерах и вычисляемых столбцах. Вы должны быть знакомы с использованием Power BI Desktop, уметь импортировать данные, добавлять поля в отчет, а также понимать основные принципы мер и вычисляемых столбцов.

Пример книги

Лучший способ изучения DAX — создать некоторые простые формулы, использовать их с реальными данными и его фактическими данными и посмотреть результаты. В примерах и задачах здесь используется пример файла Contoso Sales for Power BI Desktop. Это тот же файл примера, который используется в статье Учебник. Создание собственных мер в Power BI Desktop.

Давайте начнем!

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

Синтаксис

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

DAX formula syntax

Эта формула содержит следующие элементы синтаксиса.

A. Имя меры Total Sales (Всего продаж).

Б. Оператор знака равенства ( = ), который обозначает начало формулы. При вычислении он будет возвращать результат.

В. Функция DAX SUM, которая складывает все числа в столбце Sales[SalesAmount]. Более подробно функции будут рассматриваться позднее.

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

Д. Ссылочная таблица Sales (Продажи).

Е. Ссылочный столбец [SalesAmount] в таблице Sales (Продажи). С помощью этого аргумента функция СУММ знает, по какому столбцу следует суммировать значения.

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

Для меры с именем "Всего продаж" вычислить (=) СУММУ значений в столбце [SalesAmount] таблицы "Продажи".

При добавлении в отчет эта мера вычисляет и возвращает значения путем суммирования объемов продаж для каждого из полей, которые мы включаем, например, для поля Cell Phones in the USA (Мобильные телефоны в США).

Возможно, вы думаете: "Разве эта мера делает то же самое, как если бы я просто добавить поле SalesAmount в мой отчет?" Ну, да. Однако есть веская причина для создания собственной меры, которая суммирует значения из поля SalesAmount: эту меру можно использовать в качестве аргумента в других формулах. Пока это может показаться несколько запутанным, но по мере роста навыков работы с формулами DAX знание этой меры сделает ваши формулы и модель более эффективными. В дальнейшем вы увидите, как мера Total Sales (Всего продаж) используется в качестве аргумента в других формулах.

Давайте рассмотрим еще некоторые моменты относительно этой формулы. В частности, мы представили функцию СУММ. Функции — это предварительно записанные формулы, которые облегчают выполнение сложных вычислений и манипуляций с числами, датами, временем, текстом и пр. Более подробно функции будут рассматриваться позднее.

Вы также видите столбец [SalesAmount] с префиксом, указывающим таблицу Sales (Продажи), в которой находится этот столбец. Такое указание имени столбца с префиксом в виде имени таблицы называется полным именем столбца. Для ссылочных столбцов в одной и той же таблице не требуется включать имя таблицы в формулу, что позволяет сделать длинные формулы, которые ссылаются на многие столбцы, короче и удобней для чтения. Однако рекомендуется включить имя таблицы в формулы мер, даже если она находится в той же таблице.

Примечание

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

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

Давайте создадим простую формулу. Эта задача поможет вам лучше разобраться в синтаксисе формул и понять, как функция предложений в строке формул может помочь.

Задача: создание формулы меры

  1. Скачайте и откройте файл примера Contoso Sales для Power BI Desktop.

  2. В представлении отчетов в списке полей щелкните правой кнопкой мыши таблицу Sales (Продажи), а затем выберите пункт Создать меру.

  3. В строке формул замените Measure, введя новое имя меры, Previous Quarter Sales (Продажи за прошлый квартал).

  4. После знака равенства введите первые несколько букв CAL, а затем дважды щелкните функцию, которую нужно использовать. В этой формуле нужно использовать функцию CALCULATE.

    Вы будете использовать функцию ВЫЧИСЛИТЬ для фильтрации величин, которые требуется суммировать, по аргументу, переданному в функцию ВЫЧИСЛИТЬ. Такие функции называются вложенными. Функция «ВЫЧИСЛИТЬ» имеет по крайней мере два аргумента. Первый аргумент — это выражение для оценки, а второй — фильтр.

  5. После открывающей скобки ( для функции CALCULATE введите SUM, а затем другую открывающую скобку (.

    Далее нам нужно передать аргумент в функцию SUM.

  6. Начните вводить Sal, а затем выберите Sales [SalesAmount] и добавьте закрывающую скобку ).

    Это первый аргумент-выражение для нашей функции ВЫЧИСЛИТЬ.

  7. Введите запятую (,) и пробел, чтобы задать первый фильтр, а затем введите PREVIOUSQUARTER.

    Функция операций со временем "PREVIOUSQUARTER" будет использоваться для фильтрации результатов суммирования по предыдущему кварталу.

  8. После открывающей скобки ( для функции PREVIOUSQUARTER введите Calendar[DateKey].

    Функция «ПРЕДКВАРТАЛ» имеет один аргумент — столбец, содержащий непрерывный диапазон дат. В нашем случае это столбец DateKey из таблицы Calendar.

  9. Завершите оба аргумента, передаваемые в функции PREVIOUSQUARTER и CALCULATE, двумя закрывающими круглыми скобками )).

    Теперь ваша формула должна выглядеть следующим образом:

    Продажи за прошлый квартал = ВЫЧИСЛИТЬ(СУММ(Sales[SalesAmount]) ПРЕДКВАРТАЛ(Calendar[DateKey]))

  10. Установите флажок Checkmark icon в строке формул или нажмите клавишу ВВОД, чтобы проверить формулу и добавить ее в таблицу Sales.

Вы справились! Вы только что создали сложную меру с помощью DAX, и при этом не ее одну. Эта формула будет вычислять суммарный объем продаж за предыдущий квартал в зависимости от фильтров, применяемых в отчете. Например, если мы поместим SalesAmount и новую меру "Продажи за предыдущий квартал" из таблицы "Продажи" в гистограмму с группировкой, то в таблице "Календарь" добавьте год в качестве среза и выберите 2011, а затем добавьте QuarterOfYear в качестве другого среза и выберите 4, мы получим диаграмму следующим образом:

Previous Quarter Sales and SalesAmount chart

Имейте в виду, что пример модели содержит только небольшой объем данных о продажах с 1.01.2011 по 19.01.2013. Если выбрать год или квартал, в котором не удается суммировать SalesAmount, или новая мера не может вычислить данные о продажах за текущий или предыдущий квартал, данные за этот период не отображаются. Например, при выборе 2011 года и 1 для QuarterOfYear данные для продаж за предыдущий квартал не отображаются, так как данные за четвертый квартал 2010 г. отсутствуют.

Вы только что ознакомились с несколькими важными аспектами формулы DAX.

  • Эта формула включает две функции. Функция операций со временем PREVIOUSQUARTER вложена в качестве аргумента, передаваемого в функцию фильтрации CALCULATE.

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

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

  • Вы использовали функцию CALCULATE. Это одна из наиболее мощных функций в DAX. При разработке моделей и создании более сложных формул вы скорее всего многократно будете использовать эту функцию. Хотя обсуждение функции CALCULATE выходит за рамки этой статьи, по мере освоения DAX стоит обратить на эту функцию особое внимание.

Быстрый тест по синтаксису

  1. Что делает эта кнопка в строке формул?

    Button selection

  2. Что всегда окружает имя столбца в формуле DAX?

Ответы приведены в конце этой статьи.

Функции

Функции являются предопределенными формулами, которые выполняют вычисления с использованием специальных значений, именуемых аргументами, в определенном порядке или структуре. Аргументами могут быть другие функции, другие формулы, выражения, ссылки на столбцы, числа, текст, логические значения, такие как TRUE и FALSE, или константы.

В DAX имеются следующие категории функций: Дата и время, Логика операций со временем, Информационные, Логические, Математические, Статистические, Текстовые, Родительские/дочерние и Прочие. Если вы знакомы с функциями в формулах Excel, многие из функций в DAX будут казаться вам аналогичными. Однако функции DAX уникальны в следующем.

  • Функция DAX всегда ссылается на столбец или таблицу целиком. В формулу можно добавить фильтры, чтобы использовать только определенные значения из таблицы или столбца.

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

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

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

  • В Excel есть популярная функция VLOOKUP (ВПР). Функции DAX не принимают в качестве ссылки ячейку или диапазон ячеек, в отличие от функции VLOOKUP в Excel. Функции DAX принимают в качестве ссылки столбец или таблицу. Не забывайте, что в Power BI Desktop вы работаете с реляционной моделью данных. Поиск значений в другой таблице довольно прост; в большинстве случаев вообще не требуется создавать какие-либо формулы.

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

Быстрый тест по функциям

  1. На что всегда ссылается функция?
  2. Может ли формула содержать несколько функций?
  3. Какую категорию функций вы бы использовали для объединения двух строк текста в одну строку?

Ответы приведены в конце этой статьи.

Контекст

Контекст является одним из наиболее важных понятий DAX. В DAX есть два типа контекста: контекст строки и контекст фильтра. Сначала рассмотрим контекст строки.

Контекст строки

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

Контекст фильтра

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

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

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

Почему контекст фильтра так важен в DAX? Хотя контекст фильтра проще всего применять путем добавления полей в визуализацию, его также можно использовать в формуле DAX путем определения фильтра с помощью функций, таких как ALL (ВСЕ), RELATED (СВЯЗАНО), FILTER (ФИЛЬТР), CALCULATE (ВЫЧИСЛИТЬ), в отношениях, а также в других мерах и столбцах. Например, рассмотрим следующую формулу в мере с именем Store Sales (Продажи в магазине).

Store Sales measure

Чтобы лучше понять эту формулу, мы можем разбить ее, как и другие формулы.

Эта формула содержит следующие элементы синтаксиса.

A. Имя меры — Store Sales (Продажи в магазине).

Б. Оператор знака равенства ( = ), который обозначает начало формулы.

В. Функция CALCULATE, которая вычисляет выражение, переданное в качестве аргумента, в контексте, который изменяется заданными фильтрами.

Г. В скобки () заключается выражение, содержащее один аргумент или несколько.

Д. Мера [Total Sales] (Всего продаж) в той же таблице, что и выражение. Мера Total Sales (Всего продаж) имеет формулу: = СУММ(Sales[SalesAmount]).

Е. Запятая (,) отделяет первый аргумент-выражение от аргумента-фильтра.

Ж. Полное имя ссылочного столбца Channel[ChannelName]. Это наш контекст строки. Каждая строка в этом столбце указывает канал: Store, Online и т.п.

З. Конкретное значение Store в качестве фильтра. Это наш контекст фильтра.

Эта формула гарантирует, что вычисляется сумма только значений продаж, определенных мерой Total Sales (Всего продаж), только для тех строк, в столбце Channel[ChannelName] которых имеется значение Store, используемое в качестве фильтра.

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

Быстрый тест по контексту

  1. Каковы два типа контекста?
  2. Что такое контекст фильтра?
  3. Что такое контекст строки?

Ответы приведены в конце этой статьи.

Сводка

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

Поскольку DAX используется уже несколько лет в других средствах бизнес-аналитики Майкрософт, таких как Power Pivot и табличные модели служб Analysis Services табличной модели, так что там имеется много полезных сведений. Дополнительные сведения можно также найти в книгах, технических документах и блогах корпорации Майкрософт и ведущих специалистов в области бизнес-аналитики. Также рекомендуется посетить вики-сайт Центра ресурсов DAX в TechNet.

Ответы для кратких тестов

Синтаксис:

  1. Проверяет и вводит меру в модели.
  2. Квадратные скобки [].

Функции

  1. Таблица и столбец.
  2. Да. Формула может содержать до 64 вложенных функций.
  3. Текстовые функции.

Контекст:

  1. Контекст строки и контекст фильтра.
  2. Один или несколько фильтров в вычислении, которое определяет одно значение.
  3. Текущая строка.