If Contents of one cell is the same as another based on another column

[johndolan2]

Hi - Can you help ... I am trying to add a column that shows whether both components of a service ID are in the same or different states. ...  If they are in the same state I would like the result in the new column (Requested Result) to show "intra state" and if not then show "inter state".

*** There are 2 components to each Service ID. So in the example of service ID "123" since both of the A location and Z location components are in Florida my requested result would be Intra state.

I have a few thousand rows of these.

Thanks, John

Component	State	Service ID	Requested Result
A Location	Florida	123	Intra state
Z Location	Florida	123	Intra state
A Location	New York	456	Inter state
Z Location	Iowa	456	Inter state

[johndolan2]

Amit - You are unbelievable .... You just answered 2 of my questions in a matter of minutes!!!

Thank you so very, very much!

John

[amitchandak · Accepted solution]

@johndolan2 , Try a new column like

new column =
var _cnt = calculate(distinctCOUNT(Table[State]), filter(Table, [Service ID] = earlier([Service ID])))
return
if(_cnt >1, "Inter state", "Intra state")