A Power BI model is a set of tables connected by relationships, and almost every confusing number in a report traces back to how those relationships are shaped. Below, every CALCULATE result shown was run for real against the Excel Data Model, the same DAX engine, VertiPaq, that Power BI itself uses, over one small shared dataset: a Customers table, a Products table, a Category table, and a Sales fact table joining them.
In this guide
- Star schema and snowflake schema
- Which way a filter actually travels
- Filters crossing more than one hop
- Combining filters, and BLANK versus 0
- RELATED() across more than one hop
- Many-to-many relationships and bridge tables
- One active relationship per table pair
- The date table and hierarchies
- Surrogate keys, and why a measure cannot be a join key
- Common mistakes
- Cheat sheet
- Try it yourself
- FAQ
Star schema and snowflake schema
A fact table sits at the centre of the model: it holds the numeric measures and a foreign key to every dimension, and it is usually the fastest-growing table, since it rows up every transaction. The dimension tables sit around it holding descriptive context, like a customer’s city or a product’s name. Connecting dimensions directly to one fact table is a star schema; splitting a dimension further into its own related tables, such as Products relating out to a separate Category table, is a snowflake.
BI models generally prefer the flatter star shape over a heavily normalised, OLTP-style design. A reporting model is read-heavy, not write-heavy, so fewer joins and shorter relationship paths make filters propagate faster and keep the model easier for an analyst to navigate, trading a little redundancy for that simplicity.
Which way a filter actually travels
By default, a filter on a one-to-many relationship travels only from the one side toward the many side, never backward. With Customers (one) related to Sales (many):
| Measure | Filter applied | Result |
|---|---|---|
| CALCULATE(SUM(Sales[Amount]), Customers[City] = "Mumbai") | On the one side (Customers) | 600 – Mumbai covers two customers, Asha and Meera, and their orders 100, 200 and 300 add up |
| CALCULATE(COUNTROWS(Customers), Sales[Amount] > 100) | On the many side (Sales) | 3 – unchanged; a filter on the many side cannot flow back up to the one side |
This asymmetry is exactly why dimension tables are built to filter well and fact tables are not: Products[ProdName] = "Pen" correctly narrows Sales down to 2 matching rows, but CALCULATE(COUNTROWS(Category), Products[ProdName] = "Pen") still returns the full, unfiltered count of 2 categories, since Products is the many side relative to Category and the filter has nowhere to travel.
A relationship also has to actually exist for any of this to work. Adding an unrelated table, Targets, with no relationship drawn to anything else, and filtering it changes nothing: CALCULATE(SUM(Sales[Amount]), Targets[Zone] = "North") still returns 800, the full unfiltered total, because two tables merely sitting in the same model is not the same thing as two tables being related.
Filters crossing more than one hop
In the snowflake, Category relates to Products, and Products relates to Sales, two hops away. A filter placed on Category still reaches all the way down to Sales:
| Measure | Hops crossed | Result |
|---|---|---|
| CALCULATE(SUM(Sales[Amount]), Category[CatName] = "Stationery") | 2 (Category → Products → Sales) | 750 – every Pen and Pencil order, 100 + 200 + 150 + 300 |
| CALCULATE(COUNTROWS(Sales), Category[CatName] = "Paper Goods") | 2 | 1 – the Notebook was only ordered once |
| CALCULATE(COUNTROWS(Products), Category[CatName] = "Stationery") | 1 (Category → Products only) | 2 – Pen and Pencil both sit in Stationery |
Crossing two hops works the same way as crossing one, provided every hop in the chain is a one-to-many relationship travelled from its one side. The filter simply keeps propagating forward hop by hop.
Combining filters, and BLANK versus 0
Listing more than one filter argument inside CALCULATE combines them with AND: each dimension branch filters Sales independently, and only a row that survives every branch at once counts.
| Measure | Result |
|---|---|
| CALCULATE(SUM(Sales[Amount]), Category[CatName] = "Stationery", Customers[City] = "Mumbai") | 600 – only orders that are both Stationery and bought by a Mumbai customer: 100, 200 and 300 |
| CALCULATE(COUNTROWS(Sales), Category[CatName] = "Paper Goods", Customers[City] = "Mumbai") | BLANK – the one Paper Goods order was bought in Delhi, so zero rows satisfy both branches |
| CALCULATE(SUM(Sales[Amount]), Category[CatName] = "Paper Goods", Customers[City] = "Mumbai") | BLANK – summing over zero matching rows gives BLANK, not the number 0 |
BLANK is not the same thing as 0. Both COUNTROWS and SUM return BLANK, not 0, when a filter combination leaves zero rows to work with. A card visual showing BLANK rather than 0 is often the first clue that a filter combination has quietly ruled out every row.
Other aggregations behave the same way once rows do survive a filter: CALCULATE(AVERAGE(Sales[Amount]), Customers[City] = "Delhi") returns 100, since Ravi is the only Delhi customer and his two orders, 150 and 50, average to that; CALCULATE(DISTINCTCOUNT(Sales[CustID]), Category[CatName] = "Stationery") returns 3, counting a repeat customer only once; and CALCULATE(MAX(Sales[Amount]), Products[ProdName] = "Pencil") returns 300, the larger of Pencil’s two order amounts.
RELATED() across more than one hop
A calculated column on Sales, Cat = RELATED(Category[CatName]), can walk across more than one relationship in a single direction, from Sales through Products to Category, as long as every hop is a one-to-many relationship travelled from the many side:
| Order | What RELATED() resolves to | Why |
|---|---|---|
| Pen (ProdID 1) | Stationery | ProdID 1 points to CatID 1 in Products, which points to Stationery in Category |
| Notebook (ProdID 3) | Paper Goods | ProdID 3 points to CatID 2 in Products, which points to Paper Goods |
Many-to-many relationships and bridge tables
A direct many-to-many relationship, such as between Students and Courses, carries a real risk: with no unique side on either end, a value can be pulled into a total through more than one path, so totals can double-count unless the model is designed carefully. The standard fix is a bridge table, such as an Enrolments table holding one row per student-course pair, related to both tables as the many side. That turns one ambiguous many-to-many relationship into two clean one-to-many relationships instead.
One active relationship per table pair
At most one relationship between the same two tables can be active at a time. Power BI’s modelling view will not even allow a second active path between the same pair, because two simultaneous active paths would make it ambiguous which route a filter should follow.
A Sales table with both an OrderDate and a ShipDate, each relating to a Calendar table, is the classic example: OrderDate stays active for ordinary reports, while ShipDate stays inactive and is called in only when a specific measure wraps its calculation in USERELATIONSHIP(Sales[ShipDate], Calendar[Date]). That function only activates the inactive relationship for that one calculation, without changing which relationship is active anywhere else in the model. A single Calendar table reused this way for more than one logical role is called a role-playing dimension.
Bidirectional filtering
A relationship’s cross filter direction can be set to both, letting a filter on the many side also narrow the one side, but this is a deliberate, situational choice, most often needed around bridge tables, not a safe default to apply everywhere. Turning it on broadly risks ambiguous filter paths and can quietly slow the model down.
Two tables that merely share a column holding matching, or even identical, values are not related unless a relationship has actually been drawn between them: Power BI never infers one automatically from matching data alone.
The date table and hierarchies
Before time intelligence functions such as TOTALYTD can rely on a Calendar table, it needs one row for every single calendar day, a contiguous range covering the data, and a genuine Date-typed column marked as the table’s official date column. Time intelligence steps through real calendar days internally, so a gap or a non-contiguous range breaks it silently.
A hierarchy, whether a natural one already sitting as separate columns, like Year, Quarter, Month, Day, or an explicit one grouped manually in the Fields pane, is purely a navigation feature: it only changes how a visual lets someone drill up and down, and it does not alter the tables, relationships or any stored value underneath.
Surrogate keys, and why a measure cannot be a join key
A dimension often uses a small whole-number surrogate key, like DateKey, instead of the real date value, purely for efficiency: a compact integer generally joins and compresses more cheaply in the engine than a wider date, text, or multi-column key.
A relationship can only be built on a calculated column, never on a measure. A calculated column is computed once and physically stored on every row at refresh time, exactly like an ordinary column, so there is a concrete stored value for a relationship to match on. A measure only produces a value at query time, inside whatever filter context happens to be active, so there is nothing fixed sitting in a row for a relationship to anchor to.
Common mistakes
- Assuming a filter on the fact table reaches the dimension. By default it does not; propagation only runs from the one side to the many side.
- Reading BLANK as 0. A card showing BLANK usually means the filter combination matched zero rows, not that the real answer is zero.
- Assuming matching values create a relationship. Two columns holding identical values do nothing until a relationship is actually drawn.
- Turning on bidirectional filtering everywhere. It solves a specific, usually bridge-table-shaped problem; applying it broadly risks ambiguous paths and slower performance.
- Trying to relate on a measure. Only a stored column, including a calculated column, can be a relationship’s join key.
Cheat sheet
| Question | Answer |
|---|---|
| Which side of a relationship filters the other by default? | The one side filters the many side |
| Does a filter on the many side reach the one side? | No, not by default |
| What does summing zero matching rows return? | BLANK, not 0 |
| What fixes an ambiguous many-to-many relationship? | A bridge table |
| How many active relationships can join the same two tables? | One |
| What activates an inactive relationship for one measure? | USERELATIONSHIP |
| What is a Calendar table reused for two date roles called? | A role-playing dimension |
| What can a relationship be built on? | A stored column, including a calculated column, never a measure |
| What does a hierarchy change? | Only how a visual drills; nothing underneath |
Try it yourself
Quick check
Sales and Returns each hold a column that looks like a matching product code, but no relationship has been drawn between the two tables. If a report filters by a value in Sales, what happens to Returns?
Show answer
Returns is completely unaffected. Filter propagation only happens across a relationship that has actually been created in the model; two columns merely looking alike, or even holding identical values, does nothing on their own, which is a frequent source of confusing, apparently unfiltered numbers.
Frequently asked questions
What is the difference between a star schema and a snowflake schema in Power BI?
A star schema connects dimension tables directly to one central fact table. A snowflake schema splits at least one dimension further into related tables of its own, adding an extra hop for filters to travel.
Which way does a filter travel across a Power BI relationship?
By default, only from the one side of a one-to-many relationship toward the many side, never backward, which is exactly why dimension tables filter a report well and fact tables do not.
Why does a measure sometimes return BLANK instead of 0?
COUNTROWS and SUM both return BLANK, not 0, when the active filters leave zero matching rows to aggregate over. BLANK means nothing matched; 0 would mean something matched and summed to zero.
What is a bridge table in Power BI?
A table, such as Enrolments between Students and Courses, that turns one ambiguous many-to-many relationship into two clean one-to-many relationships, removing the risk of double-counted totals.
Can two Power BI relationships between the same two tables both be active?
No. At most one relationship between a given pair of tables can be active at a time; any additional relationship must stay inactive and be invoked explicitly with USERELATIONSHIP inside a measure.
Can a DAX measure be used as a relationship’s join key?
No. A relationship needs a stored column, such as an ordinary column or a calculated column, since a measure only produces a value at query time and has nothing fixed stored in a row to match against.
Test yourself
Timed questions on Data Modeling and Relationships, with an explanation for every answer.