Data Model Objects that Require Slicing at Different Granularities

schilders 

Use DAX to Control Filter Context

Create two separate measures that explicitly control how filters apply:

-- Member Months ignoring Financial Responsibility MemberMonths_IgnoreFR := CALCULATE( [MemberMonths], REMOVEFILTERS(DimFinancialResponsibility) ) -- Net sliced by Financial Responsibility Net_ByFR := CALCULATE( [Net], VALUES(DimFinancialResponsibility) )

Avoid Direct Relationships That Enforce Unwanted Filters

If Financial Responsibility is related to Net but not Member Months, avoid creating a relationship that forces filter propagation. Instead:

• Use inactive relationships and activate them in DAX with USERELATIONSHIP
• Or use disconnected tables for slicers and apply filters manually in measures

3. Consider a Bridge Table (Carefully)

Your bridge table idea is valid but needs precise granularity. Try:

• Creating a bridge with Provider, Year/Month, Health Plan, Group, Financial Responsibility
• Link it to Net via Financial Responsibility and to Member Months via Provider/Year/Month/Health Plan/Group
• Use DAX to aggregate Net and Member Months separately

4. Final PMPM Measure

Once you have the two measures working independently, calculate PMPM like this:

PMPM := DIVIDE( [Net_ByFR], [MemberMonths_IgnoreFR] )

This ensures the correct slicing behavior for each component.