topic Calculating rows in DAX Commands and Tips

Calculating rows

Anonymous — Fri, 31 Mar 2023 03:49:10 GMT

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

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

Re: Calculating rows

ValtteriN — Fri, 31 Mar 2023 04:40:20 GMT

Hi,

Here is how I would go about this:

Test data:

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:

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/

Re: Calculating rows

Anonymous — Fri, 31 Mar 2023 05:05:57 GMT

Thanks ValtteriN - you are a STAR !!!!