Forum Discussion
Conditional Column if Identifier ever has a value in specific column
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
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.
1 Reply
- Ashish_MathurSuper User
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.