cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

recruitment report with a trick

 

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
Respiratory31
Hepatology4
Maternity18
Diabetes40
Haematology20
Paediatrics 4
Renal500
Orthodontics1
Orthopaedics1
Rheumatology1
H&N1

 

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 IDTitle DepartmentSplit recruitment No Participants Recruited 
2345Study 1RespiratoryNO30
2584Study 2HepatologyNO3
2174Study 3MaternityNO10
2368Study 4 DiabetesNO40
2222Study 5HaematologyNO20
2265Study 6MaternityYES7
2589Sutyd 7Paediatrics NO4
2658Study 8RenalNO500

 

Report 2: Patient Report 

 

Patient IDProject ID Title consentedrecruited
258562345Study 125/04/201826/04/2018
2ww -2822584Study 226/05/201826/05/2018
856982174Study 326/04/201826/04/2018
C-92662368Study 4 29/07/201829/07/2018
PAC0012222Study 530/06/201830/06/2018
Hep - YHS9292265study 603/05/201803/05/2018
Res - YHS2852265study 605/05/201805/05/2018
H&N - YHS2982265study 607/05/201807/05/2018
Rhe - YHS2982265study 609/05/201809/05/2018
Mat - YHS2852265study 611/05/201811/05/2018
Orthp - YHS4842265study 611/05/201811/05/2018
Orthd - YHS6592265study 613/05/201813/05/2018
HT/485922589study 704/04/201804/05/2018
L-48022658study 816/07/201830/07/2018

 

Report 3: Acronym report/list 

 

TeamAcronym
RespiratoryRES
HepatologyHEP
Maxillofacial H&N
RheumatologyRhe
MaternityMat
OrthopaedicsOrthp
OrthodonticsOrthd
3 REPLIES 3
Highlighted
Super User II
Super User II

Re: recruitment report with a trick

 

Hep - YHS9292265


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

 

Br,

J


Connect on LinkedIn
Highlighted
New Member

Re: recruitment report with a trick

Thank you Tex628 for looking into this. Much appreciated!

 

This should only count towards Hepatology. 

 

KR

G

 

Highlighted
Super User II
Super User II

Re: recruitment report with a trick

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


Connect on LinkedIn

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors