Check if a certain value exists on the same day

[amacmath]

I have a set of data (sample below) that tracks entry of progress notes by employees into individual youth records. The 'MemberID' is a unique identifier for each youth. If an employee meets with a child and the meeting took place in person, they will just enter a NoteType 'F/F2'. If they meet with the child via telehealth, they have to enter two notes. One NoteType 'F/F2' and one NoteType on the same Day/time 'TELEHLTH_AV'. 

I need to figure out how to show what visits took place via telehealth and what visits took place in person by looking to see if there is a 'TELEHLTH_AV' Note on the same day as a 'F/F2' note.

In the example below - Employee 1 had a visit that took place via telehealth and Employee 2 had an in-person visit. I need to be able to show a visual with a list of kids who ONLY had a telehealth visit.  

CaseMgrName	MemberID	NoteDate	NoteType
Employee 1	12345	10/17/2022 11:00	CC2
Employee 1	157421	10/26/2022 15:15	CC2
Employee 1	157421	10/28/2022 10:00	CMO_OutRch
Employee 1	157421	10/31/2022 14:20	CC2
Employee 1	157421	10/31/2022 14:30	F/F2
Employee 1	157421	10/31/2022 14:30	TELEHLTH_AV
Employee 2	56789	10/17/2022 11:00	CC2
Employee 2	56789	10/26/2022 15:15	CC2
Employee 2	56789	11/15/2022 10:00	CMO_OutRch
Employee 2	56789	11/28/2022 14:20	CC2
Employee 2	56789	11/30/2022 14:30	F/F2

[Thomas_Daubert]

Hello,

You can create a new table : 

Table1 = 
SUMMARIZE(
    FILTER(Sheet1,Sheet1[NoteType] in {"F/F2", "TELEHLTH_AV"} ),
    Sheet1[CaseMgrName], Sheet1[MemberID], Sheet1[NoteDate]
    , "nb",COUNT(Sheet1[NoteType])
)

Then Add column : 

Column = If(Table1[nb] = 2, "TELEHEALTH", "ONE PERSON")

First you summarize on filtered table to find where is 2 lines by day, then a column calculate the text you want.  

[amacmath]

This isnt working - to clarify, there are plenty of other notes in there for each kid...So there may be 20 notes in for one day (or one month). I need to know if there is a F/F2, C/F2, or CFTAnRev note entered on the SAME day/time as a TELEHLTH_AV. Period

It doesnt seem to be capturing that at all...

[amacmath]

This somewhat worked... - unfortunately the second part of this (which I didnt realize at first) is that there are a few different types of Notes that would need to be looked for...So, you will see in the table below I need to be looking to see if there is a 'TELEHLTH_AV' Note on the same day as a 'F/F2', 'CFT/2', or 'CFTAnRev' note type. Is there a way using your solution above to add additional note types?

In the example below - Employee 1 had a visit that took place via telehealth,  Employee 2 had an in-person visit, employee 3 had an in-person visit, and employee 4 had a telehealth. I need to be able to show a visual with a list of kids who ONLY had a telehealth visit.  

CaseMgrName	MemberID	NoteDate	NoteType
Employee 1	12345	10/17/2022 11:00	CC2
Employee 1	157421	10/26/2022 15:15	CC2
Employee 1	157421	10/28/2022 10:00	CMO_OutRch
Employee 1	157421	10/31/2022 14:20	CC2
Employee 1	157421	10/31/2022 14:30	F/F2
Employee 1	157421	10/31/2022 14:30	TELEHLTH_AV
Employee 2	56789	10/17/2022 11:00	CC2
Employee 2	56789	10/26/2022 15:15	CC2
Employee 2	56789	11/15/2022 10:00	CMO_OutRch
Employee 2	56789	11/28/2022 14:20	CC2
Employee 2	56789	11/30/2022 14:30	F/F2
Employee 3	891011	11/28/2022 13:00	CFT/2
Employee 4	111213	11/27/2022 12:00	CFTAnRev
Employee 4	111213	11/27/2022 12:00	TELEHLTH_AV

[Thomas_Daubert]

Hello,

If the condition, TELEHEALTH is true when there is 2 raws with certain values, you can simply add all values concerned in the filter : 

FILTER(Sheet1,Sheet1[NoteType] in {"F/F2", "TELEHLTH_AV"} ),

Then, by using a filter on the last calculated column you will obtain the list you are looking for.

Let me know if it works 😉

[amacmath]

So would I do, 

FILTER(Sheet1,Sheet1[NoteType] in {"F/F2", "CFT/2", "CFTAnRev", "TELEHLTH_AV"} ),

 or 

FILTER(Sheet1,Sheet1[NoteType] in {"F/F2", "TELEHLTH_AV"},FILTER(Sheet1,Sheet1[NoteType] in {"CFT/2", "TELEHLTH_AV"},FILTER(Sheet1,Sheet1[NoteType] in {"CFTAnRev", "TELEHLTH_AV"} )

I need it to return "TELEHEALTH" if there are any of those three note types (F/F2, CFT/2, CFTAnRev) PLUS a TELEHLTH_AV note on the same day. 

[Thomas_Daubert]

First one, 

FILTER(Sheet1,Sheet1[NoteType] in {"F/F2", "CFT/2", "CFTAnRev", "TELEHLTH_AV"} ),