A bridge table is an intermediate table in a data warehouse or Power BI data model that resolves a many-to-many relationship between two dimension tables — a relationship that cannot be directly modelled in a standard star schema without producing incorrect results. Standard star schema design assumes that each fact table row is associated with exactly one member of each dimension; relationships between the fact table and dimension tables are many-to-one (many fact rows reference one dimension member). When a dimension relationship is genuinely many-to-many — one GL account can belong to multiple reporting categories, one employee can be allocated to multiple cost centres, one project can span multiple geographies — a direct relationship between the two dimension tables cannot be represented in Power BI or Analysis Services without a bridge table to mediate the relationship. Without a bridge table, a many-to-many relationship in a Power BI model either produces filter propagation errors, duplicate aggregation results, or requires workarounds that compromise model design.
Bridge Table Architecture
| Component | Content | Relationship |
|---|---|---|
| Dimension A | e.g., Account dimension (AccountKey, AccountCode, AccountName) | One-to-many to Bridge |
| Bridge Table | AccountKey + CategoryKey (one row per account-category combination) | Many-to-one from both dimensions |
| Dimension B | e.g., Reporting Category (CategoryKey, CategoryName, CategoryType) | One-to-many to Bridge |
| Weighting column | Optional — allocation weight if account value is split across categories (e.g., 0.6 and 0.4) | N/A — attribute of the bridge row |
Bridge Tables in Finance Data Models
The most common finance data modelling scenario requiring a bridge table in GCC enterprise Power BI environments is the allocation of GL accounts to multiple management reporting categories. Under IFRS 18 (effective 2027), enterprises must classify income statement line items into Operating, Investing, and Financing categories — a requirement that may result in some GL accounts needing to be split across categories rather than assigned to a single category. A bridge table between the Account dimension and the IFRS Category dimension (with a weighting column specifying what percentage of each account’s balance goes to each category) enables the finance reporting model to aggregate account balances into IFRS categories correctly without duplicating the GL fact rows.
Bridge Tables and DAX Complexity
Bridge tables in Power BI require specific DAX patterns to produce correct filter propagation. The standard approach uses bidirectional relationship filtering between the fact table and the bridge table (enabling filters from Dimension B to propagate through the bridge to the fact table), combined with SUMX or CALCULATE patterns that respect the bridge table’s weighting column. Without careful DAX design, a bridge table implementation can produce double-counting — where a fact row that matches two bridge rows is aggregated twice in the result. Every bridge table implementation must be tested with data that specifically exercises the many-to-many scenario to confirm that aggregations are correct.
What Goes Wrong in Practice
The specific bridge table failure that most frequently produces incorrect finance report totals is a model where bidirectional relationship filtering was enabled on the bridge table relationship without understanding that bidirectional filtering can propagate unintended filter contexts to other fact tables in the model. Bidirectional filtering in Power BI should be used with full awareness of which tables are connected by the bidirectional path and whether the unintended propagation could affect measures on other fact tables. The alternative — using TREATAS or CROSSFILTER in DAX measures to explicitly manage filter propagation — is more verbose but more controllable.
How Loop Wise Solutions Designs Bridge Tables
We document every bridge table in the data model specification with its source many-to-many relationship, the weighting methodology (equal split, percentage-based, or binary), and the DAX pattern used to aggregate correctly across the bridge. Bridge table implementations are validated with a reconciliation test — confirming that the sum of all bridge-weighted values equals the total of the underlying fact table without the bridge filter applied.
Answers before you ask.
Many-to-many relationships between two dimensions in a star schema. When one cost centre maps to several cost categories and one category contains several cost centres, a direct relationship cannot correctly filter the fact table by both. A bridge (associative or junction) table sits between them, resolving the many-to-many so aggregation stays correct.
It holds the pairings between the two dimensions — each row linking one member of each — so the many-to-many is expressed as two many-to-one relationships through the bridge. Queries then traverse dimension to bridge to dimension, letting the fact table be filtered correctly by both dimensions without duplicating data.
Without it, resolving a many-to-many by duplicating rows or forcing a direct relationship produces incorrect aggregation — double-counting or misattributing measures. The model cannot correctly filter facts by both dimensions. The bridge table exists precisely to avoid these errors, giving accurate results where a simple star relationship cannot.
They are also called associative or junction tables. Considerations include the grain of the pairings, how the bridge affects filter propagation, and potential performance on large many-to-many sets. Bridge tables solve a real modelling problem, but must be designed carefully — the relationships and any weighting of allocations across the many-to-many need deliberate thought.