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]

=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:

[TABLE="width: 128"]
<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]a[/TD]
[TD="class: xl65, width: 64"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65"]b[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]c[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]c[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]c[/TD]
[TD="class: xl65"]0
[/TD]
[/TR]
[TR]
[TD="class: xl65"]d[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]e[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]e[/TD]
[TD="class: xl65"]0
[/TD]
[/TR]
[TR]
[TD="class: xl65"]f[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
</tbody>[/TABLE]

What I want to achieve is this:

[TABLE="width: 128"]
<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"][TABLE="width: 128"]
<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]a[/TD]
[TD="class: xl65, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]b[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]c
[/TD]
[TD="class: xl65"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65"]c
[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]c[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]d[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]e[/TD]
[TD="class: xl65"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65"]e[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]f[/TD]
[TD="class: xl65"]1
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Thanks
Greg
[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

 

[MD610]

Do you have a unique row ID in your data?

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

=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]

Do you have a unique row ID in your data?

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

=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 ?