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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Don't know how to start with this duplicate values problem

help.pngWhen I am trying to sum up the count I get 2 for John on Feb 18th but actually want that value to be 1. Is there a way to make it so that the first date comes out as 1 on the NoDupCount column and then 0 for any of the other counts that fall on that date. Looking for a calculated column solution and have looked everywere online and couldn't figure it out.

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous

 

This is one of the way to do this:

 

1.Create a indx column.

2. Creata Your new column with this formula: NoDUpCount = IF( CALCULATE(MIN(Table1[Index]), FILTER(Duplicate,Table1[Name]= EARLIER(Table1[Name])  && Table1[Date]= EARLIER(Table1[Date]))) = Table1[Index], Table1[Count],0)

 

NoDupCnt.PNG

Thanks

Raj

 

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

 

Why do you want a calculated column solution.  With a measure, it is a simple solution.

 

=DISTINCTCOUNT(Data[Count])

or

=MIN(Data[Count])

 

Drag Name and Date to the row labels of your visual.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Anonymous

 

This is one of the way to do this:

 

1.Create a indx column.

2. Creata Your new column with this formula: NoDUpCount = IF( CALCULATE(MIN(Table1[Index]), FILTER(Duplicate,Table1[Name]= EARLIER(Table1[Name])  && Table1[Date]= EARLIER(Table1[Date]))) = Table1[Index], Table1[Count],0)

 

NoDupCnt.PNG

Thanks

Raj

 

Anonymous
Not applicable

Thank you for the answer. I think it has answered my question for now so I will accept this as my solution but I will keep testing to see if the data turned out how I wanted. Thank you again for helping me out!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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