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

DAX (выражения анализа данных) — новый язык формул в PowerPivot, который включает в свой арсенал ряд стандартных формул Excel. Помимо них, DAX имеет много общего с другим языком, называемым MDX (многомерные выражения).

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

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

Добавление вычисляемого столбца

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

Добавить вычисляемый столбец

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

Колонка может содержать только одну формулу.

Операторы языка DAX

Язык DAX поддерживает несколько операторов. Многие из них аналогичны операторам Excel:

  • +-*/ объяснений не требуется, сложение, вычитание, умножение и деление
  • & конкатенация (сцепление текста)
  • ^ возведение в степень
  • =, >, <, >=, <=, <> операторы сравнения

К тому же DAX поддерживает три дополнительных оператора для оценки логических выражений:

  • && создает оператор «И», между двумя выражениями со значениями ПРАВДА/ЛОЖЬ. ([Регион]=”Запад”)&&([Продукт]=”ABC”).
  • || создает оператор «ИЛИ», между двумя выражениями со значениями ПРАВДА/ЛОЖЬ. ([Регион]=”Запад”)||([Продукт]=”ABC”).
  • ! создает оператор «НЕ». Изменяет значение ПРАВДА/ЛОЖЬ на противоположенное. !([Регион]=”Запад”)

Создание формул в PowerPivot

Для начала ввода формулы, выделите столбец Добавление столбца и введите знак равенства «=».

На этом этапе вы можете:

Выбрать колонку и PowerPivot введет название колонки в формулу

Название колонки в формуле

Ввести оператор

Щелкнуть по иконке fx, которая вызовет мастер вставки функции.

Мастер вставки функции

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

Автозаполнение DAX

Добавление вычисляемого столбца с DAX формулой

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

  1. Выберите лист Demo в окне PowerPivot.
  2. Щелкните по первой пустой ячейке правой колонки Добавить столбец.
  3. Щелкните по иконке fx, находящейся правее поля ввода формул. Появится диалоговое окно Вставить функцию с категориями  Дата и время, Арифметические и тригонометрические операции, Статистические, Текст, Логические, Фильтр, Информация, Родители-потомки. Выберите категорию Дата и время из выпадающего списка. Вы обнаружите, что появившийся список не похож на список функций Excel. Первые 5 из 6 отображенных функций в списке — необычные и новые.Дата и время формулы DAX
  4. К счастью, некоторые знакомые функции все же присутствуют в списке. Прокрутите вниз и выберите функцию YEAR (ГОД). Щелкните по первой ячейке в колонке Date. PowerPivot заполнит формулу =YEAR(demo[Date]. Закройте скобки и нажмите Enter, PowerPivot заполнит столбец формулами с соответствующими датами.Функция YEAR
  5. Щелкните правой кнопкой мыши по вычисляемому столбцу, выберите Переименовать. И назовите колонку Год.
  6. Повторите процесс, чтобы добавить колонку с месяцем, используя формулу =MONTH(demo[Date])

Функция MONTH

Мы добавили два вычисляемых столбца с использованием формул DAX. Следующим этапом в изучении PowerPivot будет создание сводной таблицы.

Файл с примером данных для создания вычисляемых столбцов.


6 комментариев

  1. День добрый!
    У меня возникла проблема/ошибка/глюк при написании формул в Power Piviot:
    он не воспринимает знак » ; » как разделитель между аргументами в формуле, а требует знак » , » (как в американской версии Excel). При этом если я буду писать с » , » при завершении формулы он выдает ошибку, и тогда уже вручную надо все заменять на » ; «. Сталкивались ли Вы когда-нибудь с подобным? Что можно сделать?
    Различные способы переустановки ( через Панель Управления, с помощью спец.программы Microsoft) результата не дали.

  2. Подскажите пожалуйста как выполнить условную функцию по дате?
    Цель: объединить данные в таблице с фактом с таблицой с планом в одну таблицу.
    Не зная как это сделать инчае, решил воспользоваться условной функцией 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

  3. спасибо за статью.
    как что то вводное нормально, но свою типичную ситуацию я в ней не нашел

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

    , сейчас после добавления данных к «модели данных» возможность создавать вычисляемые поля исчезла

    а сделать предложенными вами способами нельзя, т.к. не будет группировки
    вот например предложенный вами пример создания вычисляемой колонки делает вот так,
    (a1/b1)+(a2/b2)+(a3/b3)+(a3/b3)+…..+(aN/bN)

    ,а что делать если нужно вот так?
    sum[(a1+a2+a3+a4+a5)/(b1+b2+b3+b4+b5)] с группировкой по категориям

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

  4. Добрый вечер, уважаемые. У меня проблема, есть таблицы с данными «план» и «факт», они имеют связь через таблицу дат (день, месяц, квартал, год). Подскажите пожалуйста как можно произвести простые вычисления план-факта в PowerPivot? нужно вычислить разницу между планом и фактом за отчетный период (план / факт и план — факт). Спасибо.

Добавить комментарий для denis Отменить ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *