Advice/howto - many to 1 relationship

Hi stan_w_gifford ,

Thank you for reaching out to the Microsoft Community Forum.

Hi jaineshp , Thanks for your prompt response,

Hi stan_w_gifford ,  Could you please try the proposed solution shared by jaineshp ,  Please do let us know if you have any further queries.

Regards,

Dinesh

Hey stan_w_gifford,

Solution for Many-to-One Relationship Issue

Problem Analysis: You're experiencing the classic many-to-one relationship challenge where RELATED() function duplicates values across multiple rows, causing inflated totals in your measures.

Recommended Solution Steps:

Step 1: Create a Summarized Leave Measure Instead of using RELATED(), create a measure that aggregates leave days at the Name level:

Total Leave Days =
SUMX(
VALUES(Table1[Name]),
CALCULATE(SUM(Table2[DaysLeave]))
)

Step 2: Alternative Approach - Distinct Count Method If the above doesn't work perfectly with your slicer setup, try this approach:

Leave Days Measure =
VAR CurrentName = MAX(Table1[Name])
RETURN
CALCULATE(
SUM(Table2[DaysLeave]),
Table2[Name] = CurrentName,
ALL(Table1)
)

Step 3: Verify Relationship Setup

• Ensure your relationship between Table1[CM] and Table2[CM] is properly configured
• Set the relationship to "Many to One" (Table1 to Table2)
• Cross-filter direction should be "Single"

Step 4: Report Structure Create your visual with:

• Name from Table1 (not Table2)
• Appointments: SUM(Table1[Appointments])
• Leave: Use the measure created in Step 1 or 2

Step 5: Testing Test with your CM slicer filtering for 2025-01 and 2025-02. You should now see:

• Joe: 3 appointments, 7 leave days
• Bert: 1 appointment, 0 leave days

Key Point: The measure approach prevents the multiplication effect because it aggregates at the Name level first, then applies the filter context from your slicer.

This solution maintains the integrity of your leave calculations while properly handling the many-to-one relationship structure.

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

Best regards,
Jainesh Poojara / Power BI Developer

Hi - still working on this - have been ill for a couple of days - not quite got it - but studying suggested answer.

Stan

OK - I did a sin that I would normally beat my staff for (if I had any staff) - I left some facts out.

1. Not every line in Table 1 would have appointments for a specific CM

2. I want to put the result into a card!

At the moment I have got a measure working - I I put my data into a matrix, it looks like this (all data from Table1)

What I want to put in the card is a single value - from above the value would be 8 (8 days leave over the period)

If I put the measure into the card I get a nice big fat BLANK!

Whoops - forgot the measure I am using;

OK - got it to now work in a card.

The actual formula is