Контекст позволяет выполнять динамический анализ, при котором результаты формулы могут меняться в зависимости от выделенной строки или ячейки и любых взаимосвязанных данных. Понимание и эффективное использование контекста очень важно для построения высокопроизводительных формул и динамического анализа и для устранения неполадок в формулах.
В этом разделе определены различные типы контекста: контекст строки, контекст запроса и контекст фильтра. В нем объясняется порядок оценки контекста для формул в вычисляемых столбцах и сводных таблицах.
В заключительной части статьи предоставляются ссылки на подробные примеры, которые показывают, каким образом результаты формул меняются в соответствии с контекстом.
Основные сведения о контексте
На формулы в Power Pivot могут влиять фильтры, примененные в сводной таблице, связи между таблицами и фильтры, имеющиеся в формулах. Контекст позволяет выполнять динамический анализ. При построении формул и устранении неполадок в них важно понимать назначение контекста.
Существуют различные типы контекста: контекст строки, контекст запроса и контекст фильтра.
Контекст строки фактически соответствует понятию текущей строки. Если создан вычисляемый столбец, то контекстом строки служат значения в каждой отдельной строке и значения в столбцах, связанных с текущей строкой. Существуют также некоторые функции (РАНЕЕ и РАННЯЯ), которые получают значение из текущей строки, а затем используют это значение при выполнении операции над всей таблицей.
Контекст запроса относится к подмножеству данных, которое неявным образом создается для каждой ячейки сводной таблицы в зависимости от заголовков строки и столбца.
Контекст фильтра — это набор значений, разрешенных в каждом столбце в зависимости от ограничений фильтра, которые применяются к строке или которые определены критериями фильтра в формуле.
Контекст строки
Если формула создана в вычисляемом столбце, то контекст строки для этой формулы включает в себя значения всех столбцов в текущей строке. Если таблица связана с другой таблицей, содержимое также включает в себя все значения из другой таблицы, связанные с текущей строкой.
Например, предположим, что вы создали вычисляемый столбец =[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 автоматически формирует поток значений между двумя циклами, чтобы можно было создавать сложные агрегаты.
Контекст запроса
Контекст запроса означает подмножество данных, которое неявным образом возвращается для формулы. При удалении меры или другого поля значения в ячейку сводной таблицы подсистема Power Pivot проверяет заголовки строк и столбцов, срезы и фильтры отчетов для определения контекста. Затем Power Pivot выполняет необходимые вычисления для заполнения каждой ячейки в сводной таблице. Полученный набор данных является контекстом запроса для каждой ячейки.
Поскольку контекст может меняться в зависимости от места размещения формулы, результаты формулы также меняются в зависимости от того, где используется формула: в сводной таблице с большим количеством групп и фильтров или в вычисляемом столбце без фильтров и с минимальным контекстом.
Например, предположим, что вы создали простую формулу, которая суммирует значения в столбце Profit таблицы Sales :
=СУММ('Продажи'[Прибыль])
Если эта формула используется в вычисляемом столбце таблицы Sales, результаты формулы будут одинаковыми для всей таблицы, так как контекстом запроса для формулы всегда является весь набор данных в таблице Sales. В результаты будут входить значения прибыли для всех регионов, всех товаров, всех лет и т. д.
Однако обычно не требуется получать одни и те же результаты сотни раз подряд, а вместо этого необходимо узнать прибыль за определенный год в определенной стране или регионе по определенному продукту или некоторое сочетание этих значений, а затем получить общий итог.
В сводной таблице можно легко изменить контекст путем добавления или удаления заголовков столбцов и строк, а также путем добавления и удаления срезов. Можно создать формулу, подобную приведенной выше, в меру, а затем поместить ее в сводную таблицу. При добавлении заголовков столбцов или строк в сводную таблицу изменяется контекст запроса, в котором вычисляется мера. Операции создания срезов и фильтрации также влияют на контекст. Поэтому та же формула, которая используется в сводной таблице, вычисляется в отдельном контексте запроса для каждой ячейки.
Контекст фильтра
Контекст фильтра добавляется при задании ограничений фильтра для набора значений, допустимых в столбце или таблице, с помощью аргументов в формуле. Контекст фильтра применяется поверх других контекстов, например контекста строки или контекста запроса.
Например, сводная таблица вычисляет значения для каждой ячейки в зависимости от заголовков столбцов и строк, как описано в предыдущем разделе о контексте запроса. Однако в мерах или вычисляемых столбцах, добавляемых в сводную таблицу, можно указать выражения фильтра для управления значениями, используемыми формулой. Можно также выборочно очищать фильтры для определенных столбцов.
Дополнительные сведения о создании фильтров в формулах см. в разделе Функции фильтра.
Пример очистки фильтров для создания общих итогов см. в разделе Функция ALL.
Примеры выборочного очистки и применения фильтров в формулах см. в статье Функция ALLEXCEPT.
Поэтому необходимо просмотреть определение мер или формул, используемых в сводной таблице, чтобы вы знали о контексте фильтра при интерпретации результатов формул.
Определение контекста в формулах
При создании формулы Power Pivot для Excel вначале проверяет общий синтаксис, а затем сверяет предоставленные имена столбцов и таблиц с возможными столбцами и таблицами в текущем контексте. Если Power Pivot не может найти столбцы и таблицы, указанные в формуле, возвращается ошибка.
Контекст определяется, как описано в предыдущих разделах, с помощью доступных таблиц в книге, любых связей между таблицами и применяемых фильтров.
Например, если данные импортированы в новую таблицу, но фильтры еще не применены, то в текущий контекст будет входить весь набор столбцов в таблице. Если есть несколько связанных таблиц и выполняется работа со сводной таблицей, фильтрация которой была выполнена с помощью добавления заголовков столбцов и использования срезов, контекст включает связанные таблицы и все фильтры для данных.
Контекст — это мощная концепция, которая может также усложнять поиск и исправление ошибок в формулах. Рекомендуется начинать с простых формул и связей, чтобы увидеть, как работает контекст, а затем перейти к экспериментам с простыми формулами в сводных таблицах. В следующем разделе также приведены несколько примеров использования формул в различных типах контекстов для возвращения динамических результатов.
Примеры контекста в формулах
-
Функция RELATED расширяет контекст текущей строки для включения значений в связанном столбце. Это позволяет выполнять уточняющие запросы. Пример в этом разделе показывает взаимодействие между контекстом строки и фильтрации.
-
Функция FILTER позволяет задать строки, которые должны быть включены в текущий контекст. Примеры в этом разделе также показывают порядок внедрения фильтров в другие функции, которые выполняют агрегатную обработку.
-
Функция ALL задает контекст в формуле. Ее можно использовать для переопределения фильтров, которые применяются к результату контекста запроса.
-
Функция ALLEXCEPT позволяет удалить все фильтры, за исключением указанного фильтра. Оба раздела содержат примеры, демонстрирующие построение формул и дающие представление о сложных контекстах.
-
Функции EARLIER и EARLIEST позволяют организовать цикл по таблицам с выполнением вычислений, ссылаясь на значения во внутреннем цикле. Пользователи, знакомые с понятием рекурсии, внутренними и внешними циклами, по достоинству оценят возможности, которые предоставляют функции EARLIER и EARLIEST. Пользователям, незнакомым с этими основными понятиями, рекомендуется пошагово выполнить примеры, чтобы понять, как внутренний и внешний контексты используются при вычислениях.
Ссылочная целостность
В этом разделе рассказывается о более сложных понятиях, относящихся к отсутствующим значениям в таблицах Power Pivot, соединенных с помощью связей. Раздел может быть также полезен для интерпретации результатов при работе с книгами с несколькими таблицами и сложными формулами.
Пользователям, незнакомым с основными понятиями реляционных баз данных, рекомендуется предварительно прочесть вводный раздел Общие сведения о связях.
Целостность данных и связи Power Pivot
Power Pivot не требует строгого соблюдения целостности данных между двумя таблицами для определения допустимой связи. Вместо этого в части с одним элементом для каждой связи типа "один ко многим" создается пустая строка, которая используется для обработки всех строк из связанной таблицы, для которых не найдено соответствие. Такая строка фактически работает как внешнее соединение в SQL.
При группировании данных в части связи с одним элементом в сводных таблицах все данные в части с многими элементами, для которых не найдено соответствие, группируются и включаются в итоги с пустым заголовком строки. Пустой заголовок приблизительно равнозначен «неизвестному элементу».
Основные сведения о неизвестном элементе
Понятие неизвестного элемента может быть знакомо по опыту работы с системами многомерных баз данных, такими как службы SQL Server Analysis Services. Если понятие неизвестно, то в следующем примере объясняется, что такое неизвестный элемент и как он влияет на вычисления.
Предположим, вы создаете вычисление, которое суммирует ежемесячные продажи для каждого магазина, но в столбце таблицы Sales отсутствует значение для имени магазина. Учитывая, что таблицы для Store и Sales связаны с именем магазина, что вы ожидаете в формуле? Как в сводной таблице должны группироваться или отображаться данные о продажах, которые не связаны с существующим магазином?
Это общая проблема хранилищ данных, в которых большие таблицы фактических данных должны быть логически связаны с таблицами измерений, содержащими сведения о магазинах, регионах и прочих атрибутах, используемых для категоризации и вычисления фактических данных. Для решения данной проблемы все новые факты, не связанные с существующей сущностью, временно присваиваются неизвестному элементу. Поэтому несвязанные факты группируются в сводной таблице под пустым заголовком.
Обработка пустых значений и пустых строк
Пустые значения отличаются от пустых строк, добавляемых для обработки неизвестного элемента. Пустое значение — специальное значение, которое представляет значения NULL, пустые строки и другие отсутствующие значения. Дополнительные сведения о пустом значении, а также других типах данных DAX см. в разделе Типы данных в моделях данных.
