Reference a row in another column

[pagliaci]

Hello, I hve two columns in the same table, onde of them is the index one, I want to reference the id number of that index, for example:

ID                         INDEX

8027890                   1

9809887                   2 

2938878                   3

What I need is to find out if an ID was entered twice, so I need 

If the ((ID of index 1)-(ID of index -1))=0, "SAME ID", "DIFFERENT ID"

How do I make this reference tho?

Thank a lot!!

[v-robertq-msft · Accepted solution]

Hi, @pagliaci 

According to your description, I can clearly understand your requirement, you can try to create a calculated column like this:

Column =

var _lastID=CALCULATE(MAX('Table'[ID]),FILTER(ALL('Table'),[INDEX]=EARLIER('Table'[INDEX])-1))

return

IF([ID]=_lastID,"SAME ID","DIFFERENT ID")

And you can get what you want, like this:

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jos_Woolley]

Hi,

Calculated Column:

Duplicate? =
VAR MyID = 'Table'[ID]
VAR MyIDCount =
    CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[ID] = MyID ) )
RETURN
    IF ( MyIDCount > 1, "Duplicate" )

Regards