Star Schema with bridge tables - I cant figure it out

[dirk74]

I'm trying to build a star schema with a fact table at the center and dimensions around it. The fact table represents a person in our CRM, where each row corresponds to one person. Here's the challenge:

• A person can have one or multiple functions.

• A person can also have one or multiple pairs of positions and areas.

To handle these many-to-many relationships, I’m using bridge (or linking) tables to connect the fact table with the relevant dimensions. This approach is even recommended by Microsoft on their Learn pages.

So far, using one bridge table in the model works perfectly. However, as soon as I introduce a second bridge table, the data model breaks, and I get the error: "Can't determine relationships between the fields."

I’ve triple-checked my data:

• There are no NULL values anywhere.

• All keys are correct and match between tables.

• Running the same logic as a SQL query gives the correct results.

My question is:

• Are multiple bridge tables even allowed in Power BI?

• Is anyone successfully using more than one bridge table in their model?

• If not, how did you solve similar many-to-many relationship issues?

Any guidance or examples would be greatly appreciated!

[dirk74]

Thank you very much @DataNinja777 
I guess I will use just one bridge table.

[DataNinja777 · Accepted solution]

Hi @dirk74 ,

Using multiple bridge tables in Power BI is allowed, but it requires careful design to avoid relationship ambiguities. The error you are encountering—"Can't determine relationships between the fields"—occurs when Power BI is unable to decide how to filter or aggregate data due to multiple paths between tables. This typically happens when both bridge tables connect to the same fact table and overlapping dimension tables, creating ambiguity.

To resolve this, it is essential to ensure that relationships are unambiguous and well-structured. Relationships between the fact table and bridge tables should be one-to-many, flowing from the fact table to the bridge tables. Similarly, relationships between the bridge tables and dimension tables should be many-to-one, flowing from the bridge tables to the dimensions. These relationships should ideally remain single-directional unless bi-directional filtering is absolutely necessary.

In cases where the model becomes too ambiguous, inactive relationships can be used to simplify the model. By keeping only one relationship active and marking others as inactive, you can resolve the ambiguity. When an inactive relationship needs to be used, it can be explicitly activated within a DAX measure using the USERELATIONSHIP function. For example:

MeasureWithInactiveRelationship =
CALCULATE(
    SUM(FactTable[Value]),
    USERELATIONSHIP(BridgeTable[DimensionKey], DimensionTable[DimensionKey])
)

Another option to simplify the model is to consolidate the bridge tables into one, particularly if the attributes are conceptually related. For example, you could create a single bridge table with columns for the PersonKey, AttributeType, and AttributeValue:

PersonKey	AttributeType	AttributeValue
1	Function	Sales
1	PositionAndArea	Manager - West
2	Function	Marketing
2	PositionAndArea	Analyst - East

This combined bridge table can then be connected to multiple dimension tables by filtering based on the AttributeType column.

If ambiguity persists even after these adjustments, DAX measures can be used to explicitly filter and aggregate the data. By removing ambiguous relationships and leveraging DAX, you can calculate values dynamically. For instance, the TREATAS function can help you apply filtering directly:

FilteredValue =
CALCULATE(
    SUM(FactTable[Measure]),
    TREATAS(
        VALUES(BridgeTable[DimensionKey]),
        DimensionTable[DimensionKey]
    )
)

In scenarios where combining bridge tables or DAX filtering is not feasible, flattening the data during preprocessing is another approach. This involves concatenating related attributes into a single column in the fact table, reducing the need for complex relationships.

Ultimately, multiple bridge tables are supported in Power BI, but their usage must be carefully managed to avoid ambiguous paths. Testing relationships and filtering behavior with sample measures can help validate the accuracy of your data model. If you need further assistance with your specific structure, feel free to provide more details for tailored guidance.

Best regards,