How to determine if a certain patient type is coming back

[Reine]

Hi everyone - I've run into a problem I can't figure out.  I have the attached (sample) table that shows all appointments in our clinic.  From it I am trying to determine if a patient that had an iLasik appointment came back in to see us for another kind of appointment afterwards.  For example, patient #418 from the table did come back for other appointment types after they had and an iLasik appointment, but patient #2110 has not. 

In the end, I want to know:

1. The number of people that had ilasik appointments by month

2. Of those people each month, how many came back later and to which department

PatientID	AppointmentDate	AppointmentDepartment	AppointmentPurpose
245	08/01/18	4	Dry Eyes
245	02/19/19	4	OD Tear Science Exam
418	02/02/18	4	OD Tear Science Exam
418	12/20/18	4	Refractive Consult
418	03/01/19	4	iLasik
418	06/20/19	4	Dry Eyes
418	01/07/20	3	Earlobe reconstruction - Bilateral
770	05/03/18	4	Elective Self Pay Exam
834	07/16/18	2	Microderm- Express
834	10/31/19	4	Contact Lenses
931	03/09/18	4	Vision Exam
1021	10/04/18	4	Dry Eyes
1021	12/04/18	4	Meibomian Gland Expression
1021	06/10/19	4	iLasik
1021	07/10/19	3	Ectropion Repair LT
1061	01/25/19	2	Juvederm
1243	09/19/18	2	Electrolysis, Lid Eval
1243	11/02/18	2	Electrolysis
1243	09/06/19	4	Refractive Consult
1353	08/29/19	4	Refraction Only
1360	04/18/19	3	Lid Eval
1421	06/20/19	4	iLasik
1544	04/19/18	3	Ectropion, Ectropion Repair LT w/Medial Spindal, Entropion
1544	06/21/18	3	Ectropion Repair LT w/Medial Spindal
1559	04/02/18	3	Lid Eval
1698	01/07/19	1	Glasses
1738	05/10/19	4	iLasik
1999	03/08/19	4	Dry Eyes
2039	07/18/19	4	Refraction Only
2110	01/11/18	3	Lid Eval
2110	06/28/18	1	Contact Lenses
2110	10/04/18	4	Dry Eyes
2110	10/16/18	2	HALO
2110	06/20/19	4	iLasik
2326	01/28/20	2	Phototherapy- Face
2329	06/28/19	4	Dry Eyes
2352	02/26/18	2	LHR- Lip & Chin
2352	04/09/18	2	LHR- Lip & Chin
2470	07/16/19	3	Lid Eval
2897	07/15/19	4	Sudden Vision Loss
2904	09/19/19	4	Dry Eyes
3077	02/23/18	3	Browplasty
3077	10/05/18	4	iLasik
3077	12/11/18	2	Level 2 HALO - medium face
3077	06/11/19	4	Refraction Only
3119	12/11/18	2	Phototherapy- Face
3146	03/16/18	2	Juvederm
3146	12/10/18	2	SBP1dayface
3205	02/08/18	3	Ectropion
3205	05/29/18	3	Ectropion
3221	06/30/19	4	iLasik
3221	07/30/19	2	Visia- Front View
3221	11/13/19	2	Phototherapy- Face
3221	11/27/19	2	Illumination Express-Microderm
3221	12/11/19	2	Phototherapy- Face
3221	03/20/20	2	Phototherapy- Face
3236	04/19/19	4	ilasik
3249	11/06/19	4	Dry Eyes
3277	02/13/18	4	PRK (Photorefractive Keratectomy)
3277	07/09/19	4	Refraction Only
3409	10/17/19	3	Skin Cancer Surveillance
3453	09/24/18	4	Discharge
3461	01/24/18	4	Dry Eyes
3583	05/24/18	3	Lid Eval
3583	07/10/18	3	Browplasty, Endoscopic
3583	01/12/19	4	iLasik
3849	04/12/18	2	Latisse
3849	07/06/18	4	Dry Eyes
3849	10/01/18	4	OD Tear Science Exam
3849	11/30/18	4	Lid Eval
4030	06/29/18	4	OD Tear Science Exam
4030	05/24/19	4	Dry Eyes
4030	11/15/19	4	Dry Eyes
4035	02/14/18	4	Refraction Only
4059	07/02/19	4	iLasik
4059	08/09/19	2	Juvederm
4059	08/27/19	2	Phototherapy- Spot Treat
4059	12/10/19	2	Phototherapy- Face
4093	03/19/18	3	Lid Eval
4105	03/12/18	2	Cosmetic Consult, Lesion excision/Biopsy
4105	03/30/18	3	Earlobe reconstruction - Bilateral
4105	05/10/19	2	Juvederm
4105	06/20/19	2	Juvederm
4105	07/17/19	2	Dermaplaning

I hope this makes sense - thank you for the help!

[V-lianl-msft]

Hi @Reine ,

First you need to create an index column in edit queries.

Everything is in pbix, please refer to this pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Reine]

Thank you for your reply.  I apologize, I am still a new PBI user and though I can grasp basic DAX, I'm afraid that I can't quite understand what you've done here or see how it gives me the info I need.

It looks like the "next_department" shows the first department they had an appointment with after their iLasik appointment, correct?  

The other columns, ilasik_back and ilasik_month, I don't understand at all what they are showing.  What do the numbers in those columns refer to?

[V-lianl-msft]

Hi @Reine ,

I'm sorry that I didn't explain it clearly.

"ilasik_month" is the number of people that had ilasik appointments by month.

"ilasik_back" is the number of people who came back later.

"next_department" is back to which department.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Is this what you want?  On the row labels, I have applied a filter on ILasik and on the column labels i have applied a filter on non-Ilasik.  Please confirm whether this answers your question.

[Reine]

Hmm.  I would need to do the columns as departments rather than purpose because there are hundreds of purposes but somethign like this might work.  How do you filter on row labels and column labels seperately like this?

[Reine]

@Ashish_Mathur  hi -I think something like this might work, but I don't know how to filter like you've done.  Can you share how that is done?  Thank you 🙂

[Ashish_Mathur]

Hi,

You may download my solution workbook from here.

Hope this helps.

[Reine]

Thank you!  I will give this a try next week and let you know if it's the solution.