Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
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.
Proud to be a Super User!
Paul on Linkedin.
I did not really understand your answer, I'm sorry 😕
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-sin...
https://www.sqlbi.com/articles/start-learning-data-modeling-for-free/
https://exceleratorbi.com.au/create-lookup-table-power-pivot/
Proud to be a Super User!
Paul on Linkedin.
You have to make the relationships from Sellers to Loan, Sales and Orders and from Operations to Loan, Sales, Orders.
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
Sure they can be all active. They should 😉
Which problems did PBI tell you?
I had this message (sorry it is in french on the screenshot) : you cannot create an active relationship between Sellers and Rooms, as this could introduce ambiguity between the Sales and Loan tables. To make this relationship active, first deactivate or delete one of the relationships between Sales and Loan.
Why would you make them bi-directional?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |