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