Relationships in Power BI: One to Many and Many to Many
A relationship is the line that connects two tables on a matching key. One-to-many means one store row can match many sales rows. Many-to-many means neither column is unique, and totals can double if you are not careful.
Friends! A City slicer that changes nothing is usually a missing line, not a broken measure. Why? Filters travel along relationships. How? We relate FreshBasket Sales to Store on Store ID, one store to many sales, and Mumbai stays 150. Slow and clear.
मित्रांनो! City स्लायसर काही बदलत नसेल तर बहुतेक वेळा relationship ची रेषा नाही, measure बिघडलेला नाही. का? फिल्टर relationships वरून जातात. कसे? FreshBasket Sales ला Store शी Store ID वर जोडायचे, एक store अनेक sales, आणि Mumbai 150 राहतो. हळू आणि स्पष्ट.
मित्रों! City स्लाइसर कुछ न बदले तो अक्सर relationship की लाइन नहीं है, measure खराब नहीं है. क्यों? फ़िल्टर relationships पर चलते हैं. कैसे? FreshBasket Sales को Store से Store ID पर जोड़ें, एक store कई sales, और Mumbai 150 रहता है. धीरे और साफ़.
Quick answer
Vertical path:
- Fact Sales holds Store ID plus Amount. Store holds Store ID plus City.
- In Model view, drag Sales[Store ID] onto Store[Store ID].
- The many side (*) sits on Sales. The one side (1) sits on Store.
- Keep cross-filter direction Single for this first line.
- A City slicer = Mumbai keeps 100 + 50 and drops Pune. Total 150.
- If both columns repeat, do not shrug and accept many-to-many. Make one side unique.
Store (1) ——< Sales (*)
City filter walks from Store into Sales
Real example: three sales, two stores
Sales (many rows):
| Order | Store ID | Amount |
|---|---|---|
| A | S1 | 100 |
| B | S1 | 50 |
| C | S2 | 40 |
Store (one row per store):
| Store ID | City | Channel |
|---|---|---|
| S1 | Mumbai | Online |
| S2 | Pune | Store |
What the relationship does:
- Store ID S1 is unique on Store. It appears twice on Sales. That is one-to-many.
- Slicer City = Mumbai keeps store S1.
- Sales rows A and B stay. Row C leaves.
- Number out: 150.
- Slicer City = Pune keeps S2 only. Number out: 40.
- No slicer: 190.
There is no City column on Sales. The line carries the filter. That is the whole point of the relationship.
What do I need before this guide?
- Fact and dimension tables so you know which table is the many side.
- Star schema for the picture of tables around a fact.
- Course: Relationships.
Before and after (look at the tables first)
Before - No relationship. No line. A slicer cannot travel.
आधी (Before) — relationship नाही. रेषा नाही. स्लायसर जाऊ शकत नाही.
पहले (Before) — relationship नहीं. लाइन नहीं. स्लाइसर चल नहीं सकता.
After - One store, many sales. One-to-many. Mumbai = 150
नंतर (After) — एक store, अनेक sales. One-to-many. Mumbai = 150
बाद में (After) — एक store, कई sales. One-to-many. Mumbai = 150
How to create the line
One store row (1) matches many sales rows (*). The City slicer walks from Store into Sales.
एक store row (1) अनेक sales rows (*) शी जुळतो. City स्लायसर Store मधून Sales मध्ये जातो.
एक store row (1) कई sales rows (*) से जुड़ता है. City स्लाइसर Store से Sales में जाता है.
- Open Model view.
- Confirm Store ID has the same data type on both tables (text with text, or number with number).
- Drag Sales[Store ID] onto Store[Store ID].
- Read the labels: * on Sales, 1 on Store.
- Double-click the line. Cardinality should be many-to-one. Cross-filter direction Single. Active ticked.
- Hide Store ID from report view. Authors should pick City, not the key.
- Put City on a slicer and [Total Sales] on a card. Mumbai must read 150.
If Power BI already drew a line because the names matched, still open it. Autodetect can pair the wrong columns.
Many-to-many: when the line is a warning
If City repeats on both Sales and Targets, totals can double. Put a unique City table in the middle.
City Sales आणि Targets दोन्हीवर पुनरावृत्ती होत असेल तर totals दुप्पट होऊ शकतात. मध्ये एक unique City टेबल ठेवा.
अगर City Sales और Targets दोनों पर दोहराए तो totals दोगुने हो सकते हैं. बीच में एक unique City टेबल रखें.
Imagine a Targets sheet with two Mumbai rows (Online target and Store target). City is not unique there. City is not unique on Sales either.
| City | Channel | Target |
|---|---|---|
| Mumbai | Online | 120 |
| Mumbai | Store | 80 |
| Pune | Online | 60 |
If you relate Sales[City] to Targets[City], both sides are many. A visual can repeat rows and a total can look bigger than 190. That is not “advanced modelling”. That is a grain problem.
- Build a small City table with one row per city: Mumbai, Pune.
- Relate Sales to City (many-to-one) and Targets to City (many-to-one).
- The City slicer then filters both tables through the one side.
- Mumbai sales stay 150. Mumbai targets stay 200 (120+80). They do not multiply each other.
Power BI can create a many-to-many relationship. Use it only when a teacher has shown the grain and you have checked the total. Beginners should fix the key instead.
A second date on Sales (order date and delivered date) is a different problem: two lines to the same Date table. Only one stays active. The dashed line is taught in USERELATIONSHIP. Do not delete it because it looks dotted.
What the filter actually walks
- The slicer filters the one side first (Store or City).
- The relationship keeps fact rows whose key still matches.
- The measure sums what remains.
- A filter does not jump to a table that has no path.
- A blank row in a slicer often means a Sales key with no Store match. Fix the key. Do not hide the blank and pretend the model is clean.
Mistakes and calm fixes
| Symptom | Likely cause | Fix |
|---|---|---|
| Slicer does nothing | No line, or the line is inactive | Create an active relationship on the key |
| Totals too big | Many-to-many or duplicate keys on the “one” side | Make the dimension key unique |
| “Cannot create relationship” | Types differ, or both columns repeat | Match types; fix uniqueness |
| Blank in the slicer | Fact key missing from the dimension | Clean keys or add the missing store |
Ravindra Bagale's Tip
Interview line: “I relate the fact to the dimension many-to-one on a unique key. If both sides repeat, I add a bridge dimension instead of trusting a many-to-many total.” Got it?
Ravindra Bagale's Tip – मराठी
Interview line: “मी fact ला dimension शी unique key वर many-to-one जोडतो. दोन्ही बाजू पुनरावृत्ती करत असतील तर many-to-many total वर विश्वास न ठेवता bridge dimension जोडतो.” समजलं का?
Ravindra Bagale's Tip – हिंदी
Interview line: “मैं fact को dimension से unique key पर many-to-one जोड़ता हूँ. अगर दोनों ओर दोहराव हो तो many-to-many total पर भरोसा किए बिना bridge dimension जोड़ता हूँ.” समझ में आया?
Practice task
- Load the three sales rows and the two store rows.
- Create the relationship on Store ID.
- Prove Mumbai 150 and Pune 40.
- Add a second Mumbai row on a Targets table and see why City cannot be the key on both sides.
- Add a City table with unique names and relate both facts to it.
Got it? A relationship is the line. One store, many sales. Mumbai stays 150 because the filter walks that line. Next: what the 1 and * labels mean, and which way the filter is allowed to walk. Let us go ahead.
समजलं का? Relationship म्हणजे ती रेषा. एक store, अनेक sales. Mumbai 150 राहतो कारण फिल्टर त्या रेषेवरून जातो. पुढे: 1 आणि * चे अर्थ, आणि फिल्टर कोणत्या बाजूने जाऊ शकतो. आता पुढे जाऊया.
समझ में आया? Relationship यानी वह लाइन. एक store, कई sales. Mumbai 150 रहता है क्योंकि फ़िल्टर उस लाइन पर चलता है. आगे: 1 और * का मतलब, और फ़िल्टर किस दिशा में जा सकता है. अब आगे बढ़ते हैं.
Frequently asked questions
What is a Power BI relationship?
A line between two tables on a matching key so a slicer on one table can filter the other.
What is one-to-many?
One row on the dimension (one store) matches many rows on the fact (many sales).
What is many-to-many?
Neither column is unique. Totals can repeat. Prefer a dimension where the key is unique.
Why does my slicer do nothing?
There is no active relationship, or the columns are different types.
Why is there a blank in the slicer?
A fact key has no match in the dimension. Fix the key. Do not hide the blank and ignore it.
Where is the inactive date line taught?
In the USERELATIONSHIP guide. A dashed line is a second path, not a broken one.