Объяснение происхождения индексов в DAX

Одна из важнейших концепций в DAX — это происхождение. Речь идёт об информации о том, откуда что-либо происходит. Давайте посмотрим, что это такое и как мы можем этим манипулировать.

Делиться

Введение

В DAX происхождение индексов — важная концепция, и крайне важно понимать, как с ним работать и как им манипулировать.

Как и в предыдущих статьях, я буду использовать запросы DAX для объяснения этой концепции и её последствий.

Начну с простого запроса, чтобы получить количество заказов на продукцию бренда «Adventure Works»:

 EVALUATE       CALCULATETABLE(              SUMMARIZECOLUMNS('Date'[Year]                                         ,'Date'[MonthShortName]                                         ,'Date'[MonthKey]                                         ,'Product'[ProductCategoryName]                                         ,"Order Count", [Online Order Count]                                         )                                  ,'Product'[BrandName] = "Adventure Works"                                  )              ORDER BY 'Date'[MonthKey]                                         ,'Product'[ProductCategoryName]

Это фрагмент результата запроса:

Этот запрос возвращает 180 строк. Запомните это, так как это будет важно позже.

Далее я представлю фильтр для конкретного месяца и покажу роль этой генеалогической линии.

Установить родословную

Я добавлю фильтр на апрель 2026 года:

 DEFINE     VAR YearMonthFilter = 202604 EVALUATE     CALCULATETABLE(         SUMMARIZECOLUMNS('Date'[Year]                         ,'Date'[MonthShortName]                         ,'Date'[MonthKey]                         ,'Product'[ProductCategoryName]                         ,"Order Count", [Online Order Count]                         )                     ,'Product'[BrandName] = "Adventure Works"                     ,'Date'[MonthKey] = YearMonthFilter                     )         ORDER BY 'Date'[MonthKey]                         ,'Product'[ProductCategoryName]

В этом случае я определяю переменную и устанавливаю для нее значение 202604.

Далее я добавляю его в качестве фильтра к функции CALCULATETABLE().

Пока ничего особенного.

Вот результат:

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

Но мы можем задать родословную, используя функцию TREATAS():

 DEFINE     VAR YearMonthFilter = TREATAS({ 202604 }, 'Date'[MonthKey]) EVALUATE     CALCULATETABLE(         SUMMARIZECOLUMNS('Date'[Year]                         ,'Date'[MonthShortName]                         ,'Date'[MonthKey]                         ,'Product'[ProductCategoryName]                         ,"Order Count", [Online Order Count]                         )                     ,'Product'[BrandName] = "Adventure Works"                     ,YearMonthFilter                     )         ORDER BY 'Date'[MonthKey]                         ,'Product'[ProductCategoryName]

Как видите, введение функции TREATAS() позволяет передавать переменную в качестве фильтра. Функция CALCULATETABLE() использует данные о происхождении, заданные функцией TREATAS(), в качестве фильтра по столбцу 'Date'[MonthKey].

Результат не меняется, но запрос становится проще, поскольку мне не нужно передавать условие как «столбец равен значению фильтра».

Фактически, Power BI постоянно использует именно такой формат при передаче фильтров, заданных в отчете, в семантическую модель.

Но здесь все по-другому:

Он определяет переменные, задает происхождение данных и добавляет все фильтры непосредственно в функцию SUMMARIZECOLUMNS():

 DEFINE     VAR YearMonthFilter = TREATAS({ 202604 }, 'Date'[MonthKey])         VAR SelectedBrand = TREATAS( { "Adventure Works" }, 'Product'[BrandName]) EVALUATE         SUMMARIZECOLUMNS('Date'[Year]                         ,'Date'[MonthShortName]                         ,'Date'[MonthKey]                         ,'Product'[ProductCategoryName]                         ,YearMonthFilter                         ,SelectedBrand                         ,"Order Count", [Online Order Count]                         )         ORDER BY 'Date'[MonthKey]                         ,'Product'[ProductCategoryName]

Очистка родословной

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

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

Например, взгляните на следующий код, где я использую функцию VALUE() для удаления связи с предыдущим выражением:

 DEFINE     VAR YearMonthFilter = TREATAS({ 202604 }, 'Date'[MonthKey])         VAR YearMonthFilter_cleared = VALUE(YearMonthFilter) EVALUATE     CALCULATETABLE(         SUMMARIZECOLUMNS('Date'[Year]                         ,'Date'[MonthShortName]                         ,'Date'[MonthKey]                         ,'Product'[ProductCategoryName]                         ,"Order Count", [Online Order Count]                         )                     ,'Product'[BrandName] = "Adventure Works"                     ,YearMonthFilter_cleared                     )         ORDER BY 'Date'[MonthKey]                         ,'Product'[ProductCategoryName]

Вот ошибка, выданная Power BI:

Двигатель не может работать с фильтром в линии 71, поскольку он больше не имеет соответствующей проводки.

В таком виде это будет работать:

 DEFINE     VAR YearMonthFilter = TREATAS({ 202604 }, 'Date'[MonthKey])         VAR YearMonthFilter_cleared = VALUE(YearMonthFilter) EVALUATE     CALCULATETABLE(         SUMMARIZECOLUMNS('Date'[Year]                         ,'Date'[MonthShortName]                         ,'Date'[MonthKey]                         ,'Product'[ProductCategoryName]                         ,"Order Count", [Online Order Count]                         )                     ,'Product'[BrandName] = "Adventure Works"                     ,'Date'[MonthKey] = YearMonthFilter_cleared                     )         ORDER BY 'Date'[MonthKey]                         ,'Product'[ProductCategoryName]

Как видите, запрос возвращает тот же результат, что и раньше:

Обратите внимание на изменение аргумента фильтра в строке 91.

Однако существует более простой способ прояснить родословную при работе с мерами.

Рассмотрим следующий запрос с показателем [Количество заказов за весь год], который вычисляет количество заказов за весь год:

 DEFINE     MEASURE 'All Measures'[Order Count full year] =             VAR SelYear = TREATAS({ SELECTEDVALUE('Date'[Year]) }, 'Date'[Year])                     RETURN             CALCULATE([Online Order Count]                         ,REMOVEFILTERS('Date')                         ,SelYear                         ) EVALUATE     CALCULATETABLE(         SUMMARIZECOLUMNS('Date'[Year]                         ,'Date'[MonthShortName]                         ,'Date'[MonthKey]                         ,'Product'[ProductCategoryName]                         ,"Order Count", [Online Order Count]                         ,"Order Count full year", [Order Count full year]                         )                     ,'Product'[BrandName] = "Adventure Works"                     )         ORDER BY 'Date'[MonthKey]                         ,'Product'[ProductCategoryName]

Это выдержка из полученного результата:

Теперь я добавляю скалярное значение к переменной:

 DEFINE MEASURE 'All Measures'[Order Count full year] = VAR SelYear = TREATAS({ SELECTEDVALUE('Date'[Year]) }, 'Date'[Year]) VAR SelYear_Plus1 = SelYear + 0 RETURN CALCULATE([Online Order Count] ,REMOVEFILTERS('Date') ,SelYear_Plus1 ) EVALUATE CALCULATETABLE( SUMMARIZECOLUMNS('Date'[Year] ,'Date'[MonthShortName] ,'Date'[MonthKey] ,'Product'[ProductCategoryName] ,"Order Count", [Online Order Count] ,"Order Count full year", [Order Count full year] ) ,'Product'[BrandName] = "Adventure Works" ) ORDER BY 'Date'[MonthKey] ,'Product'[ProductCategoryName]

Эта операция удаляет родословную, и данная мера больше не работает:

Я всё ещё могу использовать фильтр равенства, чтобы получить предыдущий результат:

 DEFINE       MEASURE 'All Measures'[Order Count full year] =                    VAR SelYear = TREATAS({ SELECTEDVALUE('Date'[Year]) }, 'Date'[Year])                    VAR SelYear_Plus1 = SelYear + 0             RETURN                    CALCULATE([Online Order Count]                                        ,REMOVEFILTERS('Date')                                        ,'Date'[Year] = SelYear_Plus1                                        ) EVALUATE       CALCULATETABLE(             SUMMARIZECOLUMNS('Date'[Year]                                        ,'Date'[MonthShortName]                                        ,'Date'[MonthKey]                                        ,'Product'[ProductCategoryName]                                        ,"Order Count", [Online Order Count]                                        ,"Order Count full year", [Order Count full year]                                        )                                  ,'Product'[BrandName] = "Adventure Works"                                  )             ORDER BY 'Date'[MonthKey]                                        ,'Product'[ProductCategoryName]

Теперь я получаю тот же результат, что и раньше:

А теперь давайте используем несколько значений в качестве фильтра.

Например, два месяца:

 DEFINE     VAR YearMonthFilter = TREATAS({ 202604, 202605 }, 'Date'[MonthKey]) EVALUATE     CALCULATETABLE(         SUMMARIZECOLUMNS('Date'[Year]                         ,'Date'[MonthShortName]                         ,'Date'[MonthKey]                         ,'Product'[ProductCategoryName]                         ,"Order Count", [Online Order Count]                         )                     ,'Product'[BrandName] = "Adventure Works"                     ,YearMonthFilter                     )         ORDER BY 'Date'[MonthKey]                         ,'Product'[ProductCategoryName]

Вот результат этого запроса:

Один из способов удалить информацию о происхождении переменной с несколькими значениями — использовать функцию SUMMARIZECOLUMNS():

 DEFINE     VAR YearMonthFilter = TREATAS({ 202604, 202605 }, 'Date'[MonthKey])         VAR YearMonthFilter_cleared = SUMMARIZECOLUMNS(YearMonthFilter) EVALUATE     CALCULATETABLE(         SUMMARIZECOLUMNS('Date'[Year]                         ,'Date'[MonthShortName]                         ,'Date'[MonthKey]                         ,'Product'[ProductCategoryName]                         ,"Order Count", [Online Order Count]                         )                     ,'Product'[BrandName] = "Adventure Works"                     ,YearMonthFilter_cleared                     )         ORDER BY 'Date'[MonthKey]                         ,'Product'[ProductCategoryName]

К сожалению, этот метод полностью отключает фильтр, и все месяцы возвращаются в 180 строках (столько же строк, сколько и в исходном запросе):

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

Но если попытаться использовать переменную “YearMonthFilter_cleared” с оператором IN, она перестает работать:

В этом контексте я попробовал другие функции, такие как DISTINCT() и VALUES(). DISTINCT() не дала никакого эффекта, а вот VALUES() — дала.

Например, этот запрос не работает:

 DEFINE     VAR YearMonthFilter = TREATAS({ 202604, 202605 }, 'Date'[MonthKey])         VAR YearMonthFilter_cleared = VALUES(YearMonthFilter) EVALUATE     CALCULATETABLE(         SUMMARIZECOLUMNS('Date'[Year]                         ,'Date'[MonthShortName]                         ,'Date'[MonthKey]                         ,'Product'[ProductCategoryName]                         ,"Order Count", [Online Order Count]                         )                     ,'Product'[BrandName] = "Adventure Works"                     ,YearMonthFilter_cleared                     )         ORDER BY 'Date'[MonthKey]                         ,'Product'[ProductCategoryName]

Вот сообщение об ошибке:

Это работает при использовании оператора IN, который указывает на то, что при использовании функции VALUES() происходит очистка родословной:

 DEFINE     VAR YearMonthFilter = TREATAS({ 202604, 202605 }, 'Date'[MonthKey])         VAR YearMonthFilter_cleared = VALUES(YearMonthFilter) EVALUATE     CALCULATETABLE(         SUMMARIZECOLUMNS('Date'[Year]                         ,'Date'[MonthShortName]                         ,'Date'[MonthKey]                         ,'Product'[ProductCategoryName]                         ,"Order Count", [Online Order Count]                         )                     ,'Product'[BrandName] = "Adventure Works"                     ,'Date'[MonthKey] IN YearMonthFilter                     )         ORDER BY 'Date'[MonthKey]                         ,'Product'[ProductCategoryName]

Вот результат запроса:

В документации к функции VALUES() указано, что для её работы требуется ссылка на таблицу или столбец.

Однако такое поведение показывает, что оно зависит от того, как переменная используется в запросе.

Поскольку для переменной задана родословная, функция VALUES() принимает её как ссылку на столбец.

Манипулировать родословной

Далее изменим способ применения фильтра, изменив структуру родословной.

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

Например, у меня есть 68 заказов от клиентов из Германии за апрель 2026 года. Я хочу посмотреть, сколько заказов было выполнено магазинами в этой стране, если таковые имеются.

Примерно так:

Я могу сделать это, работая с переменной:

 DEFINE     MEASURE 'All Measures'[Orders served from Country] =             VAR SelCountry = SELECTEDVALUE('Customer'[RegionCountryName])                         RETURN                 CALCULATE([Online Order Count]                             ,REMOVEFILTERS(Customer[RegionCountryName])                             ,'Store'[RegionCountryName] = SelCountry                             ) EVALUATE     CALCULATETABLE(         SUMMARIZECOLUMNS('Date'[Year]                         ,'Date'[Month]                         ,'Date'[MonthKey]                         ,'Customer'[RegionCountryName]                         ,"Order Count", [Online Order Count]                         ,"Order Count Country Check", [Order Count Country Check]                         )                     ,'Product'[BrandName] = "Adventure Works"                     )             ORDER BY 'Date'[Year]                         ,'Date'[Month]                         ,'Customer'[RegionCountryName]

В этом подходе я сохраняю текущую страну в переменной. Затем я удаляю фильтр из Customer[RegionCountryName] и заменяю его фильтром для 'Store'[RegionCountryName].

Вот результат:

Или я могу сделать это таким образом, используя функцию TREATAS():

 DEFINE     MEASURE 'All Measures'[Orders served from Country] =                 CALCULATE([Online Order Count]                             ,REMOVEFILTERS(Customer[RegionCountryName])                             ,TREATAS(VALUES('Customer'[RegionCountryName])                                         ,'Store'[RegionCountryName])                             ) EVALUATE     CALCULATETABLE(         SUMMARIZECOLUMNS('Date'[Year]                         ,'Date'[Month]                         ,'Date'[MonthKey]                         ,'Customer'[RegionCountryName]                         ,"Order Count", [Online Order Count]                         ,"Orders served from Country", [Orders served from Country]                         )                     ,'Product'[BrandName] = "Adventure Works"                     )             ORDER BY 'Date'[Year]                         ,'Date'[Month]                         ,'Customer'[RegionCountryName]

В этом подходе я снова удаляю фильтр из Customer[RegionCountryName]. Но затем я использую TREATAS() для переключения цепочки фильтров для текущей страны на 'Store'[RegionCountryName].

При таком подходе мне не нужна переменная; я могу напрямую отфильтровать таблицу магазинов по текущей стране.

Этот код намного короче, но может быть сложнее для понимания читателям, которые не знакомы с принципом работы функции TREATAS().

Работа с таблицами

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

Я могу начать писать код по этой теме, но SQLBI уже это сделал, и вы можете прочитать их статью, в которой все очень хорошо объяснено:

Вы можете найти это здесь:

https://www.sqlbi.com/articles/understanding-data-lineage-in-dax

Заключение

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

Power BI постоянно генерирует код с использованием функции TREATAS() при применении фильтров отчета.

Иногда это может привести к упрощению кода DAX, если вы знаете, как эффективно с ним работать.

Это может стать критически важным при создании таблицы с помощью DAX на основе существующих таблиц. Таблица сохранит родословную. Это может привести к проблемам при попытке добавить связи к исходным таблицам. Без очистки родословной я столкнусь с ошибкой из-за циклической зависимости.

Я призываю вас начать экспериментировать с представленными здесь концепциями и попытаться оптимизировать свой код DAX.

Хотя слово «оптимизировать» здесь неуместно, поскольку я не заметил никаких улучшений производительности при использовании показанных вариантов.

Однако наличие более короткого и легко читаемого кода DAX само по себе может быть оптимизацией.

Получайте удовольствие от работы с ним.

Ссылки

Статья SQLBI о работе с происхождением данных:

Как и в моих предыдущих статьях, я использую пример набора данных Contoso. Вы можете бесплатно скачать набор данных ContosoRetailDW с сайта Microsoft здесь.

Данные Contoso можно свободно использовать в соответствии с лицензией MIT, как описано в этом документе. Я изменил набор данных, чтобы привести данные к современным датам.

Сальваторе Кальяри Посмотреть все магазины Сальваторе Кальяри

Источник: towardsdatascience.com