Data Model Objects that Require Slicing at Different Granularities

[schilders]

Hello!!  

I need guidance regarding a data modeling issue.  I need to calculate a Per Member Per Month measure which I've already programmed.  The dollar amount (NET) must be "sliced" by Financial Responsibility which is a field only related to the dollar amount and not to my Member Months.  That is, Member Months should never be slicied by Financial Responsibility.

My data model is currently configured as:

Business Rules:

1.  Member Months are aggregated per calendar year/month, provider, health plan and group.  Not all providers will have a positive value.

2.  Net from Details object is the amount paid for member claims.  Not all providers will have members who have claims.

3.  Net from Details object must be grouped as to Financial Responsibility without affecting the Member Months aggregate.

I've tried the following:

1.  Adding Net from claims data to Member Months.  I tried this through a left outer join in my SQL query so that providers with paid claims have a non-zero Net amount.  Unreliable Net amount was returned.

2.  Attempted to create a "bridge" object comprised of Provider, Service Year/Month, Financial Responisbility, Health Plan, Group, and Total Net amount.  Could not create an accurate relationship from Member Months to associate this object for calculation purposes.

The visual (with redacted edits) is as follows:

Year is from the Date Dimension.  HP Code is from the Health Plans object.  Sum of MM is from Member Months object.  Net is from Details object.  Member Months aggregate is valid.  Net is not valid.

What are the steps I should take to modify or augment the data model to produce the intended results?

[v-venuppu]

Hi @anilgavhane ,

Thank you @anilgavhane for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[anilgavhane · Accepted solution]

@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.

[schilders]

Thanks for your guidance.  I appreciate the suggestion about using inactive relationships between busienss objects and activating them via USERELATIONSHIP in DAX.  Thus far, this appraoch has yielded favorable results.  Thanks again!

[v-venuppu]

Hi @schilders ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @pbiuseruk for the prompt response.

I have developed a PBIX file using sample data.Please go through the attached PBIX for your reference.

[pbiuseruk]

Hi,

Can you these 2 measures for your Member Months value:
Measure 1: 
MemberMonths (Ignore FR) :=
CALCULATE(
[MemberMonths],
REMOVEFILTERS(DimFinancialResponsibility)
) 

Measure 2: 
Ignores all filter = CALCULATE(
[MemberMonths],
ALL(DimFinancialResponsibility)
) 

[schilders]

@pbiuseruk I tried something similar.  I need to return the Net amount and ensure the slicers for health plan, option as well as financial responsibility affect the calculation.  In the same visual, I need to return the Member Months amount ensuring slicers for health plan and option affect this calculation.  Still not producing intended results.  Any other suggestions?