Identifying Duplicates DAX forumla not working as intended

[slow1911s]

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
)

[TABLE="width: 300"]
<tbody>[TR]
[TD]Column1
[/TD]
[TD]Column2
[/TD]
[TD]CalculatedColumn
[/TD]
[/TR]
[TR]
[TD="align: center"]Billy
[/TD]
[TD="align: center"]Yes
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]Billy
[/TD]
[TD="align: center"]Yes
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]Billy
[/TD]
[TD="align: center"]Yes
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]Billy
[/TD]
[TD="align: center"]Yes
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
</tbody>[/TABLE]

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.

Thanks in advance.

 

[miguel.escobar]

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:

=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