В этой статье мы рассмотрим основы создания формул вычислений для вычисляемых столбцов и мер в Power Pivot. Если вы новичок в DAX, обязательно проведите проверку Краткое руководство: Изучите основы DAX за 30 минут.
Основные сведения о формулах
Power Pivot предоставляет выражения анализа данных (DAX) для создания пользовательских вычислений в сводных таблицах Power Pivot и Excel. В состав DAX входят некоторые функции, используемые в формулах Excel, а также дополнительные функции, предназначенные для работы с реляционными данными и динамического агрегирования.
Вот несколько основных формул, которые можно использовать в вычисляемом столбце:
| Формула | Описание |
|---|---|
| =СЕГОДНЯ() | Сегодняшняя дата вставляется во все строки столбца. |
| =3 | Вставляет значение 3 в каждую строку столбца. |
| =[Столбец1] + [Столбец2] | Суммирует значения в одной строке [Столбец1] и [Столбец2] и помещает результаты в ту же строку вычисляемого столбца. |
Формулы Power Pivot можно создавать для вычисляемых столбцов так же, как формулы в Microsoft Excel.
При создании формулы выполните следующие действия:
- Каждая формула должна начинаться со знака равенства.
- Вы можете ввести или выбрать имя функции или ввести выражение.
- Начните вводить первые несколько букв названия нужной функции или названия, после чего функция автозаполнения отобразит список доступных функций, таблиц и столбцов. Нажмите клавишу TAB, чтобы добавить в формулу элемент из списка автозаполнения.
- Нажмите кнопку Fx , чтобы отобразить список доступных функций. Чтобы выбрать функцию из раскрывающегося списка, выделите ее с помощью клавиш со стрелками и нажмите кнопку "ОК ", чтобы добавить функцию в формулу.
- Укажите аргументы функции, выбрав их из раскрывающегося списка возможных таблиц и столбцов или введя значения или другую функцию.
- Проверьте на наличие синтаксических ошибок: убедитесь, что все скобки закрыты, а ссылки на столбцы, таблицы и значения указаны правильно.
- Нажмите клавишу ВВОД, чтобы подтвердить формулу.
Примечание
В вычисляемом столбце заполняется значениями сразу после принятия формулы. В мере нажатие клавиши ВВОД сохраняет определение меры.
Создание простой формулы
| Создание вычисляемого столбца с помощью простой формулы ДатаПродажиПодкатегорияПродажаКоличество1/5/2009АксессуарыФутляр для переноски254995681/5/2009АксессуарыЗарядное устройство для мини-аккумулятора1099.56441/5/2009DigitalSlim Digital6512441/6/2009АксессуарыТелеобъектив для переназначения1662.5181/6/2009АксессуарыШтатив938.34181/6/2009АксессуарыUSB-кабель1230.2526
|
|---|
Советы по использованию функции автозаполнения
- Функцию автозаполнения формул можно использовать в середине существующей формулы со вложенными функциями. Текст, расположенный непосредственно перед точкой вставки, используется для отображения значений раскрывающегося списка, а остальной текст остается без изменений.
- В Power Pivot не добавляются закрывающие круглые скобки для функций и скобки не сопоставляются автоматически. Необходимо убедиться, что каждая функция синтаксически верна, иначе вам не удастся сохранить или использовать формулу. Power Pivot выделяет круглые скобки, что упрощает проверку, если они правильно закрыты.
Работа с таблицами и столбцами
Таблицы Power Pivot похожи на таблицы Excel, но отличаются способом работы с данными и формулами.
- Формулы в Power Pivot работают только с таблицами и столбцами, а не с отдельными ячейками, ссылками на диапазоны или массивами.
- Формулы могут использовать связи для получения значений из связанных таблиц. Извлекаемые значения всегда связаны со значением текущей строки.
- Формулы Power Pivot невозможно вставить в лист Excel и наоборот.
- Вы не можете иметь нерегулярные или "неровные" данные, как на листе Excel. Каждая строка в таблице должна содержать одинаковое количество столбцов. Однако в некоторых столбцах могут содержаться пустые значения. Таблицы данных Excel и Power Pivot не взаимозаменяемы, но вы можете связывать таблицы Excel из Power Pivot и вставлять данные Excel в Power Pivot. Дополнительные сведения см. в статьях Добавление данных листа в модель данных с помощью связанной таблицы и Копирование и вставка строк в модель данных в Power Pivot.
Ссылки на таблицы и столбцы в формулах и выражениях
На любую таблицу и любой столбец можно ссылаться, используя его имя. Например, следующая формула иллюстрирует создание ссылки на столбцы из двух таблиц с использованием полного имени:
=СУММ('Новые продажи'[Сумма]) + СУММ('Прошлые продажи'[Сумма])
При вычислении формулы Power Pivot сначала проверяет общий синтаксис, а затем сравнивает имена указанных столбцов и таблиц с возможными столбцами и таблицами в текущем контексте. Если имя неоднозначное или не удается найти столбец или таблицу, появится сообщение об ошибке формулы (#ERROR строка вместо значения данных в ячейках, в которых возникает ошибка). Дополнительные сведения о требованиях к именованию таблиц, столбцов и других объектов см. в статье "Требования к именованию в спецификации синтаксиса DAX для Power Pivot.
Примечание
Контекст — важная функция моделей данных Power Pivot, позволяющая создавать динамические формулы. Контекст определяется таблицами в модели данных, связями между таблицами и любыми примененными фильтрами. Дополнительные сведения см. в статье Контекст в формулах DAX.
Отношения между таблицами
Таблицы могут быть связаны с другими таблицами. Создание связей дает возможность искать данные в другой таблице и использовать связанные значения для выполнения сложных вычислений. Например, в вычисляемом столбце можно найти все записи о доставке, связанные с текущим торговым посредником, и затем вычислить сумму затрат на доставку для каждой из них. Результат похож на параметризованный запрос: можно вычислить разную сумму для каждой строки текущей таблицы.
Многим функциям DAX требуется связь между таблицами или несколькими таблицами, чтобы найти столбцы, на которые вы ссылаетесь, и вернуть соответствующие результаты. Другие функции будут пытаться определить связь; Однако для достижения наилучших результатов всегда следует создавать отношения там, где это возможно.
Для правильного вычисления итоговых данных при работе со сводными таблицами особенно важно соединить между собой все таблицы, которые используются в сводной таблице. Дополнительные сведения см. в статье Работа со связями в сводных таблицах.
Устранение ошибок в формулах
Если при определении вычисляемого столбца возникает ошибка, формула может содержать синтаксическую или семантическую ошибку.
Синтаксические ошибки устранять проще всего. Они обычно вызваны пропущенной скобкой или запятой. Дополнительные сведения о синтаксисе отдельных функций см. в справочнике по функциям DAX.
Ошибки другого типа возникают, когда синтаксис задан правильно, но значение упоминаемого столбца не имеет смысла в контексте формулы. Такие семантические ошибки могут быть вызваны следующими проблемами:
- Формула ссылается на несуществующий столбец, таблицу или функцию.
- Формула кажется правильной, но при получении данных Power Pivot обнаруживает несоответствие типов и выводит сообщение об ошибке.
- Формула передает функции неверное число или тип параметров.
- Формула ссылается на другой столбец, который содержит ошибку, поэтому ее значения недопустимы.
- Формула ссылается на столбец, который не был обработан. Такое может произойти, если вы переключили книгу в ручной режим, внесли изменения, а затем не обновили данные или не обновили вычисления.
В первых четырех случаях DAX помечает весь столбец, содержащий недопустимую формулу. В последнем случае DAX выделяет имя столбца серым цветом, чтобы показать, что он находится в необработанном состоянии.