Esta secção descreve como criar filtros em fórmulas do Data Analysis Expressions (DAX). Pode criar filtros dentro de fórmulas, para restringir os valores dos dados de origem utilizados em cálculos. Para o fazer, especifica uma tabela como entrada para a fórmula e, em seguida, especifica uma expressão do filtro. A expressão do filtro fornecida é utilizada para consultar os dados e devolve apenas um subconjunto dos dados de origem. O filtro é aplicado dinamicamente sempre que atualizar o resultado da fórmula, dependendo do contexto atual dos seus dados.
Neste artigo
Criar um Filtro numa Tabela utilizada numa Fórmula
Pode aplicar filtros em fórmulas que utilizam uma tabela como entrada. Em vez de introduzir um nome de tabela, utilize a função FILTER para definir um subconjunto de linhas da tabela especificada. Esse subconjunto é transmitido para outra função, para operações como agregações personalizadas.
Por exemplo, imagine que tem uma tabela de dados que contém informações sobre encomendas de revendedores e quer calcular o montante de vendas de cada revendedor. No entanto, só pretende mostrar o montante de vendas dos revendedores que venderam várias unidades dos produtos de valor mais elevado. A fórmula seguinte, baseada no livro de exemplo do DAX, mostra um exemplo do modo como pode criar este cálculo utilizando um filtro:
=SUMX(
FILTRAR ('ResellerSales_USD', 'ResellerSales_USD'[Quantidade] > 5 &&
'ResellerSales_USD'[ProductStandardCost_USD] > 100),
'ResellerSales_USD'[SalesAmt]
)
-
A primeira parte da fórmula especifica uma das funções de agregação do Power Pivot, que considera uma tabela como argumento. A função SUMX calcula uma soma sobre uma tabela.
-
A segunda parte da fórmula, FILTER(table, expression),, indica à função SUMX que dados utilizar. A função SUMX requer uma tabela ou expressão que resulte numa tabela. Aqui, em vez de utilizar os dados existentes numa tabela, utiliza a função FILTER para especificar que linhas da tabela são utilizadas.
A expressão de filtro tem duas partes: a primeira parte indica o nome da tabela ao qual o filtro é aplicado. A segunda parte define uma expressão a utilizar como condição de filtro. Neste caso, está a filtrar pelos revendedores que venderam mais de 5 unidades e produtos que custam mais de €100. O operador, &&, é um operador AND lógico, que indica que ambas as partes da condição têm de ser verdadeiras para a linha pertencer ao subconjunto filtrado.
-
A terceira parte da fórmula indica à função SUMX que valores devem ser somados. Neste caso está a utilizar apenas o montante das vendas.
Repare que as funções como FILTER, que devolvem uma tabela, nunca devolvem diretamente a tabela ou as linhas, sendo sempre incorporadas noutra função. Para obter mais informações sobre FILTER e outras funções utilizadas para filtragem, incluindo mais exemplos, consulte o artigo sobre as Funções de Filtro (DAX).
Nota: A expressão de filtro é afetada pelo contexto em que é utilizada. Por exemplo, se utilizar um filtro numa medida e a medida for utilizada numa Tabela Dinâmica ou gráfico dinâmico, o subconjunto de dados devolvido poderá ser afetado por filtros ou Filtragem adicionais que o utilizador tenha aplicado na tabela dinâmica. Para obter mais informações sobre contexto, consulte o artigo Contexto em Fórmulas DAX.
Filtros que Removem Duplicados
Para além de filtrar valores específicos, pode devolver um conjunto exclusivo de resultados a partir de outra tabela ou coluna. Esta operação pode ser útil quando pretender contar o número de valores exclusivos numa coluna ou utilizar uma lista de valores exclusivos para outras operações. O DAX fornece duas funções para devolver valores distintos: Função DISTINCT e Função VALUES.
-
A função DISTINCT examina uma coluna única especificada como argumento para a função e devolve uma coluna nova que contém apenas os valores distintos.
-
A função VALUES também devolve uma lista de valores exclusivos, mas também devolve o membro Desconhecido. Esta operação é útil quando utilizar valores de duas tabelas que estão associadas por uma relação e um valor está em falta numa tabela e presente na outra. Para obter mais informações sobre o membro Desconhecido, consulte o artigo Contexto em Fórmulas DAX.
Ambas as funções devolvem uma coluna completa de valores, o que significa que tem de utilizar as funções para obter uma lista de valores que é transmitida para outra função. Por exemplo, poderá utilizar a fórmula seguinte para obter uma lista dos produtos distintos vendidos por um revendedor específico, utilizando a chave de produto exclusiva, e, em seguida, contar os produtos existentes nessa lista utilizando a função COUNTROWS:
=COUNTROWS(DISTINCT('VendasDeRevendedor_USD'[ChaveDeProduto]))
Como o Contexto Afeta os Filtros
Quando 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 estiver a trabalhar numa tabela do Power Pivot, o contexto é a linha atual e os respetivos valores. Se estiver a trabalhar numa Tabela Dinâmica ou Gráfico Dinâmico, o contexto é o conjunto ou subconjunto de dados definido por operações tais como a segmentação de dados ou a filtragem. A estrutura da Tabela Dinâmica ou Gráfico Dinâmico também impõe um contexto próprio. Por exemplo, se criar uma Tabela Dinâmica que agrupa as vendas por região e ano, só serão apresentados na Tabela Dinâmica os dados relativos a essas regiões e anos. Consequentemente, todas as medidas que adicionar à Tabela Dinâmica serão calculadas no contexto dos títulos de coluna e linha e os filtros na fórmula de medida.
Para obter mais informações, consulte o artigo Contexto em Fórmulas DAX.
Remover Filtros
Quando trabalhar com fórmulas complexas, poderá pretender saber exatamente quais são os filtros atuais ou poderá pretender modificar a parte do filtro da fórmula. O DAX fornece várias funções que lhe permitem remover filtros e controlar quais as colunas retidas como parte do contexto de filtro atual. Esta secção fornece uma descrição geral do modo como estas funções afetam os resultados existentes numa fórmula.
Substituir Todos os Filtros com a Função ALL
Pode utilizar a função ALL para substituir os filtros previamente aplicados e devolver todas as linhas da tabela à função que está a executar a agregação ou outra operação. Se utilizar uma ou mais colunas, em vez de uma tabela, como argumentos da função ALL, a função ALL devolve todas as linhas, ignorando os filtros de contexto.
Nota: Se estiver familiarizado com a terminologia de bases de dados relacionais, a função ALL pode considerar-se como a geradora da associação externa à esquerda natural de todas as tabelas.
Por exemplo, imagine que tem as tabelas Vendas e Produtos e pretende criar uma fórmula que calcule a soma das vendas do produto atual dividida pelas vendas de todos os produtos. Tem de ter em consideração o facto de que, se a fórmula for utilizada numa medida, o utilizador da Tabela Dinâmica poderá estar a utilizar uma Filtragem para filtrar por um produto específico, com o nome do produto nas linhas. Assim, para obter o valor verdadeiro do denominador, independentemente dos filtros ou Segmentação de Dados, terá de adicionar a função ALL para ignorar todos os filtros. A fórmula seguinte é um exemplo de como pode utilizar a função ALL para substituir o efeito de filtros anteriores:
=SUM (Vendas[Montante])/SUMX(Vendas[Montante], FILTER(Vendas, ALL(Produtos)))
-
A primeira parte da fórmula, SUM (Vendas[montante]), calcula o enumerador.
-
A soma tem em conta o contexto atual, o que significa que, se adicionar a fórmula a uma coluna calculada, o contexto da linha é aplicado e, se adicionar a fórmula a uma Tabela Dinâmica como medida, todos os filtros aplicados à Tabela Dinâmica (o contexto do filtro) são aplicados.
-
A segunda parte da fórmula calcula o denominador. A função ALL substitui os filtros aplicados na tabela Products.
Para obter mais informações, incluindo exemplos detalhados, consulte Função ALL.
Substituir Filtros Específicos com a Função ALLEXCEPT
A função ALLEXCEPT também substitui filtros existentes, mas é possível especificar que alguns dos filtros existentes devem ser preservados. As colunas indicadas como argumentos da função ALLEXCEPT especificam as colunas que continuarão a ser filtradas. Se pretender substituir os filtros da maior parte das colunas, mas não de todas, a função ALLEXCEPT é mais prática do que a ALL. A função ALLEXCEPT é especialmente útil quando estiver a criar Tabelas Dinâmicas que possam ser filtradas em várias colunas diferentes e pretender controlar os valores utilizados na fórmula. Para obter mais informações, incluindo um exemplo detalhado sobre como utilizar a função ALLEXCEPT numa tabela dinâmica, consulte o tátil ALLEXCEPT.
