Выражения анализа данных (DAX) в Power Pivot

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

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

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

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

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

Область применения формул DAX

В Power Pivot формулы можно создавать в вычисляемых столбцах или вычисляемых полях.

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

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

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

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

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

Дополнительные сведения см. в статье Вычисляемые столбцы в Power Pivot.

Меры

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

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

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

Дополнительную информацию см. в статье Меры в Power Pivot.

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

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

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

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

Дополнительные сведения и пошаговые инструкции по построению формул см. в статье Создание формул для вычислений в Power Pivot.

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

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

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

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

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

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

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

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

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

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

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

Типы данных DAX

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

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

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

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

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

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

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

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

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

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

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

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

Решение проблем с формулами

Ошибки при записи формул

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

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

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

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

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

Неверные или необычные результаты при ранжировании или упорядочении значений столбца

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

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

Чтобы подтвердить правильность результатов, используйте условные операторы (с функцией ЕСЛИ) для выявления значений "не число" (с возвратом числового значения 0).

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

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

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

Дополнительные сведения см. в документации по табличному моделированию служб Analysis Services в электронном руководстве SQL Server 2012 BooksOnline.