Many to Many Relationships

What happens when many to many relationship is established between two tables in Power BI? It warns about the possibility of getting unexpected results in visuals. But what exactly is going on? I came across this recently while I was building a tool for data model validation for myself.(not mature enough for general release yet)

Let us first take a step back and think about how values that are displayed in visuals get those values in context of data model and filter propagation and talk about a possible design of such a system( and if we assume that it is done similarly in Power BI) and see for ourselves why it is indeed problematic.

Relationships

They are essentially how a data modeller tells how which fields represent the same thing and how real world concepts are interconnected. Say, Region is connected to Sales via Customer or Class is connected to Exam Result via Student ( and also through Teacher). Also it specifies which is dependent on which, in otherwords the direction of filter propagation.

But where is the issue in this? It becomes clear when we try to implement it as joins.

Joins and Relationships

When joins are used to implement it there is a multiplicative effect.

To be continued...