# DAX Formula to Count Duplicates



## phillipsg

Hello,

I have a formula to count duplicate rows in Excel.  It marks the first instance of a duplicate with a 1 and each duplicate thereafter with a 0.

=IF(COUNTIF($B$2:$B2, B2)>1,0,1)

What I'm trying to write is an equivalent DAX formula that will do the same thing, but I'm not sure how to go about it.  I'm almost certain that I need to combine the CALCULATE and COUNTROWS functions to get what I need, but I can't seem to nail down the correct syntax.  Any help would be appreciated.

Thanks!


----------



## MD610

Code:
__


=IF(
          CALCULATE(
                 COUNTA(TableName[ColumnName]),
                 FILTER(TableName, TableName[ColumnName] = EARLIER(TableName[ColumnName]))
           )>1, 
           0,
           1
     )


This should do it.  The =IF() pattern is the same as regular Excel.  Replace TableName with your table's name and ColumnName with the column that you want to check for dups.

Basically this is saying..."count the current row value everytime the same value appears earlier in the column"


----------



## phillipsg

Mike - thanks for taking the time to respond.  That's very close to what   I need.   In fact, I found a similar formula online that uses the   COUNTROWS function instead of COUNTA and achieves the same result.  The   only problem I haven't been able to resolve is getting each initial  duplicate flagged with a 1 and each subsequent dupe with a 0.  

I'm getting this:


a1
b1c0c0c0
d1e0e0
f1

<colgroup><col style="width:48pt" span="2" width="64">  </colgroup><tbody>

 </tbody>
What I want to achieve is this:



a1b1c
1
c
0c0d1e1
e0f1


<colgroup><col style="width:48pt" span="2" width="64">  </colgroup><tbody>

 </tbody>Thanks
Greg


<colgroup><col style="width:48pt" span="2" width="64">  </colgroup><tbody>

</tbody>


----------



## MD610

Do you have a unique row ID in your data?

If so, a second Calculated Column like this should do the trick:



		Code:
__


=IF(
       TableName[CalculatedColumn1] = 0 &&
       TableName[UniqueRowID] = CALCULATE(
                                MIN(TableName[UniqueRowID]),
                                ALLEXCEPT(TableName, TableName[ColumnName])
                          ),
     1,
     0
  )


CalculatedColumn1 is the first Calc Column from above and ColumnName is the column with dups again.


----------



## pinguindell

MD610 said:


> Do you have a unique row ID in your data?
> 
> If so, a second Calculated Column like this should do the trick:
> 
> 
> 
> Code:
> __
> 
> 
> =IF(
> TableName[CalculatedColumn1] = 0 &&
> TableName[UniqueRowID] = CALCULATE(
> MIN(TableName[UniqueRowID]),
> ALLEXCEPT(TableName, TableName[ColumnName])
> ),
> 1,
> 0
> )
> 
> 
> CalculatedColumn1 is the first Calc Column from above and ColumnName is the column with dups again.



and what mean field TableName[UniqueRowID] in your formula above, from where did you get it ?


----------

