Um dos recursos mais poderosos do Power Pivot é a capacidade de criar relações entre tabelas e depois usar as tabelas relacionadas para pesquisar ou filtrar dados relacionados. Você recupera valores relacionados de tabelas usando a linguagem de fórmula fornecida com o Power Pivot, Data Analysis Expressions (DAX). O DAX usa um modelo relacional e, portanto, pode recuperar com facilidade e precisão valores relacionados ou correspondentes em outra tabela ou coluna. Se você estiver familiarizado com PROCV no Excel, essa funcionalidade no Power Pivot é semelhante, mas muito mais fácil de implementar.
Você pode criar fórmulas que fazem pesquisas como parte de uma coluna calculada ou como parte de uma medida para usar em uma Tabela Dinâmica ou em um Gráfico Dinâmico. Para saber mais, confira os seguintes tópicos:
Campos calculados no Power Pivot
Colunas Calculadas no Power Pivot
Esta seção descreve as funções DAX fornecidas para pesquisa, juntamente com alguns exemplos de como usar as funções.
Observação
Dependendo do tipo de operação de pesquisa ou da fórmula de pesquisa que você deseja usar, talvez seja necessário criar uma relação entre as tabelas primeiro.
Noções básicas sobre as funções de pesquisa
A capacidade de pesquisar dados correspondentes ou relacionados de outra tabela é particularmente útil em situações em que a tabela atual tem apenas um identificador de algum tipo, mas os dados necessários (como preço do produto, nome ou outros valores detalhados) são armazenados em uma tabela relacionada. Também é útil quando há várias linhas em outra tabela relacionadas à linha atual ou ao valor atual. Por exemplo, você pode recuperar facilmente todas as vendas vinculadas a uma determinada região, loja ou vendedor.
Ao contrário das funções de pesquisa do Excel, como PROCV, que são baseadas em matrizes, ou PESQUISA, que obtém o primeiro de vários valores correspondentes, o DAX segue as relações existentes entre tabelas unidas por chaves para obter o único valor relacionado que corresponde exatamente. O DAX também pode recuperar uma tabela de registros relacionados ao registro atual.
Observação
Se você estiver familiarizado com bancos de dados relacionais, poderá pensar nas pesquisas no Power Pivot como semelhantes a uma instrução subselect aninhada no Transact-SQL.
Recuperando um único valor relacionado
A função RELATED retorna um único valor de outra tabela relacionada ao valor atual na tabela atual. Você especifica a coluna que contém os dados desejados, e a função segue as relações existentes entre as 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, suponha que você tenha uma lista das remessas de hoje no Excel. No entanto, a lista contém apenas um número de ID de funcionário, um número de ID do pedido e um número de ID da transportadora, dificultando a leitura do relatório. Para obter as informações adicionais desejadas, você pode converter essa lista em uma tabela vinculada do Power Pivot e, em seguida, criar relações com as tabelas Funcionário e Revendedor, correspondendo IDPdoFuncionário ao campo EmployeeKey e IDProdutor ao campo ResellerKey.
Para exibir as informações de pesquisa na tabela vinculada, adicione duas novas colunas calculadas, com as seguintes fórmulas:
= RELATED('Employees',[EmployeeName])
= RELATED('Resellers'[CompanyName])
Remessas de hoje antes da pesquisa
| OrderID | EmployeeID | ResellerID |
|---|---|---|
| 100314 | 230 | 445 |
| 100315 | 15 | 445 |
| 100316 | 76 | 108 |
Tabela Employees
| EmployeeID | verificado | Reseller |
|---|---|---|
| 230 | Kuppa Vamsi | Sistemas de ciclo modular |
| 15 | Pilar Ackeman | Sistemas de ciclo modular |
| 76 | Kim Ralls | Bicicletas associadas |
Remessas de hoje com pesquisas
| OrderID | EmployeeID | ResellerID | verificado | Reseller |
|---|---|---|---|---|
| 100314 | 230 | 445 | Kuppa Vamsi | Sistemas de ciclo modular |
| 100315 | 15 | 445 | Pilar Ackeman | Sistemas de ciclo modular |
| 100316 | 76 | 108 | Kim Ralls | Bicicletas associadas |
A função usa as relações entre a tabela vinculada e a tabela Funcionários e Revendedores para obter o nome correto para cada linha no relatório. Você também pode usar valores relacionados para 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 descobrir quantos pedidos cada revendedor fez este ano. Você pode criar uma nova coluna calculada na tabela Revendedores que inclua a fórmula a seguir, que pesquisa registros para cada revendedor na tabela ResellerSales_USD e conta o número de pedidos individuais feitos por cada revendedor.
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
Nessa fórmula, a função RELATEDTABLE obtém primeiro o valor de ResellerKey para cada revendedor na tabela atual. (Você não precisa especificar a coluna ID em nenhum lugar na fórmula, porque o Power Pivot usa a relação existente entre as tabelas.) Em seguida, a função RELATEDTABLE obtém todas as linhas da tabela ResellerSales_USD relacionadas a cada revendedor e conta as linhas. Se não houver nenhuma 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, há quatro pedidos na tabela de vendas, portanto, a função retorna 4. Para Bicicletas Associadas, o revendedor não tem vendas, portanto, a função retorna um espaço em branco.
| Reseller | Registros na tabela de vendas deste revendedor |
|---|---|
| Sistemas de ciclo modular | Reseller ID |
| 445 | |
| 445 | |
| 445 | |
| 445 | |
| Reseller ID | |
| Bicicletas associadas |
Observação
Como a função RELATEDTABLE retorna uma tabela, e não um único valor, ela deve ser usada como argumento para uma função que executa operações em tabelas. Para obter mais informações, consulte Função RELATEDTABLE.