Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
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 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...
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 |
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 😉
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.
First one,
FILTER(Sheet1,Sheet1[NoteType] in {"F/F2", "CFT/2", "CFTAnRev", "TELEHLTH_AV"} ),
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
95 | |
88 | |
70 |
User | Count |
---|---|
165 | |
131 | |
129 | |
102 | |
98 |