Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Mamoun_issa
Frequent Visitor

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 NoVisit NumberVisit DateNameClaim IDPhamracy Claim IDInvoice Amount
XXVAN1001 Jan 20JohnCL1000 150
YYVAN2001 Jan 20Adam CLP2000250
ZZVAN3005 Jan 20LawranceCL1500CLP2500500

 

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

Claim IDPayment Date Payment Amount
CL10002 Feb 20150
CLP20005 Feb 20200
CL15003 March 20200
CLP25007 Feb 20300

 

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 )
VAN100150150
VAN200250200
VAN300500500
7 REPLIES 7
az38
Community Champion
Community Champion

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


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

@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
Community Champion
Community Champion

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


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

@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
Capture.JPG
az38
Community Champion
Community Champion

@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


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

@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
Community Champion
Community Champion

@Mamoun_issa 

thats the result on your first data sample

Безымянный.png

 

let me see please on your original data sample

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


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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.