Forum Discussion
Tables with Two Key
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 |
7 Replies
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
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)
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
