How to exclude duplicate values

[mclawler]

Hi there, I am trying to remove duplicate transactions and keep a certain type.  

I need the Customer Count to equal 22 and the Amount Sum to equal $110,500

The logic should be:

If the same "Customer Number" appears > 1 time then it needs to only Count the Customer Number with the Product "Personal Line"  &&

If the same "Customer Number" appears > 1 time then it needs to only Sum the Amount with the Product "Personal Line"

It almost needs a temp table with the duplicate data stripped out of it?  Because ultimately it then becomes separated into groups for a matrix table(the image below isn't accurate):

Here is the table data.  Basically keep the green and strip out the red because of the duplicate Customer Number:

Thank you! 

[Anonymous]

Hi ALL,
Firstly, Ashish_Mathur Thank you for your solution!
And @mclawler  ,you can use measure to write down the requirements you need to implement, and then aggregate them into a new table using summarize. 

CALCULATE(SUM(PCL_Data[Amount]),ALLEXCEPT(PCL_Data,'PCL_Data'[Customer Number])) 

We can use the rankx function to determine the first of the duplicate values as the return value,Fistly product As long as it is similar to Fistly pcl, you can change a few parameters.

FirstPCLMeasure = 
IF(
   CALCULATE(COUNTROWS('PCL_Data'), ALLEXCEPT('PCL_Data', 'PCL_Data'[Customer Number])) > 1,
   CALCULATE(
       MAX('PCL_Data'[PCL Increase/PCL New]),
       FILTER(
           'PCL_Data',
           'PCL_Data'[Customer Number] = MAX('PCL_Data'[Customer Number]) &&
           RANKX(
               FILTER('PCL_Data', 'PCL_Data'[Customer Number] = MAX('PCL_Data'[Customer Number])),
               'PCL_Data'[PCL Increase/PCL New],
               ,
               ASC
           ) = 1
       )
   ),
  MAX('PCL_Data'[PCL Increase/PCL New])
)

Finally in the aggregation by summarize to form a new table, the design of their own matrix

Table = 
SUMMARIZE('PCL_Data','PCL_Data'[Customer Number],"Amount",'PCL_Data'[measure],"A",'PCL_Data'[FirstPCLMeasure],"B",'PCL_Data'[FirstProductMeasure])

If you still have questions, check out my pbix file, I hope it helps!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[mclawler]

H! Those measures aren't working for what I'm trying to accomplish.  I have created 2 measures that get me the correct grand totals, but the row items are still incorrect.

The Existing PCL Increase is correct = 5 units for $10,000

The New PCL is incorrect, should = 17 units for $102,500

I have created a pbix for testing here : PCL Testing.pbix

If the SAME Member Number appears for both Existing and New, I need the measure to strip out the data rows pertaining to the New PCL redundant Member Number.  This visual might make it clearer.  Include all black and green(duplicate member number), exclude the red:

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

[Ashish_Mathur]

Hi,

Why should the answer be 110,500?  Share data in a format that can be pasted in an MS Excel file.

[mclawler]

H! I have created 2 measures that get me the correct grand totals, but the row items are still incorrect.

The Existing PCL Increase is correct = 5 units for $10,000

The New PCL is incorrect, should = 17 units for $102,500

I have created a pbix for testing here : PCL Testing.pbix

If the SAME Member Number appears for both Existing and New, I need the measure to strip out the data rows pertaining to the New PCL redundant Member Number.  This visual might make it clearer.  Include all black and green(duplicate member number), exclude the red:

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

[mclawler]

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

[Ashish_Mathur · Accepted solution]

Check Measure and Measure2.  File attached.

[mclawler]

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!!!! 

[Ashish_Mathur]

You are welcome.

[mclawler]

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

[Ashish_Mathur]

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.

[mclawler]

Almost there.  How do I get your Calculated column to dynamically calculate based on the Slicer(date table) selection?

Consider? = if(CALCULATE(COUNTROWS('PwBi LoanData'),FILTER('PwBi LoanData','PwBi LoanData'[Member Number]=EARLIER('PwBi LoanData'[Member Number])))>1,if('PwBi LoanData'[Product] = "Personal Line",1,0),1)

I keep reading about SELECTEDVALUE but I can't get it to work.  

Basically, I want that calculated column to "refresh" dynamically based on the Month chosen within my slicer.  This way it won't take into calculation the last 50 years of data, only the selected month at any given time.

[Ashish_Mathur]

PBI file attached.

Hope this helps.