Aplicar relações muitos para muitos no Power BI Desktop

Com as relações com uma cardinalidade muitos para muitos no Power BI Desktop, você pode unir tabelas que usam uma cardinalidade igual a muitos para muitos. Você pode criar modelos de dados mais fáceis e intuitivos que contêm duas ou mais fontes de dados. As relações com uma cardinalidade muitos para muitos fazem parte das funcionalidades mais amplas de modelos compostos do Power BI Desktop.

Captura de tela de uma relação muitos para muitos no painel Editar relação.

Uma relação com uma cardinalidade muitos para muitos no Power BI Desktop é composta por um de três recursos relacionados:

  • Modelos compostos: Um modelo composto permite que um relatório tenha duas ou mais conexões de dados, incluindo conexões do DirectQuery ou importação, em qualquer combinação. Para obter mais informações, confira Usar modelos compostos no Power BI Desktop.

  • Relações com uma cardinalidade muitos para muitos: Com os modelos compostos, você pode estabelecer relações com uma cardinalidade muitos para muitos entre tabelas. Esta abordagem remove os requisitos de valores exclusivos nas tabelas. Ela também remove as soluções alternativas anteriores, como introduzir novas tabelas somente para estabelecer relações. O recurso é descrito em detalhes neste artigo.

  • Modo de armazenamento: agora você pode especificar quais visuais exigem uma consulta para fontes de dados de back-end. Visuais que não exigem uma consulta serão importados, mesmo se forem baseados no DirectQuery. Esse recurso ajuda a melhorar o desempenho e reduzir a carga de back-end. Anteriormente, até mesmo visuais simples, como segmentações de dados, iniciavam consultas que eram enviadas para fontes de back-end. Para mais informações, confira Gerenciar o modo de armazenamento no Power BI Desktop.

Problemas resolvidos por uma relação com uma cardinalidade muitos para muitos

Antes de as relações com uma cardinalidade muitos para muitos serem disponibilizadas, a relação entre duas tabelas era definida no Power BI. Pelo menos uma das colunas da tabela envolvidas na relação precisava conter valores exclusivos. Muitas vezes, no entanto, não há colunas contendo valores exclusivos.

Por exemplo, duas tabelas podem ter uma coluna rotulada CountryRegion. No entanto, os valores de CountryRegion não eram exclusivos em nenhuma das tabelas. Para unir essas tabelas, você precisava criar uma solução alternativa. Uma solução alternativa pode ser introduzir tabelas extras com os valores exclusivos necessários. Com as relações com uma cardinalidade muitos para muitos, você poderá unir essas tabelas diretamente se usar uma relação com uma cardinalidade igual a muitos para muitos.

Usar relações com uma cardinalidade muitos para muitos

Ao definir uma relação entre duas tabelas no Power BI, é preciso definir a cardinalidade da relação. Por exemplo, a relação entre ProductSales e Product,usando as colunas ProductSales[ProductCode] e Product[ProductCode],seria definida como Muitos para um. Definimos a relação dessa forma, porque cada produto tem muitas vendas e a coluna na tabela Product (ProductCode) é exclusiva. Ao definir uma cardinalidade da relação como Muitos para um, Um para muitos ou Um para um, o Power BI a valida, de modo que a cardinalidade selecionada corresponda aos dados reais.

Por exemplo, confira o modelo simples nesta imagem:

Captura de tela da tabela ProductSales e Product no modo de exibição Relação.

Agora, imagine que a tabela Produto exibe apenas duas linhas, conforme mostrado:

Captura de tela de um visual de tabela Produto com duas linhas.

Imagine também que a tabela Sales só tem quatro linhas, incluindo a linha para um produto C. Devido a um erro de integridade referencial, a linha do produto C não existe na tabela Product.

Captura de tela de um visual de tabela Vendas com quatro linhas.

O ProductName e o Price (da tabela Product), juntamente com a Qtd total de cada produto (da tabela ProductSales), serão exibidos conforme mostrado:

Captura de tela de um Visual exibindo o nome, o preço e a quantidade do produto.

Como você pode ver na imagem anterior, uma linha ProductName em branco está associada às vendas do produto C. Essa linha em branco explica as seguintes considerações:

  • Qualquer linha na tabela ProductSales sem nenhuma linha correspondente na tabela Produto. Há um problema de integridade referencial, como vemos no produto C deste exemplo.

  • Qualquer linha na tabela ProductSales para a qual a coluna de chave estrangeira é nula.

Por esses motivos, em ambos os casos, a linha em branco refere-se a vendas em que ProductName e Price são desconhecidos.

Às vezes, as tabelas são unidas por duas colunas, mas nenhuma delas é exclusiva. Por exemplo, considere estas duas tabelas:

  • A tabela Vendas exibe dados de vendas por Estado, com cada linha contendo o valor das vendas para o tipo de venda nesse estado. Os estados são CA, WA e TX.

    Captura de tela de uma tabela Vendas exibindo vendas por estado.

  • A tabela CityData exibe os dados nas cidades, incluindo a população e o estado (como CA, WA e Nova York).

    Captura de tela de uma tabela Vendas exibindo cidade, estado e população.

Agora, há uma coluna para Estado em ambas as tabelas. É razoável querer relatar o total de vendas por estado e a população total de cada estado. No entanto, há um problema: a coluna State não é exclusiva em nenhuma das tabelas.

A solução alternativa anterior

Antes da versão de julho de 2018 do Power BI Desktop, não era possível criar uma relação direta entre essas tabelas. Uma solução alternativa comum era:

  • Criar uma terceira tabela que continha apenas as IDs de State exclusivas. A tabela pode ser qualquer uma destas ou todas estas:

    • Uma tabela calculada (definida usando o [DAX] Data Analysis Expressions).
    • Uma tabela baseada em uma consulta que é definida no Editor do Power Query, que pode exibir as IDs exclusivas extraídas de uma das tabelas.
    • O conjunto completo combinado.
  • Em seguida, relacione as duas tabelas originais à nova tabela usando relações comuns Muitos para um.

Você pode deixar a tabela de solução alternativa visível. Ou você pode ocultar a tabela de solução alternativa, para que ela não apareça na lista Campos . Se você ocultar a tabela, as relações Muitos para um normalmente serão definidas para filtro em ambas as direções e você poderá usar o campo Estado de uma das tabelas. A última filtragem cruzada se propagaria para a outra tabela. Essa abordagem é mostrada na imagem a seguir:

Captura de tela de uma tabela Estado oculta no modo de exibição Relação.

Um visual que exibe o Estado (da tabela CityData), juntamente com a População total e o total de Vendas apareceria desta forma:

Captura de tela mostrando uma tabela com dados de Estado, População e Vendas.

Observação

Como o estado da tabela CityData é usado nesta solução alternativa, somente os estados dessa tabela são listados e, portanto, o TX é excluído. Além disso, ao contrário das relações Muitos para um, embora a linha do total inclua todas as Vendas (incluindo as do TX), os detalhes não incluem uma linha em branco que abrange essas linhas não correspondentes. Da mesma forma, nenhuma linha em branco abrange Sales, para a qual há um valor nulo em State.

Suponha que você também adicione City a esse visual. Embora a população por City seja conhecida, as Sales mostradas para a City simplesmente repetirão as Sales do State correspondente. Esse cenário normalmente ocorre quando o agrupamento de colunas não está relacionado a uma medida agregada, conforme mostrado aqui:

Captura de tela de uma tabela mostrando a população e as vendas estaduais e municipais.

Digamos que você defina a nova tabela Sales como a combinação de todos os States aqui e a tornemos visível na lista Fields. O mesmo visual exibirá State (na nova tabela), a Population total e o total de Sales:

Captura de tela de um visual mostrando o estado, a população e o visual de vendas.

Como você pode ver, o TX — com os dados de Sales, mas com os dados de Population desconhecidos,e New York — com os dados de Population conhecidos, mas sem dados de Sales, seriam incluídos. Essa solução alternativa não é ideal, e tem vários problemas. Nas relações com uma cardinalidade muitos para muitos, os problemas resultantes são resolvidos conforme descrito na próxima seção.

Para obter mais informações sobre como implementar essa solução alternativa, consulte Diretrizes de relação muitos para muitos.

Usar uma relação com uma cardinalidade muitos para muitos em vez da solução alternativa

Você pode relacionar tabelas diretamente, como aquelas que descrevemos anteriormente, sem precisar recorrer a soluções alternativas semelhantes. Agora é possível definir a cardinalidade da relação como muitos para muitos. Essa configuração indica que nenhuma tabela contém valores exclusivos. Para essas relações, você ainda pode controlar qual tabela filtra a outra tabela. Ou você pode aplicar a filtragem bidirecional, em que cada tabela filtra a outra.

No Power BI Desktop, a cardinalidade usa como padrão muitos para muitos quando determina que nenhuma das tabelas contém valores exclusivos para as colunas da relação. Nesses casos, uma mensagem de aviso confirma que você deseja definir uma relação e que a alteração não é o efeito não intencional de um problema de dados.

Por exemplo, quando você cria uma relação diretamente entre CityData e Sales, em que os filtros devem fluir de CityData para Sales, Power BI Desktop exibe a caixa de diálogo Editar relação:

Captura de tela da caixa de diálogo Editar relação com Cardinalidade e Direção do filtro cruzado realçadas.

O modo de exibição Relação resultante conteria a relação direta, muitos para muitos, entre as duas tabelas. A aparência das tabelas na lista Campos e o comportamento posterior delas quando os visuais são criados são semelhantes a quando aplicamos a solução alternativa. Na solução alternativa, a tabela extra que exibe os dados distintos de Estado não se torna visível. Conforme descrito anteriormente, um visual que mostra dados de State, Population e Sales será exibido:

Captura de tela de uma tabela Estado, População e Vendas.

As principais diferenças entre as relações com uma cardinalidade muitos para muitos e as relações muitos para um, mais comuns, são as seguintes:

  • Os valores mostrados não incluem uma linha em branco que conta para as linhas incompatíveis na outra tabela. Além disso, os valores não consideram as linhas em que a coluna usada na relação na outra tabela é nula.

  • Não é possível usar a função RELATED(), pois mais de uma linha pode estar relacionada.

  • Usar a função ALL() em uma tabela não removerá os filtros aplicados a outras tabelas relacionadas a ela por uma relação muitos para muitos. No exemplo anterior, uma medida definida, conforme mostrado aqui, não removerá os filtros nas colunas da tabela CityData relacionada:

    Captura de tela de um exemplo de script. O exemplo é, Total de vendas = Calculate(Sum('Sales'[Sales]), All('Sales')).

    Um visual que mostra os dados de State, Sales e Sales total resultará neste gráfico:

    Captura de tela de um visual de tabela mostrando o total de Estado, Vendas e Vendas resultante da fórmula.

Com as diferenças anteriores em mente, verifique se os cálculos que usam ALL(<Table>), como % do total geral, estão retornando os resultados pretendidos.

Considerações e limitações

Há algumas limitações nesta versão das relações com uma cardinalidade muitos para muitos e dos modelos compostos.

As seguintes fontes (multidimensionais) do Live Connect não podem ser usadas com os modelos compostos:

  • SAP HANA
  • SAP Business Warehouse
  • SQL Server Analysis Services
  • Conjuntos de dados do Power BI
  • Azure Analysis Services

Ao se conectar a essas fontes multidimensionais usando o DirectQuery, não é possível se conectar à outra fonte do DirectQuery nem a combinar com os dados importados.

As limitações existentes no uso do DirectQuery ainda se aplicam quando você usa relações com uma cardinalidade muitos para muitos. Muitas limitações agora são por tabela, dependendo do modo de armazenamento da tabela. Por exemplo, uma coluna calculada em uma tabela importada pode se referir a outras tabelas, mas uma coluna calculada em uma tabela do DirectQuery ainda se refere apenas às colunas na mesma tabela. Outras limitações serão aplicáveis ao modelo como um todo se uma das tabelas do modelo forem DirectQuery. Por exemplo, os recursos Insights Rápidos e P e R não estarão disponíveis em um modelo se uma das tabelas desse modelo tiver o modo de armazenamento do DirectQuery.

Próximas etapas

Para obter mais informações sobre modelos compostos e DirectQuery, confira os seguintes artigos: