Forum Discussion
Working with Duplicate Rows
I am trying to create a report to reconcile charges with payments. This is a healthcare environment that generates 2-3 charges for each claim, depending on the number of insurances. Unfortunately, the billing system does not assign a unique charge number or indicate who the charge is going to. Here is an example:
Claim Name Amount
| 12345 | John Smith | 250 |
| 12345 | John Smith | 250 |
| 12345 | John Smith | 250 |
| 45678 | Bob Jones | 300 |
| 45678 | Bob Jones | 300 |
| 45678 | Bob Jones | 300 |
In this example, each patient has a Primary Insurance, a Secondary Insurance, and a Co-pay. Each row represents a claim to each of those payment sources.
On the payment side, I need to reconcile how many payments have been received and if there is an outstanding balance
Claim Name Payment
| 12345 | John Smith | 100 |
| 12345 | John Smith | 100 |
| 12345 | John Smith | 40 |
| 45678 | Bob Jones | 100 |
| 45678 | Bob Jones | 100 |
Using this sample data, John Smith/12345 has an outstanding balance of $10, while Bob Jones/45678 has an outstanding balance of $100.
I would like my final table to look something like this:
Thank you in advance for any advice
Hey aflintdepm ,
You will need some DAX measures and a correct data model. See the attached pbix example.
4 Replies
Hey aflintdepm ,
You will need some DAX measures and a correct data model. See the attached pbix example.Thank you for the example file- this was very helpful. I just have a couple of questions
First- am I correct in my understanding that you created the third table to remove the duplicates and give a single lookup table?
Second- could you please explain the structure of the Charge measure you created? I don't understand the nested CALCULATE(VALUES without a filter expression)
Charge =SUMX(VALUES(Charge[Claim]),CALCULATE(VALUES(Charge[Amount])))I'm going to work to recreate this on the larger datatables that I am working with. Thank youHey aflintdepm !
1. Yes. I put the data into a star schema model perspective
2. TheCALCULATEfunction changes the context of the calculation. Inside theCALCULATE, we useVALUES(Charge[Claim])to get a table with the unique values of the "Claim" column. The absence of a filter expression inCALCULATEmeans it operates in the current filter context established by the outerSUMX.
Hi,
PBI file attached.
Hope this helps.
