Forum Discussion
How to exclude duplicate values
Check Measure and Measure2. File attached.
Hi,
Why should the answer be 110,500? Share data in a format that can be pasted in an MS Excel file.
Because the 2 I need removed are technically duplicates. Which would leave 22 units for $110,500 as the correct Totals. Thank you for your help!
| Member Number | Product | Amount Booked | FundedDate | PCL Increase/PCL New |
| 1110410 | PERSONAL CREDIT LINE | $9,500.00 | 29-Jul-24 | New PCL |
| 1239460 | PERSONAL CREDIT LINE | $10,000.00 | 22-Jul-24 | New PCL |
| 1472450 | Personal Line | $5,000.00 | 10-Jul-24 | Existing PCL Increase |
| 1596150 | PERSONAL CREDIT LINE | $14,000.00 | 11-Jul-24 | New PCL |
| 2316130 | PERSONAL CREDIT LINE | $7,000.00 | 11-Jul-24 | New PCL |
| 2588730 | Personal Line | $1,500.00 | 6-Jul-24 | Existing PCL Increase |
| 2588730 | PERSONAL CREDIT LINE | $1,500.00 | 6-Jul-24 | New PCL |
| 3215470 | PERSONAL CREDIT LINE | $4,500.00 | 31-Jul-24 | New PCL |
| 3574660 | PERSONAL CREDIT LINE | $5,000.00 | 24-Jul-24 | New PCL |
| 3697240 | PERSONAL CREDIT LINE | $4,000.00 | 30-Jul-24 | New PCL |
| 4338890 | PERSONAL CREDIT LINE | $5,000.00 | 1-Jul-24 | New PCL |
| 4562300 | PERSONAL CREDIT LINE | $5,000.00 | 24-Jul-24 | New PCL |
| 5563620 | Personal Line | $500.00 | 30-Jul-24 | Existing PCL Increase |
| 5563620 | PERSONAL CREDIT LINE | $500.00 | 30-Jul-24 | New PCL |
| 5648730 | PERSONAL CREDIT LINE | $10,000.00 | 23-Jul-24 | New PCL |
| 6546300 | PERSONAL CREDIT LINE | $1,000.00 | 18-Jul-24 | New PCL |
| 7412220 | PERSONAL CREDIT LINE | $10,000.00 | 29-Jul-24 | New PCL |
| 7536160 | PERSONAL CREDIT LINE | $2,500.00 | 2-Jul-24 | New PCL |
| 7898990 | PERSONAL CREDIT LINE | $2,000.00 | 24-Jul-24 | New PCL |
| 8523290 | Personal Line | $1,000.00 | 17-Jul-24 | Existing PCL Increase |
| 8979840 | PERSONAL CREDIT LINE | $500.00 | 30-Jul-24 | New PCL |
| 9514250 | Personal Line | $2,000.00 | 10-Jul-24 | Existing PCL Increase |
| 9632420 | PERSONAL CREDIT LINE | $10,000.00 | 31-Jul-24 | New PCL |
| 9873000 | PERSONAL CREDIT LINE | $500.00 | 24-Jul-24 | New PCL |
Check Measure and Measure2. File attached.
Hi Ashish! When I input your calclulated column and those 2 measures I get a variance with New PCL. Would EARLIER not work since my real pbix has months/years worth of previous data? Should my measures relate to the monthly date table slicer somehow? I'm not sure where to go from here.
The Consider? calculated column is only counting/summing 8 of the Member Numbers for PERSONAL CREDIT LINE:
Product Amount Booked FundedDate FundedDateMonth PCL Increase/PCL New PCL Consider? Personal Line $5,000.00 10-Jul-24 7/1/2024 0:00 Existing PCL Increase 1 Personal Line $1,000.00 17-Jul-24 7/1/2024 0:00 Existing PCL Increase 1 Personal Line $2,000.00 10-Jul-24 7/1/2024 0:00 Existing PCL Increase 1 Personal Line $1,500.00 6-Jul-24 7/1/2024 0:00 Existing PCL Increase 1 Personal Line $500.00 30-Jul-24 7/1/2024 0:00 Existing PCL Increase 1 PERSONAL CREDIT LINE $14,000.00 11-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $10,000.00 22-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $500.00 30-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $500.00 24-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $4,000.00 30-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $500.00 30-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $10,000.00 29-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $2,500.00 2-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $10,000.00 23-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $5,000.00 24-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $5,000.00 24-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $5,000.00 1-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $1,000.00 18-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $2,000.00 24-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $9,500.00 29-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $4,500.00 31-Jul-24 7/1/2024 0:00 New PCL 0 PERSONAL CREDIT LINE $7,000.00 11-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $10,000.00 31-Jul-24 7/1/2024 0:00 New PCL 1 PERSONAL CREDIT LINE $1,500.00 6-Jul-24 7/1/2024 0:00 New PCL 0 As seen in my previous screenshot and file, i got the exact result you wanted. Now i do no know what you want. Someone who understands your question will help.
Thanks Ashish!! I ended up using a slightly modified version of your EARLIER measure. I added an additional EARLIER statement so that IF the same Member # AND the same Date were posted previously, then they were exlcuded. This gave me the ability to aggregate/slice by month.
Thank you!!!!
You are welcome.