Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
amacmath
Frequent Visitor

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.  

 

CaseMgrNameMemberIDNoteDateNoteType
Employee 11234510/17/2022 11:00CC2
Employee 115742110/26/2022 15:15CC2
Employee 115742110/28/2022 10:00CMO_OutRch
Employee 115742110/31/2022 14:20CC2
Employee 115742110/31/2022 14:30F/F2
Employee 115742110/31/2022 14:30TELEHLTH_AV
Employee 25678910/17/2022 11:00CC2
Employee 25678910/26/2022 15:15CC2
Employee 25678911/15/2022 10:00CMO_OutRch
Employee 25678911/28/2022 14:20CC2
Employee 25678911/30/2022 14:30F/F2

 

 

6 REPLIES 6
Thomas_Daubert
Frequent Visitor

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.  

 

CaseMgrNameMemberIDNoteDateNoteType
Employee 11234510/17/2022 11:00CC2
Employee 115742110/26/2022 15:15CC2
Employee 115742110/28/2022 10:00CMO_OutRch
Employee 115742110/31/2022 14:20CC2
Employee 115742110/31/2022 14:30F/F2
Employee 115742110/31/2022 14:30TELEHLTH_AV
Employee 25678910/17/2022 11:00CC2
Employee 25678910/26/2022 15:15CC2
Employee 25678911/15/2022 10:00CMO_OutRch
Employee 25678911/28/2022 14:20CC2
Employee 25678911/30/2022 14:30F/F2
Employee 389101111/28/2022 13:00CFT/2
Employee 411121311/27/2022 12:00CFTAnRev
Employee 411121311/27/2022 12:00TELEHLTH_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"} ),

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.