Forum Discussion
Helper IISeveral relationships between tables but filtering does not work
I am a newbie on Power BI and I am facing some difficulties about creating a good modelisation for my different tables.
To explain a little bit my data, I have operations which can be made of (sales, orders and loans) (we can have many sales, many orders, many loans for one operation) and I have different informations about sellers which made the operations. I would like to have a model which allows me to do two things :
1. Filter sales, loans and orders by the operation_id.
2. Filter sales, loans and orders by the seller_id.
However, with my current modelisation, the first point works but when I want to filter by seller_id only the table of Loans is filtered because it is the one directly related. I tried to ceate non-active relationship too but it still does not work. Do you have any idea of how I could correct this setback ?
Thanks in advance for your help and you time.
Here is an example of my current data modelisation :
8 Replies
lukiz84Memorable Member
You have to make the relationships from Sellers to Loan, Sales and Orders and from Operations to Loan, Sales, Orders.
- maxduff59
But they cannot all be active relationships no ? Because I tried something similar than that but PBI told me that it cannot be active relationship because it would create some problems with my tables
- lukiz84
Sure they can be all active. They should 😉
Which problems did PBI tell you?
PaulDBrownCommunity Champion
You need to create the model with dimension tables for fields common to different tables with unique values and set up one-to-many, single direction relationships.
- maxduff59
I did not really understand your answer, I'm sorry 😕
- PaulDBrown
It is highly recommended to avoid many-to-many relationships in a Power BI model, since they can lead to unexpected results and inevitable headaches. You can avoid them by creating dimension tables containing a field of unique values which are common to the fact tables (for example you can create a dimension table for Seller ID since it is common to Loans, Sales and Orders).
Check out these resources:
https://radacad.com/basics-of-modeling-in-power-bi-what-is-a-dimension-table-and-why-say-no-to-a-single-big-tablehttps://www.sqlbi.com/articles/start-learning-data-modeling-for-free/
https://exceleratorbi.com.au/create-lookup-table-power-pivot/