# Identifying Duplicates DAX forumla not working as intended

#### slow1911s

##### New Member
This is the formula I'm using in a calculated column:

=IF(
CALCULATE(COUNTROWS(Table1),
FILTER(Table1,
Table1[Column1]=EARLIER(Table1[Column1]) &&
Table1[Column2]=EARLIER(Table1[Column2])
)
)>1,0,1
)

 Column1 Column2 CalculatedColumn Billy Yes 1 Billy Yes 1 Billy Yes 1 Billy Yes 1

<tbody>
</tbody>

This isn't what expect. I expect that this would put a 0 in cells 2-4 in the calculated column. If I was using basic Excel formulas it would look like this: =IF(SUMPRODUCT(\$A2:A2=A2)*(\$B2:B2=B2))>1,0,1)

I also tried EARLIEST to see if that changed things - no. I'm just trying to find unique instances of various combinations of data.

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### miguel.escobar

##### Active Member
Hi!

You might want to take a look at defining a unique ID per row otherwise there's no other way that Powerpivot can tell under what context what row comes first. It'll throw you something like the count of total times that the combination appears with something like this:
Code:
``````=COUNTAX( FILTER( Table,
EARLIER([Column1])=[Column1] &&
[Column2] =EARLIER([Column2])
),
[Column1]
)``````

Which in this case is 4. In regular Excel the A1 reference style is extremely useful but in Powerpivot that A1 cell reference is gone and now we need to talk to the Engine using DAX, which is a language that works with Tables only.

Hope this helps

Replies
5
Views
878
Replies
6
Views
857
Replies
4
Views
31K
Replies
0
Views
2K
Replies
6
Views
1K

1,137,349
Messages
5,680,965
Members
419,946
Latest member
Trickay

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back