Relationships vs Matrix data

[ReneeKoelewyn]

Hi everyone,

I am still quite new to using Power BI and I’m facing an issue with comparing data between two categories across multiple datasets. I’ve been using two sheets to compare these categories for over two years. While I managed to resolve one comparison, I’m having trouble with the second. The data for the second comparison isn’t pulling through correctly.

I also tried creating a new relationship for the second comparison, but I’m encountering issues and it won’t allow me to proceed. 

This is the relationship (many to many) that works

The above is used to compare the data in the Matrix below

Now the problem is to compare another category and display it in the same way for the following:

I get the following error and it is not allowing me to select any other relationship. 

The first columns (for 2023) data pulls through correctly, but not the second (for 2024)

I am not even sure if I should create relationships for each category to create a Matrix for comparison. 

Any advice or suggestions would be greatly appreciated!

[Uzi2019 · Accepted solution]

Hi @ReneeKoelewyn 

You should always avoid creating many to many relationship. this can be done by using bridge table.

check the video for better understanding

https://www.youtube.com/watch?v=LRzpJTliW8Q

https://www.youtube.com/watch?v=1L66tcLaRQ0

https://www.youtube.com/watch?v=aPLaun-tTgQ

and their should be only single active relationship between 2 tables.

You dont need to create relationship for each comparison. it wont solve your purpose.

I hope I answered your question!