Conditional Column if Identifier ever has a value in specific column

[JustinRobinette]

I have a table with multiple rows for each identifier. I want to add a conditional column if the identifier ever has a specific value in another column. In the below example, because FIN 234 contains Observation on one row, I want all rows of that FIN to include 'Observation'.

FIN          Census Event          Encounter Type          Proposed New Column
123          Admission                Emergency

123          Discharge                 Emergency

234          Admission                Emergency                        Observation

234          Transfer In                Observation                      Observation

234          Discharge                 Observation                      Observation

345          Admission                OP Surgery

345          Discharge                 OP Surgery

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[FIN]=EARLIER(Data[FIN])&&Data[Encounter Type]="Observation"))>0,"Observation",Blank())

Hope this helps.