Tables with Two Key

[Mamoun_issa]

Hello,

I Have two tables the first includes the Revenue Details and the other one claims, the tables looks something similar to the below,

Medical Record No	Visit Number	Visit Date	Name	Claim ID	Phamracy Claim ID	Invoice Amount
XX	VAN100	1 Jan 20	John	CL1000		150
YY	VAN200	1 Jan 20	Adam		CLP2000	250
ZZ	VAN300	5 Jan 20	Lawrance	CL1500	CLP2500	500

And the other table Include the the Claims Details as follow:

Claim ID	Payment Date	Payment Amount
CL1000	2 Feb 20	150
CLP2000	5 Feb 20	200
CL1500	3 March 20	200
CLP2500	7 Feb 20	300

Im trying to create a relation between the two tables but since there is two coulmns in the first table as the primary key and only one in the second table i could not get, im looking to build a summary that looks like 

Visit Number ( Table 1 )	Claimed Amount (Table 1)	Paid Amount (Table 2 )
VAN100	150	150
VAN200	250	200
VAN300	500	500

[az38]

Hi @Mamoun_issa 

you can add a measure to your Revenue Details table

Paid Amount = 
CALCULATE(SUM('Claims Details'[Payment Amount]); 
FILTER(ALL('Claims Details');
'Claims Details'[Claim ID]=SELECTEDVALUE('Revenue Details'[Claim ID]) || 'Claims Details'[Claim ID]=SELECTEDVALUE('Revenue Details'[Phamracy Claim ID])
)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Mamoun_issa]

@az38 Thank you for your response , but unfortunatly this did not work out since in the revenue details the claim ID and Pharmacy Claim ID are in the same row, so it took only the first expression (i.e. Claim ID)

[az38]

Hi @Mamoun_issa 

i don't understand. it works perfect on your data sample

please, extend your mention, what do you mean "first expression"?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Mamoun_issa]

@az38 The Formula i made was like this :

Formula = CALCULATE(SUM(ERAClaim[Activity.PaymentAmount]),

FILTER(ALL(ERAClaim),

ERAClaim[ID]=SELECTEDVALUE(ACR[PHA_CLAIM_ID]) || ERAClaim[ID]=SELECTEDVALUE(ACR[CLAIM_ID])

))

However, the result i got are as follow, it takes only the sum of the first claim id

[az38]

@Mamoun_issa 

are you sure your PHA_CLAIM_ID and CLAIM_ID are clean and have no any spaces and other invisible symbols?

also, please, povide as your data looks like in original data sources, separately (ERAClaim and ACR)

do you create FOrmula as measure or calculated column?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Mamoun_issa]

@az38 

Yes Im sure the data is clean, i duplicated the table to check the details thats why it is appearing in two columns, i created a measure not a column

[az38]

@Mamoun_issa 

thats the result on your first data sample

let me see please on your original data sample

do not hesitate to give a kudo to useful posts and mark solutions as solution