Всем привет! Меня зовут Максим Кушнер, и я занимаюсь BI-разработкой в команде HR-аналитики Лемана Тех. Дашборды, которые создаёт и поддерживает наша команда, охватывают широкий круг HR-процессов компании, в т. ч. состояние и движение персонала, расходы на персонал, продуктивность, контроль и эффективность использования рабочего времени, обучение, профессиональное развитие, вовлечённость, внутренние конкурсы, различные рейтинги и др. Пользователями дашборда могут быть все сотрудники Лемана ПРО — 40 000+ коллег разных подразделений от центрального офиса до распределительных центров. Соответственно, количество различных срезов данных и бизнес-показателей в дашбордах может исчисляться десятками.

И очень часто наши коллеги говорят: «Ваш дашборд, конечно, классный, но нам хочется самим покрутить данные». Другими словами, пользователи хотят построить аналитику в нужных им разрезах и структуре, которые не предусмотрены разработчиком по умолчанию.

Если не пытаться решить эту боль пользователя, то он просто экспортирует сырые данные из дашборда в Excel, где использует инструмент сводных таблиц (pivot tables) для выстраивания аналитики в нужном ему виде. Но тогда встаёт вопрос: зачем нужен такой дашборд (и его разработчики), если пользователь использует его как перевалочный пункт, а основную ценность извлекает из другого инструмента?

Пути решения проблемы, возможности и ограничения

Давайте посмотрим, какие пути решения этой проблемы существуют в Power BI и в чём особенности их реализации с точки зрения пользователя и разработчика.

• В облачной версии Power BI можно использовать инструмент персонализации визуальных элементов (Personalize Visuals): если активировать эту опцию, то при наведении на визуализацию пользователь может перейти в очень похожий на Excel диалог с выбором нужных полей и срезов, также возможно их перетаскивание, изменение типов агрегации, сброс до первоначальных настроек и т. п

К преимуществам данного инструмента относится простота его активации со стороны разработчика — достаточно просто включить данную опцию в параметрах отчёта (Файл — Параметры и Настройки — Параметры — Параметры отчёта — Разрешите читателям отчёта персонализировать визуальные элементы в соответствии с их потребностями), при этом пользователь получает знакомый по Excel функционал. Также несомненным плюсом данной опции является выбор типа визуализации, то есть пользователь может на лету превращать таблицу в линейный график, круговую диаграмму и т. п.

Среди недостатков можно выделить достаточно мелкий по размеру интерфейс для пользователя, также для выбора полей в PersonalizeVisuals показываются все таблицы и меры, которые есть в модели данных, т. е. разработчику необходимо убрать все лишние объекты из поля видимости пользователя и дать сущностям понятные пользователю названия; также неудобство для пользователя может представлять то, что в часто используемой разработчиками схеме модели данных «звезда» нужные сущности могут находиться в разных таблицах, тогда как для пользователя удобно, если бы все нужные поля находились в рамках одной таблицы.

Более подробно про настройки и возможности Personalize Visuals можно почитать по ссылке1, ссылке2.

• Также в облачной версии Power BI и самой последней версии Power BI Desktop для Report Server от сентября 2025 г. доступен функционал параметров поля (Field Parameters) — с помощью данного инструмента (см. вкладку Моделирование — Создание параметра — Поля) можно собрать все нужные измерения в одной таблице и затем использовать поле с набором этих измерений в матрице (графике, гистограмме и тому подобное); при этом с помощью визуализации «Срез» можно управлять составом и порядком измерений, которые определяют нужную пользователю структуру визуализации, как в сводной таблице Excel.

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

К недостаткам Field Parameters можно отнести то, что для пользователя нет очевидной подсказки, в каком порядке собраны измерения в его сводной таблице: порядок следования срезов определяется порядком щелчка на то или иное измерение, что может быть не так явно для пользователя. Соответственно, если пользователь захочет изменить порядок следования измерений, ему следует отщёлкнуть ранее выбранные измерения и собрать структуру визуализации заново.

Более подробно про настройки и возможности Field Parameters можно почитать по ссылке1, ссылке2.

В принципе Personalize Visuals и Field Parameters решают проблему воспроизведения функционала динамических сводных таблиц Excel в Power BI, но следует учитывать следующие нюансы:

• пользователь может использовать Personalize Visuals только в облачной версии продукта, которая в настоящее время не всегда и везде доступна для российского бизнеса в силу разных причин;

• Field Parameters помимо облачной версии появился в самом свежем релизе Power BI Desktop для Report Server от сентября 2025 г., однако, как показывает практика, обновления версий PBI RS могут отличаться нестабильностью, требуют дополнительного тестирования и разрыв между датой выпуска релиза и его реальной адаптацией в инфраструктуре бизнеса может занимать 1-2 года.

В нашей компании нет облачного Power BI, вместо неё мы используем on premise версию продукта Power BI Report Server, где Personalize Visuals и Field Parameters для создания сводных таблиц отсутствуют. Как тогда решать нашу проблему?

• Можно использовать закладки (Bookmarks) — для этого нужно создать несколько вариантов таблицы с различной структурой измерений, затем каждое состояние визуализаций, когда нужная таблица показана, а остальные таблицы спрятаны, нужно привязать к закладкам, а те в свою очередь должны быть привязаны к понятным для пользователя кнопкам.

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

• Использовать возможности DAX для создания дополнительных таблиц и формул. Сразу отмечу, что данный путь потребует некоторых трудозатрат от разработчика, однако, они, безусловно, окупятся удобством и новыми возможностями для ваших пользователей. К тому же эти трудозатраты будут потребуют гораздо меньше усилий по сравнению с созданием закладок для десятков потенциальных вариантов сводной таблицы, а также могут обеспечить, по нашему мнению, более удобный интерфейс для пользователя даже по сравнению с функционалом Personalize Visuals и Field Parameters из облачной версии Power BI.

Давайте рассмотрим, как же использовать DAX для создания динамической сводной таблицы в Power BI Report Server.

Подготовка данных

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

• Дата (Date);

• Страна (Country) — 102 уникальных значения;

• Сегмент бизнеса (Segment) — 15 уникальных значений;

• Продукт (Product) — 10 уникальных значений;

• Производитель (Manufacturer) — 10 уникальных значений;

• Канал продаж (Channel) — 4 уникальных значения;

• Количество проданных единиц (Units Sold) в целых числах;

• Выручка от продаж (Gross Sales) с точностью до 2 знаков после запятой;

• Себестоимость товаров (COGS) с точностью до 2 знаков после запятой.

Датасет состоит из 25 млн. записей без значений NULL, что подходит для релевантной оценки производительности. Также в целях оценки производительности создаваемой сводной таблицы предполагается целесообразным выбрать для неё поля с максимальной кардинальностью.

В связи с этим предположим, что в качестве 4 измерений, в разрезе которых мы хотим дать пользователю анализировать данные в дашборде, используются Country, Segment, Product и Manufacturer, а оставшиеся измерения (например, Channel) будут использоваться в качестве фильтров. В таком случае кардинальность данных, задействованных в расчётах сводной таблицы, будет достигать 153 тыс. значений (102 × 15 × 10 × 10).

Загрузка датасета и создание первых мер

Загрузим датасет в файл Power BI Desktop RS с помощью стандартного диалога с редактором Power Query (разделителем полей является «|») в таблицу Info.

Можно проверить совокупную кардинальность полей Country, Segment, Product и Manufacturer с помощью меры DAX и подтвердить, что она равна максимальной оценке 153 тыс. значений.

_Check_Cardinality = COUNTROWS (
    SUMMARIZE (
        Info,
        Info[Country],
        Info[Manufacturer],
        Info[Product],
        Info[Segment]
    )
)

После загрузки датасета создадим элементарные меры по 3 количественным полям дашборда

_Sales = SUM ( Info[Gross Sales] )

_Quantity = SUM ( Info[Units Sold] )

_Cost = SUM ( Info[COGS] )

Создание таблицы измерений и их значений

Теперь приступаем непосредственно к созданию сводной таблицы a lá Excel. Для демонстрации работы сводной таблицы ограничимся 4 измерениями, в разрезе которых пользователь сможет строить сводную таблицу.

Создадим специальную таблицу Pivot_01, которая будет включать все названия измерений (качественных атрибутов, в нашем случае Country, Segment, Product и Manufacturer) и их значений

Pivot_01 = 
VAR _Country =
    CROSSJOIN ( ROW ( "Type", "Country" ), DISTINCT ( Info[Country] ) )
VAR _Product =
    CROSSJOIN ( ROW ( "Type", "Product" ), DISTINCT ( Info[Product] ) )
VAR _Segment =
    CROSSJOIN ( ROW ( "Type", "Segment" ), DISTINCT ( Info[Segment] ) )
VAR _Manufacturer =
    CROSSJOIN ( ROW ( "Type", "Manufacturer" ), DISTINCT ( Info[Manufacturer] ) )
VAR _Union =
    UNION ( _Country, _Product, _Segment, _Manufacturer )
VAR _Total =
    ADDCOLUMNS ( _union, "Total", "Итого" )
RETURN
    _Total

Таблица Pivot_01 будет содержать все уникальные сочетания типов измерений (в столбце Type) и их всех возможных значений (в столбце Key).

Следует отметить, что данная таблица может быть также создана с помощью SQL или Power Query, что может быть предпочтительно с точки зрения производительности; в данном случае мы использовали DAX в качестве примера, который легко воспроизвести (в частности, код SQL-запроса будет зависеть от источника на стороне конкретного пользователя).

После этого рекомендуется перейти в представление таблицы и переименовать получившийся столбец Country на другое имя, например Key.

Поле Key из данной таблицы будет использоваться в дальнейшем в строках нашей будущей сводной таблицы. С учётом того, что мы будем использовать 4 уровня измерений в наших данных, следует создать ещё 3 таких таблицы. Отметим, что данные таблицы будут полными копиями уже созданной таблицы Pivot_01, поэтому DAX для их создания будет очень простым.

Pivot_02 = Pivot_01

Pivot_03 = Pivot_01

Pivot_04 = Pivot_01

Далее на холсте Power BI Desktop следует разместить 4 визуализации «срез», в каждую из которых необходимо поместить поле Type из таблиц Pivot_01, Pivot_02, Pivot_03, Pivot_04. Другими словами, мы должны получить 4 среза, каждый из которых ссылается на поле Type той или иной таблицы измерений. Этими срезами будет управлять пользователь для выстраивания нужной ему структуры сводной таблицы.

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

• Pivot_01[Total]

• Pivot_01[Key]

• Pivot_02[Key]

• Pivot_03[Key]

• Pivot_04[Key]

Создание вспомогательных мер

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

• Country;

• Product;

• Segment;

• Manufacturer,

то

• на уровне Country мера _Sales должна показывать продажи по каждой стране, т. е. быть отфильтрована по странам;

• на уровне Product мера _Sales должна показывать продажи по каждому продукту внутри каждой страны, т. е. быть отфильтрована сначала по странам, а затем по продуктам;

• на уровне Segment мера _Sales должна показывать продажи по каждому сегменту внутри каждого продукта для каждой страны, т. е. быть отфильтрована сначала по странам, затем по продуктам, после чего по сегментам;

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

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

Вариант 1. Использование CALCULATE + SWITCH

Мы можем использовать комбинацию функций CALCULATE + SWITCH, чтобы в зависимости от выбора пользователя в срезе нужного ему измерения расчёт вёлся по соответствующим ключам этого измерения. Формулы расчёта вспомогательных мер на 4-х уровнях иерархии представлены ниже

_Sales_L1 = 
VAR _val_01 =
    SELECTEDVALUE ( Pivot_01[Type] )
RETURN
    CALCULATE (
        [_Sales],
        SWITCH (
            _val_01,
            "Country", Info[Country],
            "Product", Info[Product],
            "Segment", Info[Segment],
            "Manufacturer", Info[Manufacturer]
        )
            = SELECTEDVALUE ( Pivot_01[Key] )
    )

_Sales_L2 = 
VAR _val_01 =
    SELECTEDVALUE ( Pivot_01[Type] )
VAR _val_02 =
    SELECTEDVALUE ( Pivot_02[Type] )
RETURN
    CALCULATE (
        [_Sales],
        SWITCH (
            _val_01,
            "Country", Info[Country],
            "Product", Info[Product],
            "Segment", Info[Segment],
            "Manufacturer", Info[Manufacturer]
        )
            = SELECTEDVALUE ( Pivot_01[Key] ),
        SWITCH (
            _val_02,
            "Country", Info[Country],
            "Product", Info[Product],
            "Segment", Info[Segment],
            "Manufacturer", Info[Manufacturer]
        )
            = SELECTEDVALUE ( Pivot_02[Key] )
    )

_Sales_L3 = 
VAR _val_01 =
    SELECTEDVALUE ( Pivot_01[Type] )
VAR _val_02 =
    SELECTEDVALUE ( Pivot_02[Type] )
VAR _val_03 =
    SELECTEDVALUE ( Pivot_03[Type] )
RETURN
    CALCULATE (
        [_Sales],
        SWITCH (
            _val_01,
            "Country", Info[Country],
            "Product", Info[Product],
            "Segment", Info[Segment],
            "Manufacturer", Info[Manufacturer]
        )
            = SELECTEDVALUE ( Pivot_01[Key] ),
        SWITCH (
            _val_02,
            "Country", Info[Country],
            "Product", Info[Product],
            "Segment", Info[Segment],
            "Manufacturer", Info[Manufacturer]
        )
            = SELECTEDVALUE ( Pivot_02[Key] ),
        SWITCH (
            _val_03,
            "Country", Info[Country],
            "Product", Info[Product],
            "Segment", Info[Segment],
            "Manufacturer", Info[Manufacturer]
        )
            = SELECTEDVALUE ( Pivot_03[Key] )
    )

_Sales_L4 = 
VAR _val_01 =
    SELECTEDVALUE ( Pivot_01[Type] )
VAR _val_02 =
    SELECTEDVALUE ( Pivot_02[Type] )
VAR _val_03 =
    SELECTEDVALUE ( Pivot_03[Type] )
VAR _val_04 =
    SELECTEDVALUE ( Pivot_04[Type] )
RETURN
    CALCULATE (
        [_Sales],
        SWITCH (
            _val_01,
            "Country", Info[Country],
            "Product", Info[Product],
            "Segment", Info[Segment],
            "Manufacturer", Info[Manufacturer]
        )
            = SELECTEDVALUE ( Pivot_01[Key] ),
        SWITCH (
            _val_02,
            "Country", Info[Country],
            "Product", Info[Product],
            "Segment", Info[Segment],
            "Manufacturer", Info[Manufacturer]
        )
            = SELECTEDVALUE ( Pivot_02[Key] ),
        SWITCH (
            _val_03,
            "Country", Info[Country],
            "Product", Info[Product],
            "Segment", Info[Segment],
            "Manufacturer", Info[Manufacturer]
        )
            = SELECTEDVALUE ( Pivot_03[Key] ),
        SWITCH (
            _val_04,
            "Country", Info[Country],
            "Product", Info[Product],
            "Segment", Info[Segment],
            "Manufacturer", Info[Manufacturer]
        )
            = SELECTEDVALUE ( Pivot_04[Key] )
    )

Вариант 2. Использование CALCULATE + TREATAS

Мы можем использовать альтернативную комбинацию функций CALCULATE + TREATAS, чтобы в зависимости от выбора пользователя в срезе нужного ему измерения формировалась виртуальная связь между значениями таблицы Info и соответствующими выбранному измерению его значениями в таблице Pivot. Формулы расчёта вспомогательных мер на 4-х уровнях иерархии представлены ниже.

_Sales_T1 = 
VAR _val =
    SELECTEDVALUE ( Pivot_01[Type] )
RETURN
    SWITCH (
        TRUE (),
        _val = "Country", CALCULATE ( [_Sales], TREATAS ( VALUES ( Pivot_01[Key] ), Info[Country] ) ),
        _val = "Product", CALCULATE ( [_Sales], TREATAS ( VALUES ( Pivot_01[Key] ), Info[Product] ) ),
        _val = "Segment", CALCULATE ( [_Sales], TREATAS ( VALUES ( Pivot_01[Key] ), Info[Segment] ) ),
        _val = "Manufacturer", CALCULATE ( [_Sales], TREATAS ( VALUES ( Pivot_01[Key] ), Info[Manufacturer] ) )
    )

_Sales_T2 = 
VAR _val =
    SELECTEDVALUE ( Pivot_02[Type] )
RETURN
    SWITCH (
        TRUE (),
        _val = "Country", CALCULATE ( [_Sales_T1], TREATAS ( VALUES ( Pivot_02[Key] ), Info[Country] ) ),
        _val = "Product", CALCULATE ( [_Sales_T1], TREATAS ( VALUES ( Pivot_02[Key] ), Info[Product] ) ),
        _val = "Segment", CALCULATE ( [_Sales_T1], TREATAS ( VALUES ( Pivot_02[Key] ), Info[Segment] ) ),
        _val = "Manufacturer", CALCULATE ( [_Sales_T1], TREATAS ( VALUES ( Pivot_02[Key] ), Info[Manufacturer] )
            )
    )

_Sales_T3 = 
VAR _val =
    SELECTEDVALUE ( Pivot_03[Type] )
RETURN
    SWITCH (
        TRUE (),
        _val = "Country", CALCULATE ( [_Sales_T2], TREATAS ( VALUES ( Pivot_03[Key] ), Info[Country] ) ),
        _val = "Product", CALCULATE ( [_Sales_T2], TREATAS ( VALUES ( Pivot_03[Key] ), Info[Product] ) ),
        _val = "Segment", CALCULATE ( [_Sales_T2], TREATAS ( VALUES ( Pivot_03[Key] ), Info[Segment] ) ),
        _val = "Manufacturer", CALCULATE ( [_Sales_T2], TREATAS ( VALUES ( Pivot_03[Key] ), Info[Manufacturer] )
            )
    )

_Sales_T4 = 
 VAR _val =
    SELECTEDVALUE ( Pivot_04[Type] )
RETURN
    SWITCH (
        TRUE (),
        _val = "Country", CALCULATE ( [_Sales_T3], TREATAS ( VALUES ( Pivot_04[Key] ), Info[Country] ) ),
        _val = "Product", CALCULATE ( [_Sales_T3], TREATAS ( VALUES ( Pivot_04[Key] ), Info[Product] ) ),
        _val = "Segment", CALCULATE ( [_Sales_T3], TREATAS ( VALUES ( Pivot_04[Key] ), Info[Segment] ) ),
        _val = "Manufacturer", CALCULATE ( [_Sales_T3], TREATAS ( VALUES ( Pivot_04[Key] ), Info[Manufacturer] )
            )
    )

Вариант 3. Использование CALCULATE + USERELATIONSHIP

Можно доработать существующую модель данных путём создания неактивных связей между таблицей Info и таблицами Pivot_01, Pivot_02, Pivot_03 и Pivot_04 по полям Country, Product, Segment, Manufacturer (из таблицы Info) и полям Key (из таблиц измерений).

С учётом создания связей теперь можно использовать комбинацию функций CALCULATE + USERELATIONSHIP, с помощью которой можно активировать данные связи при выполнении условий, определённых формулой DAX. Формулы расчёта вспомогательных мер на 4-х уровнях иерархии представлены ниже.

_Sales_K1 = 
VAR _val =
    SELECTEDVALUE ( Pivot_01[Type] )
RETURN
    SWITCH (
        TRUE (),
        _val = "Country", CALCULATE ( [_Sales], USERELATIONSHIP ( Info[Country], Pivot_01[Key] ) ),
        _val = "Product", CALCULATE ( [_Sales], USERELATIONSHIP ( Info[Product], Pivot_01[Key] ) ),
        _val = "Segment", CALCULATE ( [_Sales], USERELATIONSHIP ( Info[Segment], Pivot_01[Key] ) ),
        _val = "Manufacturer", CALCULATE ( [_Sales], USERELATIONSHIP ( Info[Manufacturer], Pivot_01[Key] ) )
    )

_Sales_K2 = 
VAR _val =
    SELECTEDVALUE ( Pivot_02[Type] )
RETURN
    SWITCH (
        TRUE (),
        _val = "Country", CALCULATE ( [_Sales_K1], USERELATIONSHIP ( Info[Country], Pivot_02[Key] ) ),
        _val = "Product", CALCULATE ( [_Sales_K1], USERELATIONSHIP ( Info[Product], Pivot_02[Key] ) ),
        _val = "Segment", CALCULATE ( [_Sales_K1], USERELATIONSHIP ( Info[Segment], Pivot_02[Key] ) ),
        _val = "Manufacturer", CALCULATE ( [_Sales_K1], USERELATIONSHIP ( Info[Manufacturer], Pivot_02[Key] ) )
    )

_Sales_K3 = 
VAR _val =
    SELECTEDVALUE ( Pivot_03[Type] )
RETURN
    SWITCH (
        TRUE (),
        _val = "Country", CALCULATE ( [_Sales_K2], USERELATIONSHIP ( Info[Country], Pivot_03[Key] ) ),
        _val = "Product", CALCULATE ( [_Sales_K2], USERELATIONSHIP ( Info[Product], Pivot_03[Key] ) ),
        _val = "Segment", CALCULATE ( [_Sales_K2], USERELATIONSHIP ( Info[Segment], Pivot_03[Key] ) ),
        _val = "Manufacturer", CALCULATE ( [_Sales_K2], USERELATIONSHIP ( Info[Manufacturer], Pivot_03[Key] ) )
    )

_Sales_K4 = 
VAR _val =
    SELECTEDVALUE ( Pivot_04[Type] )
RETURN
    SWITCH (
        TRUE (),
        _val = "Country", CALCULATE ( [_Sales_K3], USERELATIONSHIP ( Info[Country], Pivot_04[Key] ) ),
        _val = "Product", CALCULATE ( [_Sales_K3], USERELATIONSHIP ( Info[Product], Pivot_04[Key] ) ),
        _val = "Segment", CALCULATE ( [_Sales_K3], USERELATIONSHIP ( Info[Segment], Pivot_04[Key] ) ),
        _val = "Manufacturer", CALCULATE ( [_Sales_K3], USERELATIONSHIP ( Info[Manufacturer], Pivot_04[Key] ) )
    )

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

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

Рассмотрим пока создание общих мер на примере мер, полученных по варианту 1 (с использованием CALCULATE + SWITCH), – общая мера Sales_Pvt будет рассчитываться следующим образом:

_Sales_Pvt = 
SWITCH (
    TRUE (),
    ISINSCOPE ( Pivot_04[Key] ), [_Sales_L4],
    ISINSCOPE ( Pivot_03[Key] ), [_Sales_L3],
    ISINSCOPE ( Pivot_02[Key] ), [_Sales_L2],
    ISINSCOPE ( Pivot_01[Key] ), [_Sales_L1],
    [_Sales]
)

Данную меру можно поместить в значение матрицы, которую мы создали ранее. В результате мера Sales будет рассчитываться на каждом уровне иерархии по-разному с учётом фильтров по другим иерархиям, причём на самом первом уровне, который представлен полем Pivot01[Total], преобразования контекста вычисления не происходит, т. е. в верхней строке мы видим просто общий итог по данной мере.

Далее, аналогично мере Sales, можно создать такие же наборы конструкции для мер _Quantity и _Cost. Следует отметить, что можно существенно снизить трудоёмкость создания таких мер с помощью инструмента Tabular Editor (его бесплатная, но вполне функциональная версия 2.0 доступна по ссылке), см. шаги ниже.

• Установить и открыть Tabular Editor 2.

• Подключиться к локальному файлу, где ведётся разработка.

• В папке Model – Tables найти, например, меры Sales и все производные меры, которые были созданы выше на основе меры Sales, выделить их и щёлкнуть правой кнопкой мыши, чтобы их скопировать (Duplicate Measures).

• С помощью функции пакетного переименования (Batch Rename) можно сначала заменить в названии мер Sales, например, на Quantity, а затем убрать появившиеся при копировании фрагменты « _copy».

• Теперь во вновь созданных мерах Quantity следует заменить внутри текста формул фрагмент «Sales» на фрагмент «_Quantity»; для этого необходимо выделить меры, в которых нужна замена, и перейти на вкладку C#, откуда можно выполнить следующий скрипт:

 var Before = "_Sales"; // Фрагмент для замены
 var After = "_Quantity"; // Замена
 
 foreach (var m in Selected.Measures)
     {
         m.Expression = m.Expression.Replace(Before, After);
     }

• Сохранить выполненные изменения

После того, как мы создали ещё 2 общих меры Quantity_Pvt и Cost_Pvt аналогично созданной ранее Sales_Pvt, их также можно использовать в сводной таблице, поместив в значения матрицы.

Помимо этого, чтобы дать возможность пользователю выбирать, значения каких именно показателей он будет выводить в сводной таблице, можно воспользоваться группами вычислений (Calculation Groups), которые также входят в функционал Tabular Editor. Для создания группы вычислений необходимо:

• В открытом через Tabular Editor локальном файле щёлкнуть правой кнопкой мыши на папке Tables и выбрать пункт Create New — Calculation Group.

• Щёлкнуть правой кнопкой по вновь созданной группе вычисления, чтобы создать новый вычислительный элемент (Calculation Item), в вычислительные элементы мы будем включать те общие меры, которые планируется отдать пользователю на включение / выключение в сводной таблице.

• После создания вычислительного элемента в окне справа на вкладке Expression Editor разместить формулу расчёта вычислительного элемента, например [_Sales_Pvt].

• Сохранить изменения в Tabular Editor.

• Перейти в Power BI Desktop и обновить изменения на стороне самого файла.

• Создать фиктивную меру произвольного содержания, например CalculationGroup = 1, и поместить её в значения матрицы, где содержится сводная таблица.

• Столбец группы вычислений (единственное поле вновь созданной группы вычислений) переместить в столбцы матрицы со сводной таблицы.

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

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

Строки матрицы

• Pivot_01[Total]

• Pivot_01[Key]

• Pivot_02[Key]

• Pivot_03[Key]

• Pivot_04[Key]

 Столбцы матрицы

• Столбец группы вычислений

 Значения матрицы

• [_Calculation Group]

 В результате, если всё настроено корректно, мы получаем следующий результат:

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

Также можно обратить внимание на удобный и понятный интерфейс (более крупный и чёткий по сравнению с Personalize Visuals), из которого пользователь чётко понимает структуру собираемой им сводной таблицы (в отличие от Field Parameters).

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

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

Безусловно, реализация этого подхода потребует от разработчика определенных усилий в виде создания промежуточных таблиц и мер со сложной иерархией. Вместе с тем такой механизм — это необходимое компромиссное решение, поскольку такой подход значительно упрощает разработку DAX-мер. Кроме того, структурированная иерархия делает расчёты более прозрачными и удобочитаемыми, что особенно важно при дальнейшей поддержке и доработке отчётов.

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

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

Анализ производительности

Для анализа производительности создадим ещё две общие меры аналогично Sales_Pvt для вариантов реализации вспомогательных мер через CALCULATE + TREATAS и CALCULATE + USERELATIONSHIP.

_Sales_Total = 
 SWITCH (
    TRUE (),
    ISINSCOPE ( Pivot_04[Key] ), [_Sales_T4],
    ISINSCOPE ( Pivot_03[Key] ), [_Sales_T3],
    ISINSCOPE ( Pivot_02[Key] ), [_Sales_T2],
    ISINSCOPE ( Pivot_01[Key] ), [_Sales_T1],
    [_Sales]
)

_Sales_Fin = 
SWITCH (
    TRUE (),
    ISINSCOPE ( Pivot_04[Key] ), [_Sales_K4],
    ISINSCOPE ( Pivot_03[Key] ), [_Sales_K3],
    ISINSCOPE ( Pivot_02[Key] ), [_Sales_K2],
    ISINSCOPE ( Pivot_01[Key] ), [_Sales_K1],
    [_Sales]
)

Для сравнения производительности вариантов создадим три сводные таблицы (матрицы) с одинаковой структурой и привязкой к срезам, которые переключают измерения сводных таблиц. Сводные таблицы будут содержать меры Sales_Pvt, Sales_Total, Sales_Fin. Меры будут вставлены в значения матриц без использования групп вычислений.

В 1-й сводной таблице будет содержаться мера Sales_Pvt (на основе CALCULATE + SWITCH), во 2-й сводной таблице – Sales_Total (на основе CALCULATE + TREATAS), в 3-й сводной таблице – Sales_Fin (на основе CALCULATE + USERELATIONSHIP).

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

Для оценки производительности использовался DAX Studio, с помощью которого запросы, генерируемые при обновлении сводных таблиц, обрабатывались с помощью инструмента Run Benchmark. В таблице сведены усреднённые результаты после 10 прогонов каждого запроса (указано время оценки по Cold Cache, т. е. по максимальному времени с учётом очистки кэша перед каждым выполнением запроса).

Как показывают результаты оценки, по мере развёртывания детализации лучшие результаты демонстрирует вариант CALCULATE + SWITCH. Очевидно, что при большем погружении в структуру сводной таблицы пользователь столкнётся с замедлением работы инструмента, однако время отработки движка даже при самой глубокой детализации остаётся вполне приемлемым.

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

Вывод

В результате мы успешно создали динамическую сводную таблицу в Power BI Report Server, полностью соответствующую пожеланиям пользователя, — несмотря на ограничения on premise версии, нам удалось реализовать гибкий и удобный инструмент, сравнимый по функциональности с Excel.

Таким образом, даже в Power BI Report Server, где нет некоторых функций облачного Power BI, можно создавать мощные и гибкие дашборды. Ключ к успеху — грамотное моделирование данных и эффективное использование DAX.

Хотя этот подход требует дополнительных усилий на этапе проектирования, он позволяет:

• компенсировать отсутствие некоторых облачных функций (например, Personalize Visuals, Field Parameters);

• упрощать сложные расчёты за счёт продуманной структуры данных;

• повышать удобство для пользователей.

Поэтому ограничения Report Server могут служить стимулом глубже изучить DAX и методы моделирования, чтобы улучшать архитектуру решения и создавать интерактивные и удобные отчёты.

Ссылка на материалы к статье: GitHub