DAX Many-to-Many Bridge Patterns with Bidirectional Filtering: Building Accurate Allocation, Attribution, and Shared Dimension Measures Across Complex Schemas

Introduction

Imagine you're building a sales attribution model where each deal can be credited to multiple sales reps based on their contribution percentage. Or you're working on a marketing analytics model where a single campaign can span multiple product lines, and a single product line can be touched by multiple campaigns. Or consider a project accounting system where costs are shared across departments according to allocation keys that change quarterly. All of these scenarios share the same structural challenge: a true many-to-many relationship between entities that you need to measure accurately, allocate proportionally, and filter intuitively.

This is where DAX many-to-many bridge patterns come in — and where most intermediate Power BI developers hit a wall. The default behavior of bidirectional filtering sounds like it should solve the problem automatically, but it introduces data model instability, ambiguous filter paths, and measures that silently return wrong answers. The correct approach requires understanding the interplay between physical relationships, filter propagation, virtual relationships built with TREATAS, bridge table design, and the precise semantics of functions like CROSSFILTER and USERELATIONSHIP. Getting this right is the difference between a model that produces defensible numbers and one that produces convincing lies.

By the end of this lesson, you'll be able to design and implement bridge table patterns for genuine many-to-many scenarios, write allocation and attribution measures that survive filtering from multiple dimensions, understand when bidirectional filtering is safe versus when it silently corrupts your results, and use virtual relationship techniques to maintain control when the physical model can't express your intent.

What you'll learn:

• How to identify true many-to-many relationships and design the correct bridge table schema
• The specific failure modes of bidirectional filtering in complex schemas, with examples
• How to write proportional allocation measures that remain accurate under any filter combination
• Attribution patterns using TREATAS and CALCULATETABLE to build virtual relationships on-the-fly
• Shared dimension patterns for scenarios where the same lookup table serves multiple fact tables
• Performance trade-offs between bidirectional filtering, CROSSFILTER(), and measure-level virtual relationships

---

Prerequisites

This lesson assumes you're comfortable with DAX fundamentals at an advanced level. Specifically, you should understand how filter context propagates through relationships — if you need a refresher on that foundation, start with DAX Relationships 101: How Power BI Filters Flow Across Tables and Why It Matters for Your Measures. You should also be confident with CALCULATE, context transition, and the difference between filter modifiers. The lesson on Understanding DAX: CALCULATE and Filter Context covers those mechanics in depth. Familiarity with TREATAS, CROSSFILTER, and USERELATIONSHIP is helpful but not required — we'll build up to them.

---

Understanding What Makes a Relationship Truly Many-to-Many

Before we write a single line of DAX, we need to be precise about what we mean by many-to-many. Power BI uses this term loosely in two distinct situations, and conflating them is the root cause of most modeling mistakes in this space.

Accidental many-to-many happens when your data has duplicates on what should be a join key — typically a sign of incomplete ETL or a missing grain transformation. If your DimProduct table has duplicate ProductID rows because someone accidentally duplicated the data load, Power BI will warn you about a many-to-many relationship, but the fix is data cleanup, not a bridge table.

Structural many-to-many happens when the business reality genuinely requires it. A deal can legitimately be associated with multiple reps. A product can genuinely belong to multiple categories simultaneously. A campaign can genuinely touch multiple product lines. These aren't data quality problems — they're facts about how the business works. These are the scenarios we're solving.

Structural many-to-many relationships come in two flavors based on whether they carry payload:

1. Pure bridge tables — a junction table that simply records which entities are related (DealID, RepID), possibly with an allocation weight
2. Weighted bridge tables — the junction table carries meaningful attributes like percentage splits, effective dates, or priority rankings

Both require bridge tables in your data model. What differs is how you compute your measures.

---

The Bridge Table Schema: Getting the Foundation Right

Let's ground this in a realistic scenario we'll use throughout the lesson. You're modeling a professional services firm:

• DimProject — each project is a distinct engagement
• DimEmployee — the workforce
• BridgeProjectEmployee — records which employees are assigned to which projects, with an allocation percentage
• FactTimesheet — weekly time entries logged by employees
• FactProjectRevenue — revenue recognized per project

The bridge table looks like this:

ProjectKey	EmployeeKey	AllocationPct
P001	E001	60%
P001	E002	40%
P002	E002	50%
P002	E003	50%

Notice that Employee E002 appears on both projects. This is structurally correct — she splits her time. If you tried to model this in a traditional star schema without the bridge, you'd either have to pick one project for E002 (losing accuracy) or duplicate her records (creating overcounting).

Your physical relationship chain should be:

DimEmployee --> BridgeProjectEmployee <-- DimProject

Both relationships are single-direction from the dimension to the bridge. The bridge itself joins to your fact tables:

DimProject --> FactProjectRevenue
DimEmployee --> FactTimesheet

This is the correct starting structure. The temptation at this point is to enable bidirectional filtering everywhere and let Power BI figure out the path. Resist that temptation until you understand exactly what it does.

---

Why Bidirectional Filtering Is More Dangerous Than It Looks

Bidirectional filtering means that when a filter is applied to one table, it propagates in both directions across a relationship rather than only from the "one" side to the "many" side. This sounds convenient, but it creates three serious problems in complex schemas.

Problem 1: Ambiguous Filter Paths

When bidirectional filtering is enabled throughout a schema, the DAX engine may have multiple valid paths to propagate a filter from point A to point B. Power BI will use one of them, but not necessarily the one you intend. In schemas with multiple fact tables sharing dimensions, this ambiguity can mean that slicing by DimEmployee filters FactProjectRevenue through the bridge, which may or may not be what you want depending on the report context.

Problem 2: Silent Double-Counting

Here's a concrete example with our schema. Suppose you enable bidirectional filtering on both relationships touching the bridge table. Now you write a simple revenue measure:

Total Revenue = SUM( FactProjectRevenue[Revenue] )

When a user slices by DimEmployee[EmployeeName], the filter crosses from DimEmployee to BridgeProjectEmployee, and from there it crosses to DimProject, and that filter then reaches FactProjectRevenue. So far, so good — you'll see only revenue for projects that employee worked on.

But now a user selects multiple employees on the same project. Since both E001 and E002 are on project P001, the filter passes through both bridge rows, and the revenue for P001 still appears once (the DISTINCT values in DimProject are correctly used). However, if you now try to build an allocated revenue measure using the allocation percentages in the bridge, bidirectional filtering has already collapsed the bridge context in a way that makes your percentage calculations ambiguous.

Problem 3: The Expansion Problem with Shared Dimensions

Consider a "shared dimension" pattern — where DimDate serves both FactTimesheet and FactProjectRevenue. If bidirectional filtering is enabled on the DimDate relationship to one fact table, filtering by date can inadvertently propagate through one fact table, through a shared dimension, into the other fact table. This creates measures where selecting "Q1 2024" in a report changes not just which timesheets appear, but which projects appear, even if you were trying to show all-time project revenue filtered only by the employee.

The correct approach is to leave all physical relationships as single-direction and use DAX functions — specifically CROSSFILTER(), USERELATIONSHIP(), and TREATAS() — to activate the filter direction you need, only in the measures where you actually need it. This is the architecture pattern used in enterprise models.

You can learn the full mechanics of these functions in Mastering DAX CROSSFILTER and USERELATIONSHIP: Activating Inactive Relationships and Controlling Filter Direction in Power BI.

---

Building the Core Bridge Traversal Measure

With our schema properly set up (all relationships single-direction), let's build the foundation measure that correctly traverses the bridge.

The goal: when a user filters by DimEmployee, show the total revenue for projects those employees are assigned to, weighted by their allocation percentage.

Step 1: Understand the Context Flow

When a user selects an employee from a slicer, a filter context exists on DimEmployee. That filter does NOT automatically flow to DimProject through the bridge (because our relationships are single-direction from dimensions to bridge). We need to explicitly push that filter through.

Step 2: Build the Allocated Revenue Measure

Allocated Revenue =
VAR SelectedEmployees =
    VALUES( DimEmployee[EmployeeKey] )

VAR BridgeSubset =
    CALCULATETABLE(
        BridgeProjectEmployee,
        TREATAS( SelectedEmployees, BridgeProjectEmployee[EmployeeKey] )
    )

VAR AllocatedRevenue =
    SUMX(
        BridgeSubset,
        VAR ProjectKey = BridgeProjectEmployee[ProjectKey]
        VAR AllocationPct = BridgeProjectEmployee[AllocationPct]
        VAR ProjectRevenue =
            CALCULATE(
                SUM( FactProjectRevenue[Revenue] ),
                DimProject[ProjectKey] = ProjectKey
            )
        RETURN
            ProjectRevenue * AllocationPct
    )

RETURN
    AllocatedRevenue

Let's walk through what this does. First, we capture the currently visible EmployeeKey values in SelectedEmployees — this respects whatever filter the user has applied via slicer or cross-filter. Second, we use TREATAS to stamp that employee filter onto the bridge table, which gives us only the bridge rows relevant to the selected employees. Third, we iterate over those bridge rows with SUMX, and for each row we look up the revenue of that specific project and multiply it by the allocation percentage.

Step 3: Verify the Math

For a visual with no filters: E001 gets 60% of P001 revenue, E002 gets 40% of P001 plus 50% of P002 revenue, E003 gets 50% of P002 revenue. If P001 has $100K revenue and P002 has $80K:

• E001 allocated revenue: $60K
• E002 allocated revenue: $40K + $40K = $80K
• E003 allocated revenue: $40K

Total allocated across all employees: $180K. Note this exceeds total actual revenue of $180K ($100K + $80K) — this is correct and expected behavior. Allocated revenue is not meant to be summed across employees; it represents each employee's proportional credit. If your allocation percentages per project sum to 100%, then allocated revenue across employees for a single project also sums to that project's total revenue. The "double counting" is intentional — it's attribution, not aggregation.

---

Multi-Dimensional Bridge Filtering: Surviving Cross-Filters from Multiple Dimensions

The single-dimension filter case is manageable. Things get genuinely hard when you need your bridge-traversal measure to survive simultaneous filters from multiple dimensions. Continuing our professional services scenario: a user places both an employee filter AND a date filter on the same report page. They want to see allocated revenue for Q1 2024 for the senior consultants.

The date dimension connects to FactProjectRevenue via DimDate[DateKey] → FactProjectRevenue[RecognitionDateKey]. The employee filter connects through our bridge. We need both filters to cooperate.

The Naive Approach (and Why It Fails)

You might try nesting CALCULATE calls:

-- This approach is WRONG -- do not use
Allocated Revenue Naive =
CALCULATE(
    [Allocated Revenue],
    CROSSFILTER( DimEmployee[EmployeeKey], BridgeProjectEmployee[EmployeeKey], Both )
)

This activates bidirectional filtering for the duration of this measure evaluation. But it creates exactly the ambiguous filter path problem we described earlier — the date filter that was sitting on FactProjectRevenue can now propagate backward through the project dimension, through the bridge, and interfere with the employee filter. You'll get results that look plausible but are incorrect under certain filter combinations.

The Correct Approach: Explicit Context Passing

Allocated Revenue Filtered =
VAR SelectedEmployees =
    VALUES( DimEmployee[EmployeeKey] )

VAR BridgeSubset =
    CALCULATETABLE(
        BridgeProjectEmployee,
        TREATAS( SelectedEmployees, BridgeProjectEmployee[EmployeeKey] )
    )

VAR AllocatedRevenue =
    SUMX(
        BridgeSubset,
        VAR ProjectKey = BridgeProjectEmployee[ProjectKey]
        VAR AllocationPct = BridgeProjectEmployee[AllocationPct]
        VAR ProjectRevenue =
            CALCULATE(
                SUM( FactProjectRevenue[Revenue] ),
                DimProject[ProjectKey] = ProjectKey
                -- The date filter from DimDate naturally propagates to FactProjectRevenue here
                -- because that relationship is already active and single-direction
            )
        RETURN
            ProjectRevenue * AllocationPct
    )

RETURN
    AllocatedRevenue

Notice that we don't do anything special for the date filter. Because DimDate → FactProjectRevenue is a normal single-direction relationship, any date filter in the outer filter context automatically flows into the CALCULATE block that computes ProjectRevenue. We only needed to explicitly handle the employee-to-bridge path, which was the non-standard traversal.

---

The Shared Dimension Pattern: One Lookup Table, Multiple Fact Tables

A shared dimension is a dimension table — often DimDate, DimGeography, or DimCostCenter — that relates to multiple fact tables in your model. This is common and fine in star schemas. But when you add a bridge table into the mix, and that bridge table also needs to be filtered by the shared dimension, things get complicated.

Real Scenario: Campaign Attribution with Shared Product Dimension

You have:

• DimProduct — shared across everything
• FactSales — transactional sales data, with ProductKey
• FactCampaignSpend — media spend by campaign
• BridgeCampaignProduct — which campaigns target which products

A product can be targeted by multiple campaigns, and a campaign can target multiple products. You want to build a measure: Revenue per Campaign Dollar, which divides sales revenue for products in a campaign's targeting scope by the campaign's total spend.

Revenue Per Campaign Dollar =
DIVIDE(
    [Campaign Attributed Revenue],
    [Total Campaign Spend]
)

Let's build Campaign Attributed Revenue:

Campaign Attributed Revenue =
VAR SelectedCampaigns =
    VALUES( FactCampaignSpend[CampaignKey] )

-- Get the products targeted by selected campaigns
VAR TargetedProducts =
    CALCULATETABLE(
        VALUES( BridgeCampaignProduct[ProductKey] ),
        TREATAS( SelectedCampaigns, BridgeCampaignProduct[CampaignKey] )
    )

-- Get revenue for those products
VAR AttributedRevenue =
    CALCULATE(
        SUM( FactSales[Revenue] ),
        TREATAS( TargetedProducts, DimProduct[ProductKey] )
    )

RETURN
    AttributedRevenue

This pattern uses TREATAS twice. The first application pushes the campaign filter through the bridge to identify which products are in scope. The second application pushes the resulting product list as a filter into the DimProduct → FactSales relationship. Because both applications use TREATAS, we're building virtual filters that work regardless of physical relationship direction. No bidirectional filtering needed anywhere.

Handling Weighted Attribution in the Shared Dimension Pattern

What if the bridge table has attribution weights — for example, a campaign's "coverage score" for each product (how strongly it targets that product, from 0 to 1)?

Weighted Campaign Revenue Attribution =
VAR SelectedCampaigns =
    VALUES( FactCampaignSpend[CampaignKey] )

VAR BridgeRows =
    CALCULATETABLE(
        BridgeCampaignProduct,
        TREATAS( SelectedCampaigns, BridgeCampaignProduct[CampaignKey] )
    )

VAR WeightedRevenue =
    SUMX(
        BridgeRows,
        VAR ProductKey    = BridgeCampaignProduct[ProductKey]
        VAR CoverageScore = BridgeCampaignProduct[CoverageScore]
        VAR ProductRevenue =
            CALCULATE(
                SUM( FactSales[Revenue] ),
                TREATAS(
                    { ProductKey },
                    DimProduct[ProductKey]
                )
            )
        RETURN
            ProductRevenue * CoverageScore
    )

RETURN
    WeightedRevenue

Notice the use of { ProductKey } — a single-value table constructor — inside the TREATAS call within the SUMX loop. This correctly scopes the revenue lookup to exactly one product for each bridge row, then applies that row's weight. This is an important pattern to internalize.

---

Allocation Measures: Distributing Facts Across Shared Entities

Allocation is the inverse problem of attribution. In attribution, we start with a transaction and figure out which campaigns/reps/projects get credit. In allocation, we start with a cost or budget and distribute it across entities according to predefined keys.

Real Scenario: Overhead Cost Allocation Across Departments

You have:

• FactOverheadCosts — company-wide overhead by cost category (IT, Facilities, HR)
• DimDepartment — the departments that consume overhead
• BridgeOverheadAllocation — defines what percentage of each cost category is allocated to each department

CostCategoryKey	DepartmentKey	AllocationKey
IT	Sales	0.30
IT	Engineering	0.50
IT	Finance	0.20
Facilities	Sales	0.25
...	...	...

The measure: when a user filters by department, show the total overhead costs allocated to that department.

Allocated Overhead =
VAR SelectedDepartments =
    VALUES( DimDepartment[DepartmentKey] )

-- Get the bridge rows for selected departments
VAR DeptAllocations =
    CALCULATETABLE(
        BridgeOverheadAllocation,
        TREATAS( SelectedDepartments, BridgeOverheadAllocation[DepartmentKey] )
    )

-- Sum the allocated costs
VAR TotalAllocatedCost =
    SUMX(
        DeptAllocations,
        VAR CostCategoryKey = BridgeOverheadAllocation[CostCategoryKey]
        VAR AllocationKey   = BridgeOverheadAllocation[AllocationKey]
        VAR CategoryTotal =
            CALCULATE(
                SUM( FactOverheadCosts[Amount] ),
                TREATAS(
                    { CostCategoryKey },
                    FactOverheadCosts[CostCategoryKey]
                )
            )
        RETURN
            CategoryTotal * AllocationKey
    )

RETURN
    TotalAllocatedCost

This measure correctly isolates each cost category's total, applies the relevant allocation percentage for the selected department, and sums the results. Because we're iterating over the bridge rows with SUMX, each row's category and percentage are independent — no cross-contamination between cost categories.

Handling Time-Variant Allocation Keys

Allocation percentages often change over time. Your allocation key for IT costs might be 30% to Sales in Q1 but 35% in Q2 after a headcount change. The bridge table needs an effective date dimension:

CostCategoryKey	DepartmentKey	AllocationKey	EffectiveDate	ExpiryDate
IT	Sales	0.30	2024-01-01	2024-03-31
IT	Sales	0.35	2024-04-01	2024-12-31

Your measure must now apply the correct allocation key based on the date context. This is where bridge patterns become genuinely complex:

Time-Variant Allocated Overhead =
VAR SelectedDepartments =
    VALUES( DimDepartment[DepartmentKey] )

-- Get the min/max date in context (assumes DimDate filters FactOverheadCosts)
VAR ContextMinDate = MIN( DimDate[Date] )
VAR ContextMaxDate = MAX( DimDate[Date] )

-- Get bridge rows valid for any part of the date range in context
VAR ValidAllocations =
    CALCULATETABLE(
        BridgeOverheadAllocation,
        TREATAS( SelectedDepartments, BridgeOverheadAllocation[DepartmentKey] ),
        BridgeOverheadAllocation[EffectiveDate] <= ContextMaxDate,
        BridgeOverheadAllocation[ExpiryDate]    >= ContextMinDate
    )

VAR TotalAllocatedCost =
    SUMX(
        ValidAllocations,
        VAR CostCategoryKey = BridgeOverheadAllocation[CostCategoryKey]
        VAR AllocationKey   = BridgeOverheadAllocation[AllocationKey]
        VAR AllocEffDate    = BridgeOverheadAllocation[EffectiveDate]
        VAR AllocExpDate    = BridgeOverheadAllocation[ExpiryDate]
        VAR CategoryCostInPeriod =
            CALCULATE(
                SUM( FactOverheadCosts[Amount] ),
                TREATAS( { CostCategoryKey }, FactOverheadCosts[CostCategoryKey] ),
                DimDate[Date] >= AllocEffDate,
                DimDate[Date] <= AllocExpDate
            )
        RETURN
            CategoryCostInPeriod * AllocationKey
    )

RETURN
    TotalAllocatedCost

This measure correctly scopes each cost lookup to the intersection of the user's date filter and the allocation row's validity period. It's more complex, but it's handling a genuinely complex business rule. The use of DAX Variables in Practice: Using VAR and RETURN to Simplify Complex Measures is essential here — without variables, this measure would be nearly impossible to write correctly or debug.

---

Virtual Relationships with TREATAS: When the Physical Model Can't Express Your Intent

Sometimes you need a filter path that doesn't correspond to any physical relationship in your model. This happens when:

1. The bridge table shares a column with a dimension by value but not by relationship (e.g., a text code rather than a surrogate key)
2. You need to filter across incompatible grain levels
3. You're building a measure that should work against a dimension that was added after the model was designed and you can't modify the physical schema

TREATAS is the tool for all of these. It takes a table expression and a list of target columns, and returns a table where the values are stamped as if they came from the target columns — effectively creating a virtual relationship filter.

Pattern: Cross-Model Attribution Without Physical Relationships

-- Measure: Revenue attributed to skills, where skills connect
-- employees to projects through a text-based tagging system
Revenue By Skill =
VAR SelectedSkills =
    VALUES( DimSkill[SkillName] )

-- Find employees who have any of the selected skills
-- (via BridgeEmployeeSkill which has no physical relationship to FactProjectRevenue)
VAR QualifiedEmployees =
    CALCULATETABLE(
        VALUES( BridgeEmployeeSkill[EmployeeKey] ),
        TREATAS( SelectedSkills, BridgeEmployeeSkill[SkillName] )
    )

-- Find projects those employees worked on
VAR QualifiedProjects =
    CALCULATETABLE(
        VALUES( BridgeProjectEmployee[ProjectKey] ),
        TREATAS( QualifiedEmployees, BridgeProjectEmployee[EmployeeKey] )
    )

-- Get revenue for those projects
VAR SkillAttributedRevenue =
    CALCULATE(
        SUM( FactProjectRevenue[Revenue] ),
        TREATAS( QualifiedProjects, DimProject[ProjectKey] )
    )

RETURN
    SkillAttributedRevenue

This measure chains three TREATAS applications across two bridge tables to connect a skill filter all the way to project revenue — a five-hop path — without any bidirectional filtering. The measure is fully deterministic, immune to accidental filter propagation, and easy to reason about step by step.

For more depth on building these kinds of virtual relationship structures, the article on DAX for Many-to-Many Relationships and Complex Data Models covers complementary patterns.

---

Performance Considerations: When Bridge Patterns Become Slow

Bridge patterns with SUMX and nested CALCULATE calls can be expensive at scale. Understanding why — and what to do about it — is essential for production models.

Why These Measures Can Be Slow

The inner CALCULATE inside a SUMX loop creates a new filter context for every row in the bridge table. If your bridge table has 10 million rows (imagine a large SaaS product with users, features, and entitlements), that's 10 million individual storage engine queries. The DAX engine can cache these, but the cache doesn't always help across different evaluation contexts.

Optimization Strategy 1: Pre-aggregate the bridge

Instead of looking up revenue for each individual bridge row, pre-aggregate to the grain level that the bridge operates at:

Allocated Revenue Optimized =
VAR SelectedEmployees =
    VALUES( DimEmployee[EmployeeKey] )

VAR BridgeSubset =
    CALCULATETABLE(
        BridgeProjectEmployee,
        TREATAS( SelectedEmployees, BridgeProjectEmployee[EmployeeKey] )
    )

-- Pre-aggregate revenue to project grain (one CALCULATE instead of N)
VAR ProjectRevenues =
    ADDCOLUMNS(
        VALUES( DimProject[ProjectKey] ),
        "@Revenue", CALCULATE( SUM( FactProjectRevenue[Revenue] ) )
    )

VAR AllocatedRevenue =
    SUMX(
        BridgeSubset,
        VAR ProjectKey = BridgeProjectEmployee[ProjectKey]
        VAR AllocationPct = BridgeProjectEmployee[AllocationPct]
        VAR ProjectRevenue =
            MAXX(
                FILTER( ProjectRevenues, [ProjectKey] = ProjectKey ),
                [@Revenue]
            )
        RETURN
            ProjectRevenue * AllocationPct
    )

RETURN
    AllocatedRevenue

This builds a virtual table of project revenues once, then looks up from it for each bridge row using FILTER/MAXX — which operates entirely in memory against an already-computed set rather than issuing new storage engine queries.

Optimization Strategy 2: Use SUMMARIZECOLUMNS for bulk aggregation

When you need to aggregate bridge-traversed measures for a full matrix report (rows = employees, columns = projects), consider whether the calculation can be pushed to a SUMMARIZECOLUMNS-based measure pattern that lets the engine batch the lookups more efficiently.

Optimization Strategy 3: Reduce bridge table cardinality

This is a data model design decision, not a DAX optimization. If your bridge table has time-variant rows and you're always querying it with a specific time context, consider materializing only the current allocation keys or creating a separate "current period" bridge view in your data preparation layer. Fewer rows in the bridge table means fewer iterations in your SUMX loops.

Profiling Your Measures

Use DAX Studio to run Server Timings on your bridge measures. Look at the ratio of Formula Engine (FE) time to Storage Engine (SE) time. Bridge patterns that are slow typically show high FE time because the SUMX loop runs in the formula engine, while storage engine calls are relatively fast. If FE time is dominating, the optimization strategies above (pre-aggregation, virtual table lookups) will help most. You can go deeper on this diagnostic process with Performance Tuning DAX: Optimize Slow Measures with DAX Studio.

---

Row-Level Security Considerations in Bridge Models

If your model uses row-level security, bridge patterns introduce a subtle vulnerability. Consider: RLS on DimProject restricts users to projects in their region. If an employee in Region A is assigned to a project in Region B, and the user has a Region A security context, what should happen?

There are two valid business interpretations:

1. The user should see allocated revenue for Region A projects only, and this employee's Region B allocation is invisible to them.
2. The user should see the employee's total allocated revenue across all their projects, regardless of region.

The default behavior in a bridge model with RLS on the project dimension is interpretation #1 — the RLS filter propagates through DimProject → BridgeProjectEmployee, and only the bridge rows for allowed projects are visible. Your DAX measures will naturally scope to those rows.

If you need interpretation #2 (which is rarer but valid for people analytics scenarios), you need to explicitly lift the RLS filter within the measure using REMOVEFILTERS inside a CALCULATE that has appropriate admin-level business logic. This is an advanced pattern discussed in Mastering DAX Security: Dynamic Row-Level Security with USERPRINCIPALNAME and Org Hierarchies.

---

Hands-On Exercise

Build the following scenario from scratch in Power BI Desktop.

Dataset Description

Create three CSV files manually:

Employees.csv

EmployeeKey,EmployeeName,Department
E001,Alice Chen,Consulting
E002,Bob Martinez,Engineering  
E003,Carol Kim,Consulting
E004,Dave Patel,Engineering

Projects.csv

ProjectKey,ProjectName,Region
P001,Alpha Rollout,North
P002,Beta Migration,South
P003,Gamma Integration,North

BridgeProjectEmployee.csv

ProjectKey,EmployeeKey,AllocationPct
P001,E001,0.7
P001,E002,0.3
P002,E002,0.5
P002,E003,0.5
P003,E003,0.4
P003,E004,0.6

ProjectRevenue.csv

ProjectKey,Month,Revenue
P001,2024-01,120000
P001,2024-02,95000
P002,2024-01,80000
P002,2024-02,110000
P003,2024-01,200000
P003,2024-02,175000

Tasks

Task 1: Set up the model

• Load all four tables into Power BI
• Create single-direction relationships: Employees → Bridge (EmployeeKey), Projects → Bridge (ProjectKey), Projects → ProjectRevenue (ProjectKey)
• Verify no bidirectional filtering is enabled on any relationship

Task 2: Build the core allocated revenue measure

• Write the Allocated Revenue measure using the TREATAS + SUMX pattern from the lesson
• Create a matrix visual with Employees on rows and verify the numbers

Task 3: Add a Department slicer and verify cross-filter behavior

• Add a slicer for DimEmployee[Department]
• Select "Consulting" — you should see Alice Chen and Carol Kim with their respective allocated revenues
• Verify that the "Consulting" filter does not affect the total project revenue shown in a separate card visual (it should show all project revenue, not just consulting projects)

Task 4: Build the attribution chain

• Add a DimMonth table (just a list of months) and relate it to ProjectRevenue via Month
• Write a measure that shows allocated revenue for selected employees AND a selected month, verifying that both the employee filter and the date filter work simultaneously

Task 5: Validate your numbers manually

• For January 2024, Consulting employees (Alice, Carol), the expected allocated revenue is:
  • Alice: P001 January = $120K × 0.7 = $84,000
  • Carol: P002 January = $80K × 0.5 = $40,000, plus P003 January = $200K × 0.4 = $80,000, total = $120,000
• Verify your measure matches these figures exactly

---

Common Mistakes & Troubleshooting

Mistake 1: Using bidirectional filtering instead of TREATAS

Symptom: Measures return correct results in simple visuals but go wrong when multiple slicers are active simultaneously.

Fix: Remove all bidirectional filtering from bridge relationships. Use TREATAS to create explicit virtual filter paths in your measures.

Mistake 2: Iterating over the wrong table in SUMX

Symptom: Measure returns wrong totals — usually too high (double-counting) or too low (missing bridge rows).

Fix: Always iterate over BridgeTable rows, not over the dimension or fact table. The bridge table is the authoritative source of the many-to-many pairings and weights.

Mistake 3: Not capturing the filter context in a VAR before iterating

Symptom: When a measure is used in a row context (e.g., inside another SUMX), the inner TREATAS call picks up more rows than expected because the outer row context has modified the filter context.

Fix: Always start your bridge measures by capturing VALUES() into a VAR at the top, before any iteration begins. This locks the filter context for the employee or dimension keys at the moment the measure starts evaluating.

Mistake 4: Forgetting that TREATAS requires column types to match

Symptom: TREATAS-based measure returns all rows or errors out.

Fix: Ensure the column passed as source in TREATAS and the target column have compatible data types (both integer, both text, etc.). A common mistake is trying to TREATAS a text-keyed list against an integer surrogate key column.

Mistake 5: Not testing with "select all" vs. specific selection

Symptom: Measure works correctly for single values but over-counts when no filter is active.

Fix: Use IF( ISFILTERED( DimEmployee[EmployeeKey] ), [Bridge Measure], [Simple Measure] ) to provide different calculation paths for filtered and unfiltered contexts. Or verify that VALUES( DimEmployee[EmployeeKey] ) in the no-filter case returns all employees, and confirm your measure still makes business sense in that context (it should — you'd expect to see full allocated revenue across the entire employee population).

Mistake 6: Bridge table has NULL values in key columns

Symptom: Some employees or projects mysteriously disappear from bridge calculations.

Fix: Ensure your bridge table has no NULL values in any key column. NULL values are never equal to anything (including other NULLs) in DAX filter comparisons, so NULL bridge rows will never match any filter context. Handle NULLs in Power Query before the data reaches your model.

---

Summary & Next Steps

We've covered a lot of ground. Here's the architecture you now have in your toolkit:

Model design: Always use single-direction relationships on bridge table joins. Never rely on bidirectional filtering to solve many-to-many problems in complex schemas. The physical model's job is to express grain; the DAX measures' job is to express filter intent.

Core pattern: Capture dimension filter context with VALUES() into a VAR, use TREATAS to push that context through the bridge, iterate with SUMX over the relevant bridge rows, and use a nested CALCULATE to look up the fact value for each bridge row.

Time-variant allocation: Extend the bridge with effective/expiry date columns and filter the bridge subset to valid rows before iterating. Let the date dimension's standard relationship handle date filtering on the fact table; handle only the bridge-specific date filtering explicitly in DAX.

Performance: Pre-aggregate fact values to the bridge's dimension grain using ADDCOLUMNS before the SUMX loop. This converts N storage engine queries into one bulk query plus in-memory lookups.

Security: Understand that RLS on dimension tables propagates through bridge joins, which is the correct default. Lifting RLS within bridge measures requires explicit business justification.

Where to Go Next

If you're working with financial models that use similar allocation patterns (budget spreading, overhead absorption, intercompany eliminations), the techniques here map directly to the scenarios in Advanced DAX Patterns for Financial Reporting: Mastering P&L, Balance Sheet, and Budget Models.

If your bridge patterns need to incorporate time intelligence — for example, showing allocated revenue YTD or comparing this quarter's allocation to last quarter — you'll want to layer in the patterns from Time Intelligence in DAX: YTD, MTD, Previous Period, and Rolling Averages while being careful not to let the time filter collide with your bridge traversal logic.

And if your bridge measures are starting to feel unwieldy — multiple nested VARs, long chains of TREATAS calls — the best next step is to decompose them into modular base measures and combine them with CALCULATE. The DAX Virtual Tables in Practice: Using ADDCOLUMNS, SUMMARIZE, and GENERATEALL to Build Complex Aggregations Without Helper Tables lesson will give you the compositional tools to keep these measures readable and maintainable as your schema grows.

Many-to-many bridge patterns are where DAX earns its reputation for complexity. But with the right mental model — explicit filter paths, bridge-table iteration, and virtual relationships — they're entirely approachable and produce some of the most valuable analytics in any serious data model.