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