Выражения анализа данных (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.