Advice/howto - many to 1 relationship

[stan_w_gifford]

Good morning/afternoon.

I cannot get my head around this so I cast this to the community.

I have two tables

Table1

Name      CM        Appointments

Joe          Joe2025-01      1

Joe          Joe2025-01      1

Joe          Joe2025-02      1

Bert         Bert2025-01    1

Table2

Name    CM   DaysLeave

Joe        Joe2025-01    3

Joe        Joe2025-02    4

I need to get a report from table 1 that sums the appointments for a CM (That bit was easy) and gives me the days leave from table2. Note there will only be a single record in Table2 for any particulare name/CM combination.

The CM COLUMN in both tables is derived from a date and the name of the person and is used to join the tables together.

The report I am after is based on a slicer that (effectivly filters table one to a range of CM's

The report should show (assuming I filter for 2025-01 and 2025-02

Name             Appointments                Leave

Joe                        3                                7

Bert                       1                                0

So I need a way of creating a measure on Table1 that picks up the leave from table2 for all the CM's for the filtered value.

For Joe if I use RELATED leave, the leave days would end up being wrong because the RELATED will bring back the leave for every instance of Joe2025-01 and Joe2025-02

(The actual data would be)

Joe  joe2025-01    3

Joe  joe2025-01    3

Joe  Joe2025-02    4

Which means his total leave days becomes 10 - instead of 7

Fundimentally I really only want to pick up the leave for the first instance of CM - rather than every instance of CM.

I truly hope the above makes sense - I find it very hard to describe but hope I have succeeded

Stan

[stan_w_gifford]

OK - got it to now work in a card.

The actual formula is

Leave Days Measure =

VAR CurrentName = AllSelected(Consolidated2[LeaveKey])

RETURN

CALCULATE(

SUM(Leave[Days_Leave]),

Leave[LeaveKey] in CurrentName,

ALL(Consolidated2)

)

When I put the value in the card, I get the leave across everyone - so to get a specific persons leave over the period I filter the card for say Leane with a leavekey that starts with Leanne.

Thanks for the guidance - with combined effort we got there in the end.

Stan

[stan_w_gifford · Accepted solution]

As mentioned the actual measure is;

Leave Days Measure =

VAR CurrentName = AllSelected(Consolidated2[LeaveKey])

RETURN

CALCULATE(

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

[v-dineshya]

Hi @stan_w_gifford ,

Thank you for your response.  If you have any question relating to the current thread, please do let us know and we will try out best to help you.

Regards,

Dinesh

[stan_w_gifford]

Whoops - forgot the measure I am using;

Leave Days Measure =

VAR CurrentName = MAX(Consolidated2[LeaveKey]) var DL=0

RETURN

CALCULATE(

SUM(Leave[Days_Leave]),

Leave[LeaveKey] = CurrentName,

ALL(Consolidated2)

)

Consolidated2 is equivelant to table 1 and Leave is equivalent to Table2

I suspect the MAX is freaking the calculation out.....

[stan_w_gifford]

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!

[stan_w_gifford]

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

Stan

[v-dineshya]

Hi @stan_w_gifford ,

As you mentioned in your previous response, You are working on proposed solution suggested by @jaineshp  .  Please do let us know if you have any further queries.

Regards,

Dinesh

[jaineshp]

Hey @v-dineshya,

Thank you for the kind recognition - always happy to contribute to our community's success!

Best Regards,
Jainesh Poojara | Power BI Developer

[v-dineshya]

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

[jaineshp]

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