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

Статья
09/10/2013

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

Основные сведения о вычисляемых столбцах

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

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

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

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

Создание вычисляемых столбцов

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

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

=EOMONTH([StartDate],0])

Формула извлекает месяц из столбца StartDate таблицы Promotion из образца книги DAX. Затем для каждой строки в таблице Promotion вычисляется значение на конец месяца. Второй параметр задает число месяцев до или после месяца в дате StartDate. В этом случае 0 означает тот же самый месяц. Например, если столбец StartDate содержит 6/1/2001, значением в вычисляемом столбце будет 6/30/2001.

Сведения об этом образце книги см. в разделе Получение образца данных для PowerPivot.

Примечание
В Windows Vista и Windows 7 функции в окне PowerPivot доступны на ленте, обсуждаемой в данном разделе. В Windows XP функции доступны в наборе меню. Если в Windows XP необходимо просмотреть, каким образом команды меню связаны с командами ленты, см. раздел Пользовательский интерфейс PowerPivot в Windows XP.

В Windows Vista и Windows 7 функции в окне PowerPivot доступны на ленте, обсуждаемой в данном разделе. В Windows XP функции доступны в наборе меню. Если в Windows XP необходимо просмотреть, каким образом команды меню связаны с командами ленты, см. раздел Пользовательский интерфейс PowerPivot в Windows XP.

Создание вычисляемого столбца с помощью автозаполнения

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

Пункт Добавить столбец выделится над пустым крайним правым столбцом, и курсор переместится в строку формул.
Введите знак равенства, а затем нажмите кнопку функции ( fx).
EOMONTH — это функция даты и времени, поэтому выберите Date & Time в раскрывающемся списке Выбор категории.

Функции DAX, доступные в диалоговом окне Вставка функции, сгруппированы по категориям. Чтобы просмотреть полный список доступных функций, выберите Все.
- Для переключения между раскрывающимся списком категорий функций, раскрывающимся списком функций и кнопками ОК и Отмена используйте клавишу TAB.
- Чтобы выбрать категорию функций или отдельную функцию, используйте клавиши СТРЕЛКА ВВЕРХ и СТРЕЛКА ВНИЗ.
- После выбора каждой функции PowerPivot выдает справку по этой функции, включая описание и список обязательных и необязательных аргументов.
В раскрывающемся списке Выбор функции выберите EOMONTH и нажмите кнопку OК.

Строка формул обновится и будет содержать функцию и открытую скобку, а курсор автоматически переместится в место, где должен вводиться следующий аргумент.
```
=EOMONTH( 
```
Подсказка под строкой формул указывает, что функции EOMONTH необходимо два аргумента, первый из которых представляет дату, а второй — числовое значение, равное числу месяцев.
Введите открывающую квадратную скобку [, чтобы вывести список столбцов из текущей таблицы.

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

Для этого примера выберите из списка столбец [StartDate] и нажмите клавишу TAB.

Имя столбца вставляется в формулу следующим образом:
```
=EOMONTH ([StartDate]
```
Введите запятую, значение 0, затем закрывающую скобку.

Конечная формула должна иметь следующий вид:
```
=EOMONTH([StartDate], 0)
```
Нажмите клавишу ВВОД, чтобы принять формулу.

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

Именование вычисляемых столбцов

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

Имя каждого столбца должно быть уникальным в пределах таблицы.
Не следует использовать имена, которые уже использовались внутри одной книги. Хотя допускается, чтобы мера и вычисляемый столбец имели одинаковые имена, в том случае, если они одинаковые, можно легко получить ошибки вычислений. Чтобы исключить случайный вызов меры при обращении к столбцу, всегда используйте полную ссылку на столбец.
Если меняется имя вычисляемого столбца, необходимо также обновить все зависимые от него формулы. Обновление результатов формул происходит автоматически, если не включен режим ручного обновления. Однако эта операция может занять некоторое время.
В именах столбцов и объектов в книге PowerPivot нельзя использовать некоторые символы. Дополнительные сведения см. в подразделе «Требования к именам» раздела Спецификация синтаксиса DAX для PowerPivot.

Переименование или изменение существующего вычисляемого столбца

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

Изменение типа данных

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

Основные сведения о производительности вычисляемых столбцов

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

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

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

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

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