match table A with table B according to common relation with table C

[annonymous1999]

hello everyone i have 3 tables:

client names :

Account Name	Client Name
Account 1	Name A
Account 2	Name A
Account 3	Name B
Account 4	Name C
Account 5	Name D

revenue table:

Client Name	Total
Name A	value
Name A	value
Name C	value
Name C	value

payemnt table:

Account Name	Amount
Account 1	value
Account 1	value
Account 2	value
Account 4	value
Account 5	value

i have 2 inactive relationships due to many to many and circular dependency errors

first relation is between client table(client name) and revenenue(client name)
second relation is between client table(account name) and payment(account name)

so how can i create a measure that match that account name from payment with client name from revenue table

expected result:

Client Name	Amount
Client A	sum of value from account 1 + account 2
Client C	sum of value from account 4

[eliasayy · Accepted solution]

hello @annonymous1999 
please try:

Measure = 
CALCULATE(SUM(Payments[Amount]),
USERELATIONSHIP('Revenues'[Client Name],Clients[Client Name]),
USERELATIONSHIP(Clients[Account Name],Payments[AccountName]),
'Revenues'[Client Name] IN VALUES(Clients[Client Name]))

then add to table Client[Client Name]
and add the measure it should work

[annonymous1999]

thank you @eliasayy  and @olgad 

[eliasayy · Accepted solution]

hello @annonymous1999 
please try:

Measure = 
CALCULATE(SUM(Payments[Amount]),
USERELATIONSHIP('Revenues'[Client Name],Clients[Client Name]),
USERELATIONSHIP(Clients[Account Name],Payments[AccountName]),
'Revenues'[Client Name] IN VALUES(Clients[Client Name]))

then add to table Client[Client Name]
and add the measure it should work

[olgad]

Hi there,

create inactive relationships

Then use USERELATIONSHIP in your calculation:

Payment Amount = Calculate(Sum(payment[Amount]), USERELATIONSHIP('client names'[Account Name], payment[Account Name]))

Hope that helps.

[annonymous1999]

hello @olgad  as you can see in revenue table there is no Client D only A and C 
i tried

Measure =  Calculate(Sum(payment[Amount]), 
USERELATIONSHIP('client names'[Account Name], payment[Account Name]),
Revenue[Client Name] IN VALUES(client names[Client Name])
)

[olgad · Accepted solution]

RevenueTotal = Calculate(Sum(payment[Amount]), USERELATIONSHIP('client names'[Client Name], revenue[Client Name        ]), USERELATIONSHIP('client names'[Account Name        ], payment[Account Name        ]))