Контекст в формулах DAX

Статья
09/10/2013

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

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

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

Введение в контекст

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

Существуют различные типы контекста: контекст строки, контекст запроса и контекст фильтра.

Контекст строки фактически соответствует понятию текущей строки. Если создан вычисляемый столбец, то контекстом строки служат значения в каждой отдельной строке и значения в столбцах, связанных с текущей строкой. Предусмотрены также некоторые функции (EARLIER и EARLIEST), возвращающие значение текущей строки, а затем использующие это значение для выполнения операции над всей таблицей.

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

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

В этом разделе более детально описываются различные типы контекста: Контекст в формулах DAX.

В начало

Контекст строки

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

Например, предположим, создается вычисляемый столбец =[Freight] + [Tax], который суммирует два столбца из одной таблицы. Эта формула аналогична формулам в таблице Excel, которые автоматически ссылаются на значения из той же строки. Обратите внимание, что таблицы отличаются от диапазонов: нельзя сослаться на значение из предыдущей строки в представлении диапазона, а также нельзя сослаться на любое отдельное произвольное значение в таблице или ячейке. Необходимо всегда работать с таблицами и столбцами.

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

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

= [Freight] + RELATED('Region'[TaxRate])

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

Контекст нескольких строк

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

Например, предположим, книга содержит таблицу Products и таблицу Sales. Может потребоваться просмотреть всю таблицу продаж, содержащую транзакции с множеством продуктов, и найти самый крупный заказ по каждому продукту в любой транзакции.

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

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

=MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])

Подробное пошаговое руководство по этой формуле см. в разделе Функция EARLIER.

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

Контекст запроса

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

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

Например, предположим, что была создана простая формула, которая выполняет суммирование значений в столбце Profit таблицы Sales: =SUM('Sales'[Profit]). Если эта формула используется в вычисляемом столбце таблицы Sales, результаты формулы будут одинаковыми для всей таблицы, так как контекстом запроса для формулы всегда является весь набор данных в таблице Sales. В результаты будут входить значения прибыли для всех регионов, всех товаров, всех лет и т. д.

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

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

Контекст фильтра

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

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

Дополнительные сведения о создании фильтров в формулах см. в разделе FILTER, функция.

Пример очистки фильтров для получения итоговых сумм см. в разделе Функция ALL.

Пример выборочной очистки и применения фильтров в формулах см. в разделе Функция ALLEXCEPT.

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

Определение контекста в формулах

При создании формулы PowerPivot для Excel вначале проверяет общий синтаксис, а затем сверяет предоставляемые имена столбцов и таблиц с возможными столбцами и таблицами в текущем контексте. Если PowerPivot не удается найти столбцы и таблицы, указанные в формуле, возвращается ошибка.

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

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

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

Примеры контекста в формулах

Функция RELATED расширяет контекст текущей строки для включения значений в связанном столбце. Это позволяет выполнять уточняющие запросы. Пример в этом разделе показывает взаимодействие между контекстом строки и фильтрации.
Функция FILTER позволяет задать строки, которые должны быть включены в текущий контекст. Примеры в этом разделе также показывают порядок внедрения фильтров в другие функции, которые выполняют агрегатную обработку.
Функция ALL задает контекст в формуле. Ее можно использовать для переопределения фильтров, которые применяются к результату контекста запроса.
Функция ALLEXCEPT позволяет удалить все фильтры, за исключением указанного фильтра. Оба раздела содержат примеры, демонстрирующие построение формул и дающие представление о сложных контекстах.
Функции EARLIER и EARLIEST позволяют организовать цикл по таблицам с выполнением вычислений, ссылаясь на значения во внутреннем цикле. Пользователи, знакомые с понятием рекурсии, внутренними и внешними циклами, по достоинству оценят возможности, которые предоставляют функции EARLIER и EARLIEST. Пользователи, незнакомые с этими основными понятиями, могут пошагово выполнить примеры, чтобы понять, как при вычислении используются внутренний и внешний контексты.

Ссылочная целостность

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

Пользователям, незнакомым с основными понятиями реляционных баз данных, рекомендуется предварительно прочесть вводный раздел Общие сведения о связях.

Ссылочная целостность и связи сводной таблицы

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

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

Основные сведения о неизвестном элементе

Понятие неизвестного элемента может быть знакомо по опыту работы с системами многомерных баз данных, такими как службы SQL Server Analysis Services. Если понятие неизвестно, то в следующем примере объясняется, что такое неизвестный элемент и как он влияет на вычисления.

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

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

Обработка пустых значений ипустых строк

Пустые значения отличаются от пустых строк, добавляемых для обработки неизвестного элемента. Пустое значение — специальное значение, которое представляет значения NULL, пустые строки и другие отсутствующие значения. Дополнительные сведения о пустом значении, а также других типах данных DAX, см. в разделе Типы данных, поддерживаемые в книгах PowerPivot.