topic Re: Reference a row in another column in DAX Commands and Tips

Reference a row in another column

pagliaci — Fri, 22 Oct 2021 10:53:10 GMT

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!!

Re: Reference a row in another column

Jos_Woolley — Fri, 22 Oct 2021 11:51:17 GMT

Hi,

Calculated Column:

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

Regards

Re: Reference a row in another column

v-robertq-msft — Fri, 29 Oct 2021 07:17:07 GMT

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.