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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors