Forum Discussion
Helper IAdvice/howto - many to 1 relationship
As mentioned the actual measure is;
Leave Days Measure =VAR CurrentName = AllSelected(Consolidated2[LeaveKey])RETURNCALCULATE(SUM(Leave[Days_Leave]),Leave[LeaveKey] in CurrentName,ALL(Consolidated2))To Get the correct value in the card I made 4 descrete measure (I have 4 salespeople) like.....AL_Leanne = sumx(Filter(Consolidated2,Consolidated2[Who]="Leanne"),[Leave Days])/Calculate(COUNTROWS(Consolidated2),Consolidated2[Who]="Leanne")So simplistically, the measure finds the number of days leave between two days via the leave key and uses ALL the descrete LEAVEKEY values exposed via the filter.Then the measure for each salesperson calculates the leave from the measure sumerised - then divided by the number of entries in Consolidated2 - AKA Table1.I am going to be marking this response as the correct/Solution - however really some previous answers led me to develop this - so really there are multiple correct solutions...................Stan
Memorable MemberHey 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