Общие сведения по выражениям анализа данных (DAX)

Статья
03/18/2015

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

В этом разделе описываются следующие основные понятия.

Использование формул DAX
Создание формул DAX
Типы операций, которые можно выполнять с помощью формул DAX

Общие сведения о формулах DAX

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

Однако формулы DAX отличаются по следующим важным параметрам.

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

Использование формул

Формулы DAX можно использовать в сводных таблицах Excel (PowerPivot или Pivot).

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

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

Создание формул при помощи строки формул

PowerPivot, как и Excel, имеет строку формул, упрощающую создание и изменение формул, а также функцию автозаполнения для сокращения числа ошибок ввода данных и синтаксических ошибок.

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

Ввод имени столбца. Откройте скобку и выберите столбец из списка столбцов в текущей таблице. Для столбца из другой таблицы введите первые буквы имени таблицы и затем выберите столбец из раскрывающегося списка автозаполнения.

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

Советы по использованию автозаполнения

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

Использование нескольких функций в одной формуле

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

Многие функции PowerPivot предназначены для использования исключительно в качестве вложенных. Эти функции возвращают таблицу, которую нельзя непосредственно сохранить в книге PowerPivot, но необходимо передать табличной функции в качестве входного аргумента. Например, в качестве первого аргумента функций SUMX, AVERAGEX и MINX требуется таблица.

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

Сравнение функций DAX с функциями Excel

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

Многие функции DAX, имена которых совпадают с именами функций Excel, в целом работают схожим образом, однако они доработаны, чтобы принимать входные аргументы других типов, и в некоторых случаях могут возвращать другие типы данных. Обычно формулы DAX нельзя применять в книге Excel, а формулы Excel нельзя применять в книге PowerPivot без некоторых изменений.
Функции DAX никогда не принимают в качестве аргумента диапазон ячеек или ссылку на диапазон, но принимают в качестве аргумента столбец или таблицу.
Функции даты и времени DAX возвращают данные типа datetime. В противоположность этому функции даты и времени Excel возвращают целые числа, представляющие дату в виде порядкового номера.
Многие функции DAX возвращают таблицу значений или выполняют вычисления на основании входной таблицы значений. В противоположность этому в Excel нет функций, возвращающих таблицу, хотя некоторые функции могут работать с массивами. Способность легко создавать ссылки на полные таблицы и столбцы является новой функциональной возможностью PowerPivot.
DAX поддерживает новые функции поиска, аналогичные функциям поиска массива и вектора в Excel. Однако для функций DAX требуется, чтобы таблицы были связанными.
Тип данных Excel variant не поддерживается в DAX. Предполагается, что данные в столбце всегда имеют один и тот же тип данных. Если в столбце представлены данные разных типов, DAX меняет тип данных всего столбца на тот, который наиболее полно соответствует всем значениям.

В начало

Типы данных DAX

Данные на лист PowerPivot можно импортировать из множества различных источников данных, которые могут поддерживать различные типы данных. При импорте или загрузке данных в книгу и последующем использовании их в вычислениях или в сводных таблицах данные неявным образом преобразуются в один из типов данных PowerPivot. Список типов данных см. в разделе Типы данных, поддерживаемые в книгах PowerPivot.

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

В начало

Формулы и реляционная модель

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

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

Однако поскольку формулы DAX могут работать с целыми таблицами и столбцами, необходимо строить вычисления не так, как это делается в Excel.

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

В начало

Вычисляемые столбцы и меры

Можно создать формулы в PowerPivot, или в вычисляемых столбцах, или в мерах.

Вычисляемые столбцы

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

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

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

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

Вычисляемые столбцы

Меры

Мера — это формула, которая создается специально для использования в сводной таблице или сводной диаграмме, использующей данные PowerPivot. Меры могут быть основаны на стандартных агрегатных функциях, например COUNT или SUM, либо на пользовательских формулах на языке выражений анализа данных (DAX). Мера используется в области Значения сводной таблицы. Чтобы разместить вычисленные результаты в другой области сводной таблицы, необходимо использовать вычисляемый столбец.

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

Определение созданной меры сохраняется вместе с таблицей исходных данных. Оно отображается в списке Список полей PowerPivot и доступно для всех пользователей книги.

Создание меры в сводной таблице или сводной диаграмме

В начало

Обновление результатов формул

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

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

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

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

Дополнительные сведения см. в следующих разделах:

В начало

Совместимость с табличными моделями служб Analysis Services и режимом DirectQuery

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

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

Дополнительные сведения см. в разделе https://go.microsoft.com/fwlink/?LinkId=219172.