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
VJ1010
Frequent Visitor

Calculating rows

I am calculating duplicates using the formula
 
IsDuplicate = IF( COUNTROWS(Incident) > COUNTROWS(DISTINCT(Incident[Short Description])),TRUE(),FALSE())
 
I am able to get the duplicates in table view as below 
 
VJ1010_0-1680234334472.png

However, I want to count each row and create a card visual to display "4" which is failing. 

I am using 

Duplicate Count = COUNTROWS(FILTER(Incident,Incident[IsDuplicate]=TRUE())) 

but it shows as 

VJ1010_1-1680234530612.png

 

1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

Hi,

Here is how I would go about this:


Test data:

ValtteriN_0-1680237499301.png

Expected result = A to D are duplicates count = 4


Measures:

IsDuplicate = IF(COUNTROWS('Table (27)')>1,1,0) //since the amount of rows above 1 in a set filter context means the row is duplicate I am using this logic

TotalDuplicates = SUMX(SUMMARIZE('Table (27)','Table (27)'[Column1]),[IsDuplicate]) //Instead of booleans I am using 1 and 0 to indicate duplicates this way I can utilize SUMX to get the duplicate amount


End result:
ValtteriN_1-1680237611542.png

 

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
ValtteriN
Super User
Super User

Hi,

Here is how I would go about this:


Test data:

ValtteriN_0-1680237499301.png

Expected result = A to D are duplicates count = 4


Measures:

IsDuplicate = IF(COUNTROWS('Table (27)')>1,1,0) //since the amount of rows above 1 in a set filter context means the row is duplicate I am using this logic

TotalDuplicates = SUMX(SUMMARIZE('Table (27)','Table (27)'[Column1]),[IsDuplicate]) //Instead of booleans I am using 1 and 0 to indicate duplicates this way I can utilize SUMX to get the duplicate amount


End result:
ValtteriN_1-1680237611542.png

 

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks @ValtteriN  - you are a STAR !!!! 

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.

Top Solution Authors