В этом разделе описывается, как создавать фильтры в формулах выражений анализа данных (DAX). В формулах можно создавать фильтры, чтобы ограничить значения из исходных данных, используемые в вычислениях. Для этого в качестве входных данных для формулы укажите таблицу, а затем определите выражение фильтра. Введенное выражение фильтра используется для запроса данных и возврата только части исходных данных. Фильтр применяется динамически при каждом обновлении результатов формулы в зависимости от текущего контекста данных.
В этой статье
Создание фильтра для таблицы, используемой в формуле
В формулах, принимающих таблицу в качестве входных данных, можно применять фильтры. Вместо ввода имени таблицы можно использовать функцию ФИЛЬТР, чтобы определить подмножество строк из указанной таблицы. Затем это подмножество передается в другую функцию для таких операций, как пользовательские агрегаты.
Например, предположим, что у вас есть таблица данных, содержащая информацию о заказах торговых посредников, и вы хотите вычислить, сколько было продано каждому из торговых посредников. Однако следует показывать объем продаж только для тех торговых посредников, которые продали несколько единиц более ценных продуктов. В следующей формуле, составленной на основе образца книги DAX, приведен один из примеров создания этого вычисления с помощью фильтра:
=СУММХ(
ФИЛЬТР ('ResellerSales_USD'; 'ResellerSales_USD'[количество] > 5 &&
'ResellerSales_USD'[ProductStandardCost_USD] > 100),
'ResellerSales_USD'[SalesAmt]
)
В первой части формулы указывается одна из функций агрегирования Power Pivot, которая использует таблицу в качестве аргумента. Функция СУММX вычисляет сумму по таблице.
Вторая часть формулы указывает
SUMX,FILTER(table, expression),какие данные следует использовать.SUMXТребуется таблица или выражение, результатом которого является таблица. В этом случае вместо использования всех данных в таблице используетсяFILTERфункция для указания строк таблицы, которые используются.
Выражение фильтра состоит из двух частей: первая часть дает имя таблице, к которой применяется фильтр. Второй компонент определяет выражение, используемое в качестве условия фильтра. В этом случае вы фильтруете реселлеров, которые продали более 5 единиц и товаров стоимостью более 100 долларов. Оператор && является логическим оператором И, который указывает, что обе части условия должны быть истинными, чтобы строка принадлежала отфильтрованному подмножеству.Третья часть формулы сообщает
SUMXфункции, какие значения необходимо суммировать. В этом случае используется только сумма продаж.
Обратите внимание, что такие функции, как ФИЛЬТР, возвращающие таблицу, никогда не возвращают таблицу или строки напрямую, но всегда встроены в другую функцию. Дополнительные сведения о ФИЛЬТР и других функциях, используемых для фильтрации, включая другие примеры, см. в статье "Функции фильтра (DAX)".Примечание
Выражение фильтра зависит от контекста, в котором оно используется. Например, если фильтр используется в мере, а мера используется в сводной таблице или сводной диаграмме, на подмножество возвращаемых данных могут повлиять дополнительные фильтры или срезы, которые пользователь применил к сводной таблице. Дополнительные сведения о контексте см. в статье Контекст в формулах DAX.
Фильтры, удаляющие дубликаты
Помимо фильтрации определенных значений, можно возвращать уникальный набор значений из другой таблицы или столбца. Это может быть полезно, если требуется подсчитать количество уникальных значений в столбце или использовать список уникальных значений для других операций. В DAX доступны две функции для возврата различных значений: функция DISTINCT и функция VALUES.
- Функция DISTINCT проверяет один столбец, указанный в качестве аргумента функции, и возвращает новый столбец, содержащий только значения DISTINCT.
- Функция VALUES также возвращает список уникальных значений, но также возвращает неизвестный элемент. Это удобно, если используются значения из двух таблиц, соединенных отношением, когда отсутствует значение в одной таблице и присутствует в другой. Дополнительные сведения о неизвестном элементе см. в статье Контекст в формулах DAX.
Обе эти функции возвращают целый столбец значений; Поэтому с помощью этих функций можно получить список значений, которые затем передаются другой функции. Например, вы можете использовать следующую формулу, чтобы получить список отдельных продуктов, продаваемых определенным торговым посредником, с помощью уникального ключа продукта, а затем подсчитать продукты в этом списке с помощью функции COUNTROWS:
=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))
Влияние контекста на фильтры
При добавлении формулы DAX в сводную таблицу или сводную диаграмму контекст может влиять на результаты формулы. При работе с таблицей Power Pivot контекстом является текущая строка и ее значения. Если вы работаете со сводной таблицей или сводной диаграммой, то контекст — это набор или подмножество данных, определяемое такими операциями, как срезы или фильтрация. При структурном оформлении сводной таблицы или сводной диаграммы также накладывается собственный контекст. Например, если вы создаете сводную таблицу, которая группирует продажи по регионам и годам, в сводной таблице будут отображаться только данные, относящиеся к этим регионам и годам. Поэтому все меры, добавляемые в сводную таблицу, вычисляются в контексте заголовков столбцов и строк с учетом любых фильтров в формуле меры.
Дополнительные сведения см. в статье Контекст в формулах DAX.
Удаление фильтров
При работе со сложными формулами может потребоваться точно узнать о текущих фильтрах или изменить часть формулы, относящуюся к фильтру. В DAX есть несколько функций, позволяющих удалять фильтры и определять, какие столбцы сохраняются в текущем контексте фильтра. В этом разделе рассказывается, как эти функции влияют на результаты в формуле.
Переопределение всех фильтров с помощью функции ВСЕ
С помощью этой ALL функции можно переопределять любые ранее примененные фильтры и возвращать все строки таблицы функции, выполняющей агрегатную или другую операцию. Если в качестве аргументов ALLаргументов использовать ALL один или несколько столбцов вместо таблицы, функция возвращает все строки, игнорируя фильтры контекста.
Примечание
Если вы знакомы с терминологией реляционных баз данных, то можно представить ALL это как создание естественного левого внешнего соединения всех таблиц.
Предположим, что у вас есть таблицы "Продажи" и "Товары" и нужно создать формулу, по которой будет рассчитана сумма продаж текущего товара, деленная на сумму продаж по всем продуктам. Необходимо иметь в виду, что если формула используется в мере, пользователь сводной таблицы может использовать срез для фильтрации по определенному продукту с названием продукта в строках. Таким образом, чтобы получить истинное значение знаменателя независимо от фильтров или срезов, необходимо добавить функцию ALL для переопределения любых фильтров. Следующая формула является одним из примеров использования ОЛЛ для переопределения эффектов предыдущих фильтров.
=СУММ(Продажи[Сумма])/СУММХ(Продажи[Сумма], ФИЛЬТР(Продажи;ВСЕ(Товары)))
- Первая часть формулы — СУММ (Продажи[Сумма]) — вычисляет числитель.
- Сумма учитывает текущий контекст. Это означает, что при добавлении формулы в вычисляемый столбец применяется контекст строки, а при добавлении формулы в сводную таблицу в качестве меры применяются все фильтры, примененные в сводной таблице (контекст фильтра).
- Второй компонент формулы вычисляет знаменатель. Функция ALL переопределяет любые фильтры, которые могут быть применены к таблице
Products.
Дополнительные сведения, включая подробные примеры, см. в разделе Функция ALL.
Переопределение определенных фильтров с помощью функции ALLEXCEPT
Функция ALLEXCEPT также переопределяет существующие фильтры, но можно указать, что некоторые из существующих фильтров должны быть сохранены. Столбцы, которые вы называете аргументами функции ALLEXCEPT, указывают, какие столбцы будут по-прежнему фильтроваться. Если вы хотите переопределить фильтры для большинства столбцов, но не для всех, функция ALLEXCEPT более удобна, чем функция ALL. Функция ALLEXCEPT особенно полезна при создании сводных таблиц, которые могут быть отфильтрованы по множеству различных столбцов, и при необходимости управлять значениями, используемыми в формуле. Дополнительные сведения, включая подробный пример использования функции ALLEXCEPT в сводной таблице, см. в статье Функция ALLEXCEPT.