Power BI: Understanding Many-to-Many Relationships, Bi-Directional Filtering, Ambiguous Paths

Hi maverickf17 

The first important distinction is that a Power BI relationship is not the same as a physical SQL join.

## 1. Relationship versus SQL JOIN

If the two tables are physically joined by `Movie Title`, the two Inception award rows are matched with the two Inception revenue rows:

* 2 award rows
* 2 revenue rows
* 2 × 2 = 4 joined rows

In that physically joined table, summing Revenue would return 500 for Inception.

A Power BI model relationship does not normally materialize this combined rowset. It defines how filter context propagates between tables. Each measure is evaluated against its original fact table and at that table’s grain. Microsoft describes model relationships as filter-propagation paths between tables.

Therefore, with a proper Movie dimension:

```DAX
Revenue =
SUM ( 'Movie Revenue'[Revenue] )
```

Inception returns:

```text
150 + 100 = 250
```

The relationship itself does not convert that result to 500.

However, Revenue can appear repeatedly when it is displayed by Festival. For example, Inception’s 250 could appear once under BAFTAs and again under Oscars. Adding those visible rows manually would produce 500, but this is repeated attribution across a non-additive grouping—not physical duplication of the revenue rows. The total is evaluated again in its own filter context rather than being calculated by adding the displayed rows.

---

## 2. Table grain

The grain should be defined before creating relationships:

### Movie Revenue

One row per:

```text
Movie × Region
```

### Movie Awards

One row per:

```text
Movie × Festival
```

The two tables are separate fact tables at different grains. They should not be directly related to each other.

A table containing both `Movie Title` and `Region` is not a Movie dimension because Movie Title is repeated. It is either:

* a bridge or association table at Movie–Region grain, or
* a duplication of information already stored in the Revenue fact.

In this example, it is generally unnecessary.

---

## 3. Recommended star-schema model

I would model this as a fact constellation: two fact tables sharing conformed dimensions.

```text
Dim Movie
---------
Movie Key
Movie Title
Other movie attributes

Dim Region
----------
Region Key
Region

Dim Festival
------------
Festival Key
Festival

Fact Movie Revenue
------------------
Movie Key
Region Key
Revenue

Fact Movie Awards
-----------------
Movie Key
Festival Key
Award Count
```

Relationships:

```text
Dim Movie 1 ────> * Fact Movie Revenue
Dim Region 1 ────> * Fact Movie Revenue

Dim Movie 1 ────> * Fact Movie Awards
Dim Festival 1 ───> * Fact Movie Awards
```

All relationships should normally be:

* one-to-many;
* active;
* single direction;
* filtering from the dimension to the fact.

Microsoft generally recommends dimensions for filtering and grouping, facts for summarization, and consistent fact-table grain. It also recommends avoiding direct many-to-many relationships between fact tables when a shared-dimension design is possible.

---

## 4. When many-to-many becomes a problem

Many-to-many is not automatically incorrect. It becomes problematic when the model does not define how values should be attributed.

Typical problems include:

* directly relating two fact tables;
* using a non-unique column as though it were a dimension key;
* summing an additive measure across overlapping groups;
* using a bridge with duplicated associations;
* enabling Both filtering through several tables;
* allowing more than one active route between the same tables.

A legitimate many-to-many example would be Movies and Actors:

```text
Dim Movie 1 ───> * Bridge Movie Actor * <─── 1 Dim Actor
```

Each movie can have many actors and each actor can appear in many movies. That is a genuine entity-to-entity many-to-many relationship.

Revenue and Awards are different. They are two facts that share Movie, so a common Movie dimension is the appropriate design.

---

## 5. When to use Both cross-filtering

Both should be an exception, not the default.

It can be useful for:

* a genuine many-to-many dimension bridge;
* dimension-to-dimension analysis through a fact table;
* occasionally limiting slicer values to combinations that have data.

Microsoft recommends minimizing bi-directional relationships because they can affect performance and produce confusing filter behavior. For dimension-to-dimension calculations, Microsoft specifically suggests applying `CROSSFILTER` inside the required measure rather than changing the entire model relationship to Both.

---

## 6. Ambiguous filter paths

An ambiguous path exists when a filter can travel from one table to another through more than one active route.

For example, assume the model contains:

```text
Region → Movie Revenue
Region → Movie–Region Bridge
Movie Dim → Movie Revenue
Movie Dim ↔ Movie–Region Bridge
Movie Dim → Movie Awards
```

If the required relationships are configured as Both, Region could reach Movie Awards through the bridge:

```text
Region
→ Movie–Region Bridge
→ Movie Dim
→ Movie Awards
```

It might also reach Movie Dim through Movie Revenue:

```text
Region
→ Movie Revenue
→ Movie Dim
→ Movie Awards
```

That creates two possible filter paths.

The exact paths in the attached model depend on the relationship arrow directions. A line between two tables does not automatically mean that the filter can travel in both directions. In Model view:

* one arrow indicates single-direction filtering;
* two arrows indicate Both.

Power BI may reject a relationship change that creates ambiguity. In some models it can resolve multiple paths according to relationship priority and weight, but relying on that behavior makes the model difficult to understand and maintain.

With the recommended single-direction model, the normal paths are:

```text
Region → Movie Revenue
Movie → Movie Revenue
Movie → Movie Awards
Festival → Movie Awards
```

There is intentionally no automatic path from Region to Movie Awards.

---

## 7. DAX validation

Base measures:

```DAX
Revenue =
SUM ( 'Movie Revenue'[Revenue] )
```

```DAX
Awards =
SUM ( 'Movie Awards'[Award Count] )
```

Validation measure for Inception:

```DAX
Inception Revenue Check =
CALCULATE (
[Revenue],
'Dim Movie'[Movie Title] = "Inception"
)
```

Expected results without filters:

```text
Revenue = 630
Inception Revenue = 250
Awards = 23
```

A table containing `Dim Movie[Movie Title]`, `[Revenue]`, and `[Awards]` should return:

| Movie | Revenue | Awards |
| ---------- | ------: | -----: |
| Avatar | 180 | blank |
| Inception | 250 | 7 |
| The Matrix | blank | 5 |
| Titanic | 200 | 11 |
| Total | 630 | 23 |

A DAX query can also be used for validation:

```DAX
EVALUATE
SUMMARIZECOLUMNS (
'Dim Movie'[Movie Title],
"Revenue", [Revenue],
"Awards", [Awards]
)
```

---

## 8. Filtering Awards by Region

There is an important business-rule decision here.

Without special logic, a Region slicer should filter Revenue only. Region is an attribute of the Revenue fact, not of the Award fact.

If the requirement is:

> Show awards for movies that generated revenue in the selected region

then Region must first identify movies in Movie Revenue, and those movies must then filter Movie Awards.

Instead of permanently setting the Movie–Revenue relationship to Both, this can be limited to one measure:

```DAX
Awards for Selected Region =
VAR RegionIsRestricted =
COUNTROWS ( VALUES ( 'Dim Region'[Region] ) )
< COUNTROWS ( ALL ( 'Dim Region'[Region] ) )
RETURN
IF (
NOT RegionIsRestricted,
[Awards],
CALCULATE (
[Awards],
CROSSFILTER (
'Dim Movie'[Movie Key],
'Movie Revenue'[Movie Key],
BOTH
)
)
)
```

Inside this measure only, the filter path becomes:

```text
Region
→ Movie Revenue
→ Movie Dim
→ Movie Awards
```

The permanent model remains single-directional.

Expected results:

| Region selection | Movies with revenue | Revenue | Related awards |
| ---------------- | ------------------- | ------: | -------------: |
| No region filter | All movies | 630 | 23 |
| International | Avatar, Inception | 330 | 7 |
| North America | Inception, Titanic | 300 | 18 |

Therefore, `Correct Awards = 23` is correct only for the unfiltered model.

When `International` is selected:

```text
Inception awards = 3 + 4 = 7
Avatar awards = 0
Result = 7
```

If Awards must remain 23 after selecting a Region, then Region should not filter Awards and the normal `[Awards]` measure should be used.

---

## 9. Snowflake alternative

A snowflake model would normalize attributes into additional dimensions, for example:

```text
Dim Continent → Dim Region → Fact Movie Revenue

Dim Studio → Dim Movie → Fact Movie Revenue
→ Fact Movie Awards

Dim Festival Group → Dim Festival → Fact Movie Awards
```

The fact-table grain and measures remain unchanged.

However, there is no useful snowflake requirement in the sample data. Creating additional tables only to demonstrate a snowflake schema would add complexity without solving a business problem.

For most Power BI semantic models, the denormalized star design is preferable because it provides:

* fewer relationships;
* shorter filter paths;
* a simpler field list;
* easier DAX;
* easier model maintenance.

A snowflake can be justified when normalized dimensions are centrally governed, reused across systems, or contain meaningful reusable hierarchies. Microsoft generally recommends consolidating snowflaked dimension attributes into a single model dimension when practical.

## Conclusion

For this example:

1. Do not physically merge Revenue and Awards.
2. Do not directly relate the two fact tables.
3. Create one unique Movie dimension.
4. Connect Movie to both facts using one-to-many, single-direction relationships.
5. Connect Region only to Revenue.
6. Use a measure-level `CROSSFILTER` or `TREATAS` when Region must affect Awards.
7. Avoid permanent Both filtering unless a genuine bridge-table requirement exists.
8. Validate every result according to the grain and filter context, rather than by adding the visible rows of a visual.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Hi maverickf17 

Please ensure to understand the fundamentals with respect to Data Modelling concepts where you will be more comfortable to work on

1. Relationship vs. physical SQL JOIN

SQL JOIN physically merges rows, so joining on Movie Title directly gives Inception 2×2=4 rows and make the revenue to 500. Power BI relationship does not merge rows where it links tables through filter propagation. When you select something in one table, Power BI filters related table's rows and aggregates each table's measure. 

2. When many to many becomes a problem

When two tables share a key but neither side is unique, cardinality is ambiguous and filter propagation can behave inconsistentlly with bi-directional filtering enabled. Power BI has to guess a path and results will double the count.

3. When to use bi-directional (Both) filtering

Use it only when you need a "downstream" table like Region to filter an "upstream" table like Movie Awards through a bridge and there is no ambiguity risk in bridge table many to many patterns which is main cause of ambiguous paths

4. How bi-directional filtering creates ambiguous paths

If two or more filter paths exist between two tables like one through Movie dim, another through Movie to Region, Power BI can not determine which path should carry the filter propagation, so it either blocks the relationship of inactive one or throws a multiple relationships path error

5. Filter paths in your model

Region - Movie - Movie dim - Movie Awards

Region - Movie Revenue - Movie dim - Movie Awards

If both paths are active and bi-directional, this is an ambiguous path scenario. Power BI needs exactly one active path between any two tables at a time.

6. Validating totals with DAX

Total Revenue = SUM(MovieRevenue[Revenue]) // should return 630

Inception Revenue = CALCULATE([Total Revenue], MovieDim[Movie Title]="Inception") // should return 250

Total Awards = SUM(MovieAwards[Award Count]) // should return 23

If Inception Revenue returns 500 or Total Awards double counts, it is a direct many-to-many join rather than a dim based star schema connected.

7. Star Schema design

One central Movie dim(Region) with two fact tables - Movie Revenue and Movie Awards - each joined many to one directly to Movie dim. No fact to fact relationship where both fact tables relate only through the dimension table. Try creating bridge table of required which act as a dimension table 

8. Snowflake Schema design

Same as star, but Region is normalized into its own dimension table (Region) then Movie dim - Region dim and Movie Revenue - Region dim separately if revenue is at Region grain adding a layer of normalization matching your attached model

9. Star vs. Snowflake - when to choose which

Star is generally preferred in Power BI for simplicity and performance (fewer joins, faster DAX, easier for business users). Snowflake is useful when dimension attributes are large, reused across many facts at the cost of complex relationships and ambiguous paths which you are experiencing.

10. Filtering Movie Awards by Region = International without duplication/ambiguity

Selecting Region should filter Movie - Movie dim - Movie Awards, using single direction filtering Region - Movie - Movie dim - Movie Awards instead bi-directional. Since Movie dim is the unique table, awards are not duplicated. Only the related movies with matching Region in the Movie bridge table pass through and CALCULATE with a measure on Movie Awards which respects filtered movie list without bi-directional relationships on the awards

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-bidirectional-filtering

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships

Hi maverickf17 — you've actually already found the answer in your own write-up. All ten questions collapse into one principle, and your "500 instead of 250" is the tell.

The root cause: you're relating two fact tables (Movie Awards and Movie Revenue) on Movie Title. That's a fact-to-fact join, and it produces a fan trap -- Inception's 2 award rows x 2 revenue rows = 4 rows, so revenue double-counts to 500. A Power BI relationship differs from a SQL JOIN here in one crucial way: SQL materialises the joined rows and any measure you write sums over that inflated grain. Power BI, on a proper model, propagates a filter and each measure aggregates over its own table's native grain -- so Revenue stays 250 and Awards stays independent, no multiplication. The relationship filters; it doesn't flatten.

The fix is the star schema, and it dissolves the rest:

1. Both facts hang off a shared Movie dimension (one row per movie), single-direction relationships Dimension -> Fact. Movie Awards and Movie Revenue never touch each other directly.
2. Now there's no ambiguous path, because there's only one path from any dimension to each fact. Region filters Movie Revenue directly; to filter Movie Awards by Region you'd need Region on a shared dimension too, not bi-directional filtering.
3. Many-to-many and bi-directional (Both) become things you reach for only when you can't build that clean dimension -- a genuine M2M like Movie-to-Genre needs a bridge table, and THAT is the legitimate use. Using Both to paper over a fact-to-fact model is what manufactures the ambiguous paths you're worried about.

On star vs snowflake: star (flattened dimensions) is the default for Power BI -- VertiPaq compresses the redundancy away and the engine is tuned for it. Snowflake (normalised dimensions) only earns its keep when a sub-dimension is huge or genuinely reused across facts. For your model, star.

Validating in DAX: put the raw and correct numbers side by side. Keep Revenue = SUM ( 'Movie Revenue'[Revenue] ) as the correct one, and build the broken version deliberately once (iterate the awards table and pull revenue across) so you can watch it hit 500 -- seeing the multiplication beats trusting it.

Net: don't relate fact to fact. One shared dimension per shared key, single-direction, measures aggregate independently. Do that and questions 1-10 answer themselves -- the many-to-many and bi-directional filtering you're studying become the exceptions you rarely need, not the tools you reach for first.