Функции создания вычисляемых столбцов и таблиц в DAX: ADDCOLUMNS И DATATABLE в Power BI и Power Pivot

до конца распродажи осталось:

Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

DAX функция ADDCOLUMNS
DAX функция DATATABLE

Антон Будуев Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы разберем функции создания вычисляемых столбцов и таблиц в DAX — ADDCOLUMNS И DATATABLE.

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

В то время, как в Power Pivot (надстройки Excel), вычисляемые таблицы физически в модели данных создавать нельзя. В Powerpivot вычисляемые таблицы создаются только виртуально во время вычисления самих формул.

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

Но, к слову сказать, функцию ADDCOLUMNS мы также будем рассматривать в Power BI, просто потому, что там работать с DAX удобнее. Итак, переходим к разбору функций.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

Да, и еще один момент, в рамках распродажи до 30 апреля 2024 г. у Вас имеется возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 50% (вместо 10000, всего за 5000 руб.)

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

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 50% (до 30 апреля 2024 г.): узнать подробнее

до конца распродажи осталось:

DAX функция ADDCOLUMNS в Power BI и Power Pivot

ADDCOLUMNS () — название этой функции говорит само за себя: добавить столбец. Она создает вычисляемый столбец в таблице.

На просторах Интернета я часто встречаю, что некоторые пользователи пишут эту формулу так: addcolumn (без последней буквы s) или add columns (в два раздельных слова) — что совершенно неправильно. Правильное написание этой функции ADDCOLUMNS.

Синтаксис:

ADDCOLUMNS (
    'Таблица'; 
    "Имя столбца 1"; Выражение 1;
    "Имя столбца 2"; Выражение 2;
    "..."; ...;
    "Имя столбца N"; Выражение N
)

Где:

‘Таблица’ — существующая таблица или табличное выражение к которому добавляется столбец
«ИмяСтолбца» — имя создаваемого столбца
Выражение — вычисляемое выражение для каждой строчки создаваемого столбца

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

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

Для понимания сути работы DAX функции ADDCOLUMNS, рассмотрим следующий пример.

В модели данных Power BI Desktop имеется таблица «Продажи», содержащая дату и сумму продажи:

Исходная таблица

Задача: написать формулу суммы продаж только за апрель месяц. Без создания дополнительного вычисляемого столбца [Месяц] в физической памяти модели данных.

Для того, чтобы столбец [Месяц] не создавать в физической памяти модели данных, воспользуемся DAX функцией ADDCOLUMNS, которая добавит этот вычисляемый столбец к таблице виртуально. При этом, формула суммы продаж за апрель будет ссылаться именно на виртуальный столбец [Месяц], этим самым, мы не будем загружать оперативную память ПК.

Итак, формула с использованием ADDCOLUMNS будет такой:

/* 0 */   СуммаАпрель = 
/* 1 */   SUMX(
/* 2 */       FILTER(
/* 3 */           ADDCOLUMNS(
/* 4 */               'Продажи';
/* 5 */               "Месяц";
/* 6 */               FORMAT ('Продажи'[Дата]; "MMMM")
/* 7 */           );
/* 8 */          [Месяц] = "Апрель"
/* 9 */      );
/* 10 */   'Продажи'[СуммаПродаж]
/* 11 */   )

Для удобства объяснения этой формулы, я каждую строку пронумеровал.

В 0 строке объявляется имя создаваемой меры.

В 1 строке вызывается функция SUMX, которая и будет считать сумму столбца [СуммаПродаж] (в строке 10).

Строки 2-9 в коде формулы являются входящей таблицей в SUMX (первым ее параметром). Эта таблица создается при помощи функции FILTER, в которую, также подается таблица (строки 3-7) и затем эта поданная таблица фильтруется по условию столбец [Месяц] = «Апрель» (строка 8).

По какому столбцу [Месяц] функция FILTER будет фильтровать таблицу? И какую вообще таблицу она будет фильтровать?

Эта таблица — это не что иное, как исходная таблица «Продажи» в которой был виртуально добавлен столбец [Месяц] при помощи функции ADDCOLUMNS (3-7 строки).

Разберем построчно этот код с ADDCOLUMNS (3-7 строки).

В качестве первого параметра (4 строка) мы прописали исходную таблицу «Продажи».
Вторым параметром (строка 5) мы прописали имя создаваемого виртуального столбца [Месяц].
В третьем параметре (6 строка) указано само выражение для расчета создаваемого столбца. И это выражение состоит из функции FORMAT, которая, в свою очередь, из столбца [Дата] исходной таблицы «Продажи» возвратила наименование месяца. Этот месяц и был прописан в виртуальном столбце, созданным ADDCOLUMNS.

Итог работы этой формулы будет такой:

Результат работы формулы на основе DAX функции ADDCOLUMNS

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

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

DAX функция DATATABLE в Power BI и Power Pivot

DATATABLE () — возвращает таблицу с нужными данными (позволяет создать таблицу «с нуля»).

Синтаксис:

DATATABLE (
    "Имя столбца 1"; Тип Данных Столбца 1;
    "Имя столбца 2"; Тип Данных Столбца 2;
    "Имя столбца N"; Тип Данных Столбца N;
    {
        {Значение 1; Значение 2; Значение N};
        {Значение 1; Значение 2; Значение N};
        {Значение 1; Значение 2; Значение N};
        {Значение 1; Значение 2; Значение N}
    }
)

Где:

«Имя столбца» — имя создаваемого столбца в таблице
Тип Данных Столбца — тип данных создаваемого столбца: INTEGER (целые числа), DOUBLE (дробные числа), STRING (текстовая строка), BOOLEAN (логический тип), CURRENCY (денежный тип), DATETIME (дата и время)
{Значение 1; Значение 2; Значение 3} — строки создаваемой таблицы
Значение 1 — любое значение или выражение, соответствующие создаваемому столбцу и его типу данных

Пример формулы на основе использования DAX функции DATATABLE.

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

В Power BI Desktop во вкладке «Моделирование» создадим вычисляемую таблицу по следующей формуле на основе функции DATATABLE:

/*0*/ Таблица = 
/*1*/ DATATABLE (
/*2*/     "Менеджер"; STRING;
/*3*/     "Продажи"; CURRENCY;
/*4*/     {
/*5*/         {"Сидоров"; 31000};
/*6*/         {"Петров"; 15000};
/*7*/         {"Свиридова"; 35000}
/*8*/    }
/*9*/ )

В данной формуле в качестве параметров DATATABLE во 2 и 3 строках мы прописали имена создаваемых столбцов и рядом их типы данных.

Фигурные скобки в 4 и 8 строках говорят о том, что внутри располагаются строки создаваемой таблицы.

5, 6, 7 номера — это непосредственно 3 строки таблицы, внутри которых расположены значения в соответствии со структурой ранее прописанных столбцов.

Результатом выполнения этой формулы на основе DAX функции DATATABLE, будет созданная в модели данных в Power BI вычисляемая таблица:

Результат работы формулы на основе DAX функции DATATABLE

На этом, с разбором DAX функций ADDCOLUMNS И DATATABLE в Power BI и Power Pivot, все.

Также, напоминаю Вам, в рамках распродажи до 30 апреля 2024 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 50% (вместо 10000, всего за 5000 руб.)

До конца распродажи осталось:

Пожалуйста, оцените статью:

(19 голосов, в среднем: 4.7 из 5 баллов)

[Экспресс-видеокурс] Быстрый старт в языке DAX

Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.

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

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