Relações e pesquisas em fórmulas
Um dos recursos mais eficientes do PowerPivot para Excel é a possibilidade de criar relações entre tabelas e, em seguida, usar as tabelas relacionadas para pesquisar ou filtrar dados relacionados. Você recupera valores relacionados de tabelas usando a linguagem da fórmula fornecida com o PowerPivot para Excel, DAX (Data Analysis Expressions). A DAX usa um modelo relacional e, portanto, pode recuperar de forma fácil e precisa os valores relacionados ou correspondentes em outra tabela ou coluna.
É possível criar fórmulas fazem pesquisas como parte de uma coluna calculada ou como parte de uma medida a ser usada em uma Tabela Dinâmica ou em um Gráfico Dinâmico. Para obter mais informações, consulte os tópicos a seguir:
Esta seção descreve as funções DAX que são fornecidas para pesquisa, junto com alguns exemplos de como usar as funções.
.gif "Observação") Observação |
|---|
Dependendo do tipo de operação de pesquisa ou da fórmula de pesquisa que você queira usar, talvez seja necessário criar uma relação entre as tabelas primeiro. Para obter informações sobre como criar relações, consulte Relações entre tabelas. |
Entendendo funções de pesquisa
A possibilidade de pesquisar dados correspondentes ou relacionados de outra tabela é especialmente útil em situações nas quais a tabela atual tem apenas um identificador de algum tipo, mas os dados de que você precisa (como preço do produto, nome do produto e outros detalhes) estão armazenados em uma tabela relacionada. Isso também é útil quando há várias linhas de outra tabela relacionadas à linha ou ao valor atual. Por exemplo, você pode recuperar facilmente todas as vendas associadas a uma determinada região, repositório ou vendedor.
Em comparação com as funções de pesquisa do Excel, como VLOOKUP, que se baseiam em matrizes, ou LOOKUP, que obtêm o primeiro entre vários valores de correspondência, a DAX segue relações existentes entre tabelas unidas por chaves para obter o único valor relacionado com correspondência exata. A DAX também pode recuperar uma tabela de registros relacionados ao registro atual.
| Observação |
|---|
Se você estiver familiarizado com bancos de dados relacionais, considere pesquisas no PowerPivot como se fossem uma instrução de subseleção aninhada no Transact-SQL. |
Para obter mais informações sobre o modelo relacional usado no PowerPivot, consulte Visão geral de relações.
Recuperando um único valor relacionado
A função RELATED retorna um único valor de outra tabela que está relacionada ao valor atual na tabela atual. Você especifica a coluna que contém os dados desejados e a função segue relações existentes entre tabelas para buscar o valor da coluna especificada na tabela relacionada. Em alguns casos, a função deve seguir uma cadeia de relações para recuperar os dados.
Por exemplo, suponhamos que você tenha uma lista dos envios de hoje no Excel. No entanto, a lista só contém um número de ID de funcionário, um número de ID de pedido e um número de ID de transportador, o que dificulta a leitura do relatório. Para obter as informações adicionais desejadas, é possível converter essa lista em uma tabela vinculada do PowerPivot e, em seguida, criar relações com as tabelas Employee e Reseller, correspondendo EmployeeID com o campo EmployeeKey e ResellerID com o campo ResellerKey.
Para exibir as informações de pesquisa na tabela vinculada, você adiciona duas novas colunas calculadas, com as seguintes fórmulas:
= RELATED('Employees'[EmployeeName])
= RELATED('Resellers'[CompanyName])
Antes da pesquisa |
Depois da pesquisa |
||||||||||||||||||||||||||||||||||||||||||||
|
|
A função usa as relações entre a tabela vinculada e as tabelas Employees e Resellers para obter o nome correto de cada linha do relatório. Também é possível usar valores relacionados em cálculos. Para obter mais informações e exemplos, consulte Função RELATED.
Recuperando uma lista de valores relacionados
A função RELATEDTABLE segue uma relação existente e retorna uma tabela que contém todas as linhas correspondentes da tabela especificada. Por exemplo, suponha que você queira saber quantos pedidos cada revendedor colocou este ano. Você pode criar uma nova coluna calculada na tabela Resellers que inclua a fórmula a seguir, que pesquisa registros de cada revendedor na tabela ResellerSales_USD e conta o número de pedidos individuais colocados pelo revendedor. Essas tabelas fazem parte da pasta de trabalho de exemplo DAX. Para obter mais informações sobre dados de exemplo, consulte Obter dados de exemplo para o PowerPivot.
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
Nesta fórmula, a função RELATEDTABLE primeiro obtém o valor de ResellerKey para cada revendedor da tabela atual. (Você não precisa especificar a coluna ID em nenhum lugar da fórmula, porque o PowerPivot usa a relação existente entre as tabelas.) A função RELATEDTABLE obtém todas as linhas da tabela ResellerSales_USD relacionadas a cada revendedor e faz a contagem delas. Observe que, se não houver relação (direta ou indireta) entre as duas tabelas, você obterá todas as linhas da tabela ResellerSales_USD.
Para o revendedor Modular Cycle Systems em nosso banco de dados de exemplo, como há quatro pedidos na tabela de vendas, a função retorna 4. Para Associated Bikes, como o revendedor não tem nenhuma venda, a função retorna em branco.
Reseller |
Registros na tabela de vendas para o revendedor |
||||||||||
Modular Cycle Systems |
|
||||||||||
Associated Bikes |
|
| Observação |
|---|
Como a função RELATEDTABLE retorna uma tabela, e não um único valor, ela deve ser usada como um argumento para uma função que execute operações em tabelas. Para obter mais informações, consulte Função RELATEDTABLE. |
Consulte também
Conceitos
Adicionar cálculos a relatórios, gráficos e tabelas dinâmicas
Visão geral da linguagem DAX (Data Analysis Expressions)