Filtrar dados em fórmulas
É possível criar filtros dentro de fórmulas para restringir os valores dos dados de origem que são usados em cálculos. Você faz isto especificando uma tabela como uma entrada para a fórmula e definindo uma expressão de filtro. A expressão de filtro fornecida é usada para consultar os dados e retornar apenas um subconjunto dos dados de origem. O filtro é aplicado dinamicamente toda vez que você atualiza os resultados da fórmula, dependendo do contexto atual de seus dados. Esta seção descreve como criar filtros dentro de fórmulas DAX (Data Analysis Expressions).
Criando um filtro em uma tabela usada em uma fórmula
Você pode aplicar filtros em fórmulas que consideram uma tabela como entrada. Em vez de inserir um nome de tabela, você usa a função FILTER para definir um subconjunto de linhas da tabela especificada. Em seguida, essa tabela é passada a outra função para operações como agregações personalizadas.
Por exemplo, suponhamos que você tenha uma tabela de dados que contenha informações de pedidos sobre revendedores e queira calcular quanto cada revendedor vendeu. No entanto, você deseja mostrar a quantidade de vendas apenas para esses revendedores que venderam várias unidades dos produtos de maior valor. A seguinte fórmula, com base na planilha de exemplo DAX, mostra um exemplo de como é possível criar esse cálculo usando-se um filtro:
=SUMX(
FILTER ('ResellerSales_USD', 'ResellerSales_USD'[Quantity] > 5 &&
'ResellerSales_USD'[ProductStandardCost_USD] > 100),
'ResellerSales_USD'[SalesAmt]
)
A primeira parte da fórmula especifica uma das funções de agregação do PowerPivot, que usa uma tabela como um argumento. SUMX calcula a soma em uma tabela.
A segunda parte da fórmula, FILTER(table, expression), diz ao SUMX os dados a serem usados. SUMX exige uma tabela ou uma expressão que resulta em uma tabela. Aqui, em vez de usar todos os dados de uma tabela, você usa a função FILTER para especificar quais linhas da tabela são usadas.
A expressão de filtro tem duas partes: a primeira parte nomeia a tabela a que o filtro se aplica. A segunda parte define uma expressão a ser usada como a condição de filtro. Nesse caso, você está filtrando em revendedores que venderam mais de 5 unidades e produtos que custaram mais de R$ 100. O operador, &&, é um operador AND lógico, que indica que ambas as partes da condição devem ser verdadeiras na linha para que pertençam ao subconjunto filtrado.
A terceira parte da fórmula informa a função SUMX, em que os valores devem ser somados. Nesse caso, você está usando apenas a quantidade de vendas.
Observe que funções como FILTER, que retornam uma tabela, jamais retornam a tabela ou as linhas diretamente para a pasta de trabalho do PowerPivot, mas sempre são inseridas em outra função. Para obter mais informações sobre FILTER e outras funções usadas na filtragem, inclusive mais exemplos, consulte Funções de filtro (DAX).
.gif "Observação") Observação |
|---|
A expressão de filtro é afetada pelo contexto em que é usada. Por exemplo, se você usar um filtro em uma medida, e a medida for usada em uma Tabela Dinâmica ou um Gráfico Dinâmico, o subconjunto de dados retornado poderá ser afetado por filtros adicionais ou Segmentações de Dados aplicados pelo usuário na Tabela Dinâmica. Para obter mais informações sobre o contexto, consulte Contexto em fórmulas DAX. |
Filtros que removem duplicatas
Além da filtragem para obter valores específicos, é possível retornar um conjunto de valores exclusivo de outra tabela ou coluna. Isso pode ser útil quando você deseja contar o número de valores exclusivos de uma coluna, ou usar uma lista de valores exclusivos para outras operações. O DAX fornece duas funções para retornar valores distintos: Função DISTINCT e Função VALUES.
A função DISTINCT examina uma única coluna especificada como um argumento para a função e retorna uma nova coluna contendo apenas os valores distintos.
A função VALUES também retorna uma lista de valores exclusivos, mas também o membro desconhecido. Isso é útil quando você usa valores de duas tabelas unidos por uma relação e um valor não é encontrado em uma tabela e está na outra. Para obter mais informações sobre o membro desconhecido, consulte Contexto em fórmulas DAX.
Essas duas funções retornam uma coluna inteira de valores; por isso, você usa as funções para obter uma lista de valores passados para outra função. Por exemplo, você pode usar a fórmula a seguir para obter uma lista dos produtos distintos vendidos por um revendedor específico, usando a chave de produto exclusiva e, em seguida, contar os produtos nessa lista usando a função COUNTROWS:
=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))
Como o contexto afeta filtros
Quando você adiciona uma fórmula DAX a uma Tabela Dinâmica ou Gráfico Dinâmico, os resultados da fórmula podem ser afetados pelo contexto. Se você estiver trabalhando em uma tabela do PowerPivot, o contexto será a linha atual e seus valores. Se você estiver trabalhando em um Tabela Dinâmica ou Gráfico Dinâmico, o contexto significará o conjunto ou subconjunto de dados que é definido por operações como divisão ou filtragem. O design da Tabela Dinâmica ou Gráfico Dinâmico também impõe seu próprio contexto. Por exemplo, se você criar uma Tabela Dinâmica que agrupe vendas por região e ano, apenas os dados que se aplicam a essas regiões e anos aparecerão na Tabela Dinâmica. Assim, qualquer medida que você adicione à Tabela Dinâmica será calculada no contexto dos cabeçalhos de linha e coluna mais qualquer filtro na fórmula de medida.
Para obter mais informações, consulte Contexto em fórmulas DAX.
Removendo filtros
Ao trabalhar com fórmulas complexas, talvez você queira saber exatamente o que são os filtros atuais ou modificar o contexto de filtro como parte da fórmula. A DAX oferece várias funções que o habilitam a remover filtros e a controlar quais colunas são retidas como parte do contexto de filtro atual. Esta seção fornece uma visão geral sobre como estas funções afetam resultados em uma fórmula.
Substituindo todos os filtros com a função ALL
É possível usar a função ALL para substituir qualquer filtro aplicado anteriormente e retornar todas as linhas da tabela para a função que está executando a agregação ou outra operação. Se você usar uma ou mais colunas, e não uma tabela, como argumentos para ALL, a função ALL retornará todas as linhas, ignorando qualquer filtro de contexto.
| Observação |
|---|
Se estiver familiarizado com a terminologia de banco de dados relacional, você poderá pensar em ALL como gerador da junção externa esquerda natural de todas as tabelas. |
Por exemplo, digamos que você tenha as tabelas Sales e Products e que gostaria de criar uma fórmula que calculará a soma das vendas do produto atual dividida pelas vendas de todos os produtos. Você deve levar em conta o fato de que, se a fórmula for usada em uma medida, o usuário da Tabela Dinâmica poderá estar usando uma Segmentação de Dados para filtrar por um determinado produto com o nome do produto nas linhas. Portanto, para obter o verdadeiro valor do denominador, sejam quais forem os filtros ou as segmentações de dados, adicione a função ALL para substituir qualquer filtro. A seguinte fórmula é um exemplo de como usar ALL para substituir os efeitos de filtros anteriores:
=SUM (Sales[Amount])/SUMX(Sales[Amount], FILTER(Sales, ALL(Products)))
A primeira parte da fórmula, SUM (Sales[Amount]), calcula o numerador.
A soma leva em conta o contexto atual. Isso significa que, se você adicionar a fórmula a uma coluna calculada, o contexto de linha será aplicado e, se você adicionar a fórmula a uma Tabela Dinâmica como uma medida, qualquer filtro aplicado na Tabela Dinâmica (o contexto de filtro) será aplicado.
A segunda parte da fórmula calcula o denominador. A função ALL substitui qualquer filtro que possa ser se aplicado à tabela Products.
Para obter mais informações, inclusive exemplos detalhados, consulte Função ALL.
Substituindo filtros específicos pela função ALLEXCEPT
A função ALLEXCEPT também substitui filtros existentes, embora seja possível especificar que alguns dos filtros existentes devam ser preservados. As colunas nomeadas como argumentos para a função ALLEXCEPT especificam as colunas que continuarão sendo filtradas. Se você quiser substituir filtros da maioria das colunas, mas não todas, ALLEXCEPT será mais prática que ALL. A função ALLEXCEPT é especialmente útil quando você está criando Tabelas Dinâmicas que possam ser filtradas em muitas colunas diferentes e queira controlar os valores usados na fórmula. Para obter mais informações, inclusive um exemplo detalhado de como usar ALLEXCEPT em uma Tabela Dinâmica, consulte Função ALLEXCEPT.