DAX (выражения анализа данных) — новый язык формул в PowerPivot, который включает в свой арсенал ряд стандартных формул Excel. Помимо них, DAX имеет много общего с другим языком, называемым MDX (многомерные выражения).
Мера – это формула, которая специально создается для числовых значений и попадает в поле Значения сводной таблицы. Так, когда люди говорят DAX мера, они имеют в виду создание вычисляемого столбца с помощью формулы на языке DAX, для использования в сводной таблице в поле Значения.
Вы могли сталкиваться с вычисляемыми столбцами ранее, при создании обычной сводной таблицы в Excel. Но возможности таких столбцов ограничены. DAX меры могут использовать данные других вычисляемых столбцов. Например, можно создать один вычисляемый столбец для извлечения номера из текстовой строки, а затем использовать это число в другом вычисляемом столбце.
Добавление вычисляемого столбца
Щелчок по иконке Добавить, находящегося в группе Столбцы вкладки Конструктор, это то же самое, если вы выделите правый столбец Добавление столбца. Таким образом, если вы хотите добавить новую колонку, просто выберите любую пустую ячейку в правом столбце.
Формулы, используемые в вычисляемых столбцах, пишутся на языке DAX. После того, как вы ввели формулу и нажали клавишу Enter, вся колонка будет заполнена этой формулой.
Колонка может содержать только одну формулу.
Операторы языка DAX
Язык DAX поддерживает несколько операторов. Многие из них аналогичны операторам Excel:
- +-*/ объяснений не требуется, сложение, вычитание, умножение и деление
- & конкатенация (сцепление текста)
- ^ возведение в степень
- =, >, <, >=, <=, <> операторы сравнения
К тому же DAX поддерживает три дополнительных оператора для оценки логических выражений:
- && создает оператор «И», между двумя выражениями со значениями ПРАВДА/ЛОЖЬ. ([Регион]=”Запад”)&&([Продукт]=”ABC”).
- || создает оператор «ИЛИ», между двумя выражениями со значениями ПРАВДА/ЛОЖЬ. ([Регион]=”Запад”)||([Продукт]=”ABC”).
- ! создает оператор «НЕ». Изменяет значение ПРАВДА/ЛОЖЬ на противоположенное. !([Регион]=”Запад”)
Создание формул в PowerPivot
Для начала ввода формулы, выделите столбец Добавление столбца и введите знак равенства «=».
На этом этапе вы можете:
Выбрать колонку и PowerPivot введет название колонки в формулу
Ввести оператор
Щелкнуть по иконке fx, которая вызовет мастер вставки функции.
Вы можете начать вводить формулу или название таблицы. Система автоматического заполнения предложит список возможных названий таблиц или формул в зависимости от букв, которые вы уже ввели.
Добавление вычисляемого столбца с DAX формулой
Недостатком сводных таблиц, созданных в PowerPivot, является невозможность группировки дат по месяцам, годам и т.д. Прежде чем создавать сводную таблицу, давайте добавим вычисляемое поле в таблицу Demo с использованием формулы DAX.
- Выберите лист Demo в окне PowerPivot.
- Щелкните по первой пустой ячейке правой колонки Добавить столбец.
- Щелкните по иконке fx, находящейся правее поля ввода формул. Появится диалоговое окно Вставить функцию с категориями Дата и время, Арифметические и тригонометрические операции, Статистические, Текст, Логические, Фильтр, Информация, Родители-потомки. Выберите категорию Дата и время из выпадающего списка. Вы обнаружите, что появившийся список не похож на список функций Excel. Первые 5 из 6 отображенных функций в списке — необычные и новые.
- К счастью, некоторые знакомые функции все же присутствуют в списке. Прокрутите вниз и выберите функцию YEAR (ГОД). Щелкните по первой ячейке в колонке Date. PowerPivot заполнит формулу =YEAR(demo[Date]. Закройте скобки и нажмите Enter, PowerPivot заполнит столбец формулами с соответствующими датами.
- Щелкните правой кнопкой мыши по вычисляемому столбцу, выберите Переименовать. И назовите колонку Год.
- Повторите процесс, чтобы добавить колонку с месяцем, используя формулу =MONTH(demo[Date])
Мы добавили два вычисляемых столбца с использованием формул DAX. Следующим этапом в изучении PowerPivot будет создание сводной таблицы.
Файл с примером данных для создания вычисляемых столбцов.
День добрый!
У меня возникла проблема/ошибка/глюк при написании формул в Power Piviot:
он не воспринимает знак » ; » как разделитель между аргументами в формуле, а требует знак » , » (как в американской версии Excel). При этом если я буду писать с » , » при завершении формулы он выдает ошибку, и тогда уже вручную надо все заменять на » ; «. Сталкивались ли Вы когда-нибудь с подобным? Что можно сделать?
Различные способы переустановки ( через Панель Управления, с помощью спец.программы Microsoft) результата не дали.
Ксения, с подобной проблемой не сталкивался, формулы воспринимают точку с запятой как должное, поэтому конкретного посоветовать не могу. Как вариант, попробуйте переустановить Excel
Подскажите пожалуйста как выполнить условную функцию по дате?
Цель: объединить данные в таблице с фактом с таблицой с планом в одну таблицу.
Не зная как это сделать инчае, решил воспользоваться условной функцией if и отталкиваться при объединении данных из двух таблиц от текущей даты с помощью TODAY()
=if(Табл 1[Дата]<=TODAY();sum(Табл 1[план]);Табл 1[факт]))
ругается: "Ошибка вычисления в мере 'tbl_plan_fact'[Факт]: В текущем контексте невозможно определить значение для столбца "oper_date" в таблице "tbl_hDataCalc". Проверьте, чтобы существовали все столбцы, на которые имеются ссылки в выражении вычисления, и не было циклических зависимостей. Это может также произойти, если формула для меры ссылается непосредственно на столбец без выполнения на этом столбце каких-либо агрегирующих функций, таких как sum, average или count. Столбец не имеет единственного значения; он имеет много значений, по одному для каждой строки таблицы, но ни одна строка не указана."
Пробую поставить так
=if(AVERAGE(Табл 1[Дата])<=TODAY();sum(Табл 1[план]);Табл 1[факт])), ошибок нет, но данные ложатся неверно
Help please
причем функцию прописываю в вычисляемом поле
спасибо за статью.
как что то вводное нормально, но свою типичную ситуацию я в ней не нашел
например раньше, подключившись к внешнему многомиллионному источнику данных, создав сводную таблицу я запросто мог создать типичное вычисляемое поле
, сейчас после добавления данных к «модели данных» возможность создавать вычисляемые поля исчезла
а сделать предложенными вами способами нельзя, т.к. не будет группировки
вот например предложенный вами пример создания вычисляемой колонки делает вот так,
(a1/b1)+(a2/b2)+(a3/b3)+(a3/b3)+…..+(aN/bN)
,а что делать если нужно вот так?
sum[(a1+a2+a3+a4+a5)/(b1+b2+b3+b4+b5)] с группировкой по категориям
, это для того чтоб померить эффективность различных сотрудников в компании
Добрый вечер, уважаемые. У меня проблема, есть таблицы с данными «план» и «факт», они имеют связь через таблицу дат (день, месяц, квартал, год). Подскажите пожалуйста как можно произвести простые вычисления план-факта в PowerPivot? нужно вычислить разницу между планом и фактом за отчетный период (план / факт и план — факт). Спасибо.