recruitment report with a trick

[DK2006]

This is a particularly tricky and bespoke issue. I wonder if someone could figure out how to do this? I know of another organisation who has done this but they are not very forthcoming with details yet. 

I need to combine 3 reports (see below) to obtain the following information on a table/visual: 

Result Table: 

Department:	Recruitment total
Respiratory	31
Hepatology	4
Maternity	18
Diabetes	40
Haematology	20
Paediatrics	4
Renal	500
Orthodontics	1
Orthopaedics	1
Rheumatology	1
H&N	1

This report should include the total recruited for each project (project ID) but where the project involves split recruitment (ie various departments recruit to the same project), the department listed in the 'project report' is ignored in those case and each recruit is allocated to the relevant department listed in the 'acronym report' based on the prefix on the Patient ID field in the 'Patient Report'. For instance, Project ID 2265 has 7 recruits. You would normally pull a report which would say all 7 recruits are Maternity recruits. However, looking at the 'patient ID' you can see that all those recruits belong to different teams. I am told this can be done using vlook ups etc. but i'm pretty sure you require elements of code or other formulas to do this. I've seen it working but I cannot figure out how it's done. Finally, the issue might be further complicated as the prefixes sometimes have 3 or 4 letters (although I coud try to fix that so prefixes only have 3 letter) and the 'Patient ID' numbers are not (and can't be) standarised in many cases.  Some of them already contain other letters and prefixes that are irrelevant to this exercise. See all 3 reports below: 

The other reports are:

Report 1: Project Report

Project ID	Title	Department	Split recruitment	No Participants Recruited
2345	Study 1	Respiratory	NO	30
2584	Study 2	Hepatology	NO	3
2174	Study 3	Maternity	NO	10
2368	Study 4	Diabetes	NO	40
2222	Study 5	Haematology	NO	20
2265	Study 6	Maternity	YES	7
2589	Sutyd 7	Paediatrics	NO	4
2658	Study 8	Renal	NO	500

Report 2: Patient Report 

Patient ID	Project ID	Title	consented	recruited
25856	2345	Study 1	25/04/2018	26/04/2018
2ww -282	2584	Study 2	26/05/2018	26/05/2018
85698	2174	Study 3	26/04/2018	26/04/2018
C-9266	2368	Study 4	29/07/2018	29/07/2018
PAC001	2222	Study 5	30/06/2018	30/06/2018
Hep - YHS929	2265	study 6	03/05/2018	03/05/2018
Res - YHS285	2265	study 6	05/05/2018	05/05/2018
H&N - YHS298	2265	study 6	07/05/2018	07/05/2018
Rhe - YHS298	2265	study 6	09/05/2018	09/05/2018
Mat - YHS285	2265	study 6	11/05/2018	11/05/2018
Orthp - YHS484	2265	study 6	11/05/2018	11/05/2018
Orthd - YHS659	2265	study 6	13/05/2018	13/05/2018
HT/48592	2589	study 7	04/04/2018	04/05/2018
L-4802	2658	study 8	16/07/2018	30/07/2018

Report 3: Acronym report/list 

Team	Acronym
Respiratory	RES
Hepatology	HEP
Maxillofacial	H&N
Rheumatology	Rhe
Maternity	Mat
Orthopaedics	Orthp
Orthodontics	Orthd

[tex628]

Alright,

My first step would be creating a calculated column in the patient report table.

First name = LEFT(SUBSTITUTE(Patient Report[PatientID]," ","-"),SEARCH("-",SUBSTITUTE(Patient Report[PatientID]," ","-"))-1)

Next up, create a relationship between the Acronym list and the previously calculated column. (One to many)

Furthermore there needs to be a relationship between the Project Report and the Patient Report. This should be on ProjectID.

When both of these relationships has been created, create a new calculated column.

Column2 =
If(Project report[Split recruitment] = "NO";
Project report[Department];
Acronym report/list[Team]
)

When you have all the departments in the Patient report you just create a measure:

Measure = 
Countrows('Project report')

And then display the measure together with the new department column from the patient report!

Since i cant try the code this proberbly wont work, atleast not on the first try 🙂 But good luck!

Br,

Johannes

[tex628]

Hep - YHS929

2265

Would you count this individual towards only Hepatology or towards both Maternity and Hepatology?

Br,

J

[DK2006]

Thank you Tex628 for looking into this. Much appreciated!

This should only count towards Hepatology. 

KR

G