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.
