Forum Discussion
Check if a certain value exists on the same day
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 |
6 Replies
- Thomas_Daubert
Helper I
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.
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 😉
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...
