Power BI Many to Many Relationships

Sempre que falamos de relacionamentos, ou de cross filter direction, ou de cardinalidade, etc, estamos falando da parte de Modelagem de Dados. Então pra você entender bem os conceitos do que vamos falar aqui é preciso que esteja familiarizado com modelagem de dados em Power BI.

O conceitos de modelo de dados do Power BI focam muito no conceito star-schema, mas é claro, o Power BI também funciona com snowflake ou flat-table, mas pra início de conversa, dê antes uma lida nesses tópicos:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

Talvez, antes ainda de começar, seja bom que você leia o que escrevi sobre JOINS (MERGE) no Power Query pra te dar uma boa noção de Joins e também o que já escrevi sobre relacionamentos aqui.

Outra coisa, todos relacionamentos many to many são WEAK. Leia mais sobre Weak Relationships aqui.

Many to Many Relationships

Nesse tópico vou tentar ser mais detalhado sobre os relacionamentos N:N, ou *:* ou Many to Many ou Muitos pra Muitos.

A imagem acima representa um tipo de relacionamento N:N, também podemos falar “cardinalidade N:N”. Cardinalidade muitos para muitos:

• Descreve um relacionamento em que muitos valores são comuns entre duas tabelas.
• Não requer valores exclusivos em nenhuma das tabelas de um relacionamento.
• Não é recomendado; a falta de valores exclusivos introduz ambiguidade e seus usuários podem não saber qual coluna de valores está se referindo a quê.

Por exemplo, a figura acima mostra um relacionamento muitos para muitos entre as tabelas Sales e Order na coluna OrderDate, porque várias Sales podem ter várias Order associadas a elas. A ambiguidade é introduzida porque ambas as tabelas podem ter a mesma data de pedido.

Cross Filter Direction

Além do relacionamento, o Power BI ainda tem um negócio chamado direção do filtro. Vamos olhar denovo para aquela imagem do tópico acima, mas dessa vez vou destacar a parte do “cross filter direction” (ou direção de filtro):

Perceba que, nesse exemplo da imagem acima, o filtro é do tipo BOTH. Quando um filtro do tipo BOTH está presente significa que tanto a tabela da esquerda, quanto da direita podem filtrar o resultado uma da outra. Porém, em relacionamentos N:N você pode mudar a direção desse filtro. Muita gente que acha que N:N só permite both, e não é verdade, vc pode definir a direção desse relacionamento dando SINGLE saindo da esquerda pra direita, quando SINGLE saindo da direita para esquerda.

A ambiguidade associada à filtragem tipo BOTH é amplificada em um relacionamento muitos para muitos, pois vários caminhos existirão entre tabelas diferentes (não é o caso do nosso exemplo, ok? Porque no nosso exemplo só existem 2 tabelas, mas se houvessem mais tabelas, pensa assim, mais uma ou duas tabelas ligadas na Sales e mais outras duas tabelas ligadas na Orders, você ia perceber que fica difícil para o Power BI identificar o caminho a percorrer num filtro, ou quando tentar aplicar um contexto).

Se você criar uma medida, cálculo ou filtro, consequências indesejadas podem ocorrer onde seus dados estão sendo filtrados e, dependendo da relação que o mecanismo do Power BI escolhe ao aplicar o filtro, o resultado final pode ser diferente. Essa situação também é verdadeira para relacionamentos bidirecionais (BOTH) e por isso você deve ter cuidado ao usá-los.

Por esse motivo, relacionamentos muitos para muitos e / ou relacionamentos bidirecionais são complicados. A menos que você tenha certeza de como seus dados são agregados, esses tipos de relacionamentos abertos com várias direções de filtragem podem apresentar vários caminhos pelos dados.

Via de regra, a boa prática diz que, sempre busque relacionamentos 1:N, porém, se não for possível, crie relacionamentos N:N com filtragem do tipo SINGLE direction.

Criando Many to Many Relationships

Antes de mais nada, vamos dar uma olhada na tela da criação do relacionamento:

Veja que as duas tabelas tem a coluna de ESTADO, que é a coluna que estou usando pra relacionar, mas em ambas as tabelas as colunas se repetem várias vezes. Nesse caso, do jeito que os dados estão apresentados, só dá pra criar mesmo um relacionamento N:N.

Veja que o “Cross Filter Direction” tem três opções: single de geo pra vendas, single de vendas pra geo e both.

Single de Geo filters Vendas significa que os filtros podem sair de Geo e filtrar Vendas, mas não o inverso, ou seja, se vc criar um gráfico de vendas e fizer um filtro de Geo, então o que vc escolher de Geo vai filtrar vendas, mas o inverso não funciona, ou seja, se você fizer um gráfico de Geo e criar um filtro de Vendas e tentar filtrar dessa forma, não vai funcionar.

Single de Vendas filters Geo, é o contrário do que escrevi no parágrafo acima.

Uma grande vantagem do Power BI implementar relacionamentos N:N é de que não precisamos criar “bridge tables” ou “factless fact table”. Antes do N:N existir era preciso criar uma tabela ponte entre as duas tabelas, de forma que seu relacionamento seria N:1 1:N. Tipo assim:

Utilizando Many to Many Relationships

Pra entender o uso e resultados esperados possíveis de um relacionamento N:N vamos começar antes olhando o resultado de um relacionamento comum 1:N.

Exemplo 1

Se temos um relacionamento 1:N, como o relacionamento abaixo:

E usamos esse relacionamento pra construir algum visual, vamos criar um visual de tabela pra ficar mais claro. Vamos criar uma tabela com Produto e Preço da tabela de Produto e Quantidade da tabela de Vendas.

• O que acontece é que o Power BI insere uma linha em branco para quantidade 100, que refere-se ao PRODUTO C, isso porque o PRODUTO C não existe na tabela de produto, mas existe na tabela de VENDAS, e dessa forma o Power BI tenta garantir integridade referencial dos dados (referential integrity, leiam sobre isso).
• Essa linhas em branco também apareceria no caso de existir algum registro na tabela de PRODUTO cuja chave de conexão entre as tabelas (foreign key) fosse null.

Esse problema seria facilmente resolvido adicionando o PRODUTO C na tabela de PRODUTO, e foi isso que eu fiz. O resultado é óbvio:

Ok, então você viu e já sabe o que esperar de um relacionamento 1:N, mas e um relacionamento N:N? Pra responder essa resposta, vamos mudar o contexto do exemplo.

Exemplo 2

Imagina que você tem uma tabela VENDAS que mostra vendas por estado, e uma outra tabela GEO que mostra uma relação de cidades por estado e sua população. Perceba que nesse caso você tem um relacionamento N:N se relacionar as duas tabelas pela coluna de ESTADO, uma vez que ESTADO se repete em ambas tabelas.

Se você observer os dados que temos uma pergunta comum seria “qual total de vendas que tenho por estado e qual é o total de população em cada estado?”… O problema é que não existe uma coluna de estado única…

Cross Filter SINGLE: GEO filters VENDAS

Vamos fazer uns testes com essas tabelas usando direção SINGLE de Geo para Vendas, o resultado seria o seguinte:

• Na tabela da esquerda de baixo, estamos usando ESTADO da tabela de VENDAS, então o valor de VENDAS é repedido pra cada cidade de acordo com a soma total do ESTADO de VENDAS (porque nesse modelo nosso é impossível pro power bi dizer qual valor de venda por cidade, porque não existe*), e o valor da POPULACAO é repetido pra cada estado de acordo com o total geral da tabela GEO, porém na linha de CIDADE ele é o valor certo porque o campo CIDADE é da tabela GEO.
• Na segunda tabela, a da direita embaixo, estamos usando ESTADO da tabela de GEO, então o valor de VENDAS é repetido novamente (porque nesse modelo nosso é impossível pro power bi dizer qual valor de venda por cidade, porque não existe*), e o valor da POPULACAO está correto tanto para ESTADO quando para CIDADE porque usa como referência a tabela de GEO, mas perceba que temos registros de SP sem informação de vendas, porque esse estado não existe na tabela de VENDAS.

Cross Filter SINGLE: VENDAS filters GEO

Perceba que agora mudamos o sentido da direção do relacionamento. Agora estamos usando VENDAS filters GEO. Nesse caso o resultado é:

• Na tabela da esquerda de baixo, estamos usando ESTADO da tabela de VENDAS, então o valor de VENDAS está adequado (lembrando que é impossível nesse modelo de dados discernir vendas por CIDADE, então o valor de vendas vai mesmo se repetir*), e o valor POPULACAO melhorou um pouco, porque agora ele consegue mostrar corretamente a quantidade certa por estado e cidade mesmo com a coluna de ESTADO sendo da tabela de VENDAS, mas o valor total é 23,50 e se você somar vai ver que é um resultado errado, isso porque o resultado da soma da POPULACAO está considerando SP, porém SP não é exibido na matrix por não existir na tabela de VENDAS.
• Na segunda tabela, a da direita embaixo, estamos usando ESTADO da tabela de GEO, o valor de VENDAS está incorreto, mostra o valor total se replicando em todos registros, isso porque o filtro do relacionamento não passa de GEO para VENDAS. Perceba que SP agora recebeu um valor de 5500, diferente do comportamento do exemplo anterior. O valor da POPULACAO ficou correto porque POPULACAO é um campo já da tabela de GEO.

Cross Filter BOTH

Perceba que agora mudamos o sentido da direção do relacionamento. Agora estamos usando BOTH. Nesse caso o resultado é:

• Na tabela da esquerda de baixo, estamos usando ESTADO da tabela de VENDAS, então o valor de VENDAS está correto, afinal VENDAS pertence à tabela VENDAS (lembrando que é impossível nesse modelo de dados discernir vendas por CIDADE, então o valor de vendas vai mesmo se repetir*), e o valor da POPULACAO também está correto considerando ESTADOS e CIDADES porque há um filtro BOTH, as duas tabelas se filtram, porém, perceba que o valor total ainda considera SP, mesmo ele não estando presente na tabela.
• Na segunda tabela, a da direita embaixo, estamos usando ESTADO da tabela de GEO, o valor de VENDAS está correto e o valor POPULACAO também, porém perceba que SP não tem nenhuma venda associada, porque ele não existe na tabela VENDAS.

Sobre Valores de VENDAS se repetindo nas CIDADES

Como expliquei, não existe relacionamento sobre CIDADE nesse modelo de dados e, portanto, é impossível informar qual valor cada CIDADE representa. Porém, é possível criarmos uma MEDIDA pra exibir o valor de VENDAS de forma que o valor seja agregado apenas a nível de ESTADO. O resultado então seria valores quando mostrando ESTADO e em branco quando tratando CIDADES.

A measure a ser criada deve considerar a função DAX ISFILTERED. Exemplo:

VENDAS MEASURE = IF( NOT ISFILTERED(GEO[CIDADE]), SUM(VENDAS[VENDAS]))

Resumindo o Exemplo 2

Você só pode filtrar e agrupar os dados de acordo com a tabela que está filtrando, então no caso de VENDAS filters GEO, é VENDAS, e no caso de GEO filter VENDAS, é GEO.

Pontos de atenção sobre Relacionamentos N:N

As principais diferenças entre relacionamentos 1:N e N:N, são:

1. Os valores mostrados em relacionamentos N:N não incluem uma linha em branco que representa as linhas incompatíveis na outra tabela (ou seja, não aplica integridade referencial no seu modelo de dados). Além disso, os valores não consideram as linhas em que a coluna usada no relacionamento na outra tabela é nula (isso porque em relacionamentos N:N os dados são unidos como em um INNER JOIN).
2. Você não pode usar a função RELATED() em relacionamentos N:N, porque mais de uma linha pode estar relacionada.
3. Usar a função ALL() em uma tabela não remove filtros que são aplicados a outras tabelas relacionadas por um relacionamento muitos para muitos.

Sobre o ponto 3 acima: Usando o que mostrei acima no exemplo 2, se eu criar uma measure assim “VENDAS TOTAL = CALCULATE( SUM(VENDAS[VENDAS]), ALL(VENDAS))” o que uma measure assim faz é trazer o valor total das vendas da tabela VENDAS, então no caso seria o valor de 5500 repetido pra todas colunas. Porém, no caso de um relacionamento N:N isso não acontece quando estamos usando informações de outras tabelas relacionadas.

Pra ser mais claro, veja a imagem abaixo.

• Na primeira tabela estou usando ESTADO da tabela GEO, então perceba que a medida não funciona!
• Na segunda tabela estou usando ESTADO da tabela VENDAS, nesse caso como tá tudo em casa (o “eixo” estado e as vendas estão na mesma tabela de VENDAS) então a medida funciona. Porém, se liga no resultado da POPULACAO entre as duas tabelas.

Ou seja, muito cuidado quando você quiser calcular um %Geral de Vendas por Estado usando relacionamentos N:N.

Boas práticas

São várias as boas práticas a serem aplicadas quando estamos falando de modelagem de relacionamentos N:N, recomendo esses links:
https://docs.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many
https://www.sqlbi.com/articles/filtering-tables/
https://www.sqlbi.com/tv/different-types-of-many-to-many-relationships-in-power-bi/

Outra forma de lidar com um cenário de many to many relationships:
https://www.youtube.com/watch?v=ZrANsDNnZug – Looking at Power BI Many to Many

A Ruth explica muito bem como resolver problemas de modelagem de many to many nesse vídeo:
https://www.youtube.com/watch?v=NdrrjkvH2zo – Is MANY TO MANY, too many? | Power BI many to many relationships explained

Conclusão

Só utilize N:N quando você souber exatamente o que você está fazendo e quais são os resultados corretos, especialmente se for um cross filter tipo BOTH. Se não sabe exatamente o que você está fazendo com N:N refaça seu modelo para um Star-Schema, os links e vídeos na parte de “boas práticas” explicam várias maneiras de se fazer isso.

Referências

https://docs.microsoft.com/en-us/learn/modules/design-model-power-bi/6-relationships-cardinality
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships
https://docs.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many