Locate patients with only one visit

[anwilkins]

I need to create a follow-up with patient roster that displays all patients within a date range who have only had 1 visit / appointment.

My hope was to create a calculated column that displayed the number of visits for each patient then create another column that was a is a flag for people with only 1 visit. Here is the dax I'm using to create the first column...its NOT working...it displays all visits per patient

*PatientsWithOneVisit = CALCULATE(DISTINCTCOUNT(MERGE_PatientData[patientid]),

FILTER(VALUES('MERGE_PatientData'[service date]),CALCULATE(COUNTROWS('MERGE_PatientData')=1)))

The end goal is a tabular roster

Patient ID     Name     Last Service Date     Visit Count     Red Flag (for 1 visit)

00110011     Morris      2/01/2022                   6                  

00220033     Jones      12/01/2021                  1                         X

00110044     Stevens   10/01/2022                 3                  

00220022     Albre      04/01/2021                  1                         X

[anwilkins · Accepted solution]

Unfortunately as this is medical data I cannot share the file. However, a work around has been put into place so I will mark this as closed.

Thanks

[v-shex-msft]

Hi @anwilkins,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Notice: please remove sensitive data before sharing.

Regards,

Xiaoxin Sheng

[anwilkins]

Hi Datavisuserpbi, I tried this and the results are blank

*Count v4 = IF(CALCULATE(SUM(MERGE_PatientData[Service Date]), GROUPBY(MERGE_PatientData,MERGE_PatientData[PatientId])) = 1, "X","")

[Datavizuserpbi]

Hello,

Try this:

column= IF(CALCULATE(SUM('Table'[Visit Count]),GROUPBY('Table','Table'[Name]))=1,"X"," ")

!!

[PaulDBrown]

Can you please post sample data of the fact table or a sample PBIX file (hopefully...)?