Resolve many-to-many relationships

Many-to-many relationships are not recommended. As mentioned earlier in this chapter, the lack of a column with unique values can lead to performance issues and possible confusion about which column to filter on.

For instance, the following diagram shows a many-to-many relationship between my Manager table and my Sales table. I track sales by region, not manager, so I have a region on my Sales table:

Figure 5.12 – Many-to-many relationships are slow and bad

I can easily fix this by creating a new "bridge" table named Region of the unique region values from my Manager table. I can do this in Power Query or DAX, with one query.

I can then delete the many-to-many relationship and create two new relationships: a many-to-one from the Sales table to the new Region table and a one-to-many from the Region table to the Manager table.

Figure 5.13 – Creating a bridge table solves...