Reply
Regular Visitor
Posts: 16
Registered: ‎01-28-2018
Accepted Solution

I want to show patients whose difference in first and second dose of vaccination is more than 30 day

[ Edited ]

i have a patient vaccination info table with dose numbers in a column. i want to display those medical record numbers(MRN) for which the difference in first and second dose is more than 30 days.

 

Attached image shows the table structureUntitled.png


Accepted Solutions
Regular Visitor
Posts: 28
Registered: ‎04-20-2017

Re: I want to show patients whose difference in first and second dose of vaccination is more than 30

Hi,

 

I don't know if this is the best way but you could create a new table with GROUPBY where you extract the first date and the second date.

 

Here is the code

 

 

Delta Dose = 
GROUPBY (
   UNION(
      GROUPBY(
        FILTER('Nursing Vaccination';'Nursing Vaccination'[Dose #]="First");
        'Nursing Vaccination'[Patient Name];
        "First Date";MAXX(CURRENTGROUP();'Nursing Vaccination'[Immunization Date]); 
        "Second Date";MINX(CURRENTGROUP();0))
       ;
      GROUPBY(
        FILTER('Nursing Vaccination';'Nursing Vaccination'[Dose #]="Second");
        'Nursing Vaccination'[Patient Name];
        "First Date";MINX(CURRENTGROUP();0);
        "Second Date";MINX(CURRENTGROUP();'Nursing Vaccination'[Immunization Date]) )
   );
   'Nursing Vaccination'[Patient Name];
   "First Date";MAXX(CURRENTGROUP();[First Date]);
   "Second Date";MAXX(CURRENTGROUP();[Second Date])

)

 

You can then create a relationship with this new table and the data table to extract all the lines with more than 30 days.

 

With a file :

https://1drv.ms/u/s!AhxEamX-j2-mgmdo5os_0LHJ8qmZ

 

Hope this will help,

View solution in original post


All Replies
Regular Visitor
Posts: 28
Registered: ‎04-20-2017

Re: I want to show patients whose difference in first and second dose of vaccination is more than 30

Hi,

 

I don't know if this is the best way but you could create a new table with GROUPBY where you extract the first date and the second date.

 

Here is the code

 

 

Delta Dose = 
GROUPBY (
   UNION(
      GROUPBY(
        FILTER('Nursing Vaccination';'Nursing Vaccination'[Dose #]="First");
        'Nursing Vaccination'[Patient Name];
        "First Date";MAXX(CURRENTGROUP();'Nursing Vaccination'[Immunization Date]); 
        "Second Date";MINX(CURRENTGROUP();0))
       ;
      GROUPBY(
        FILTER('Nursing Vaccination';'Nursing Vaccination'[Dose #]="Second");
        'Nursing Vaccination'[Patient Name];
        "First Date";MINX(CURRENTGROUP();0);
        "Second Date";MINX(CURRENTGROUP();'Nursing Vaccination'[Immunization Date]) )
   );
   'Nursing Vaccination'[Patient Name];
   "First Date";MAXX(CURRENTGROUP();[First Date]);
   "Second Date";MAXX(CURRENTGROUP();[Second Date])

)

 

You can then create a relationship with this new table and the data table to extract all the lines with more than 30 days.

 

With a file :

https://1drv.ms/u/s!AhxEamX-j2-mgmdo5os_0LHJ8qmZ

 

Hope this will help,

Regular Visitor
Posts: 16
Registered: ‎01-28-2018

Re: I want to show patients whose difference in first and second dose of vaccination is more than 30

Worked like a charm. THANK YOU SO MUCH.

Highlighted
Member
Posts: 56
Registered: ‎01-21-2018

Re: I want to show patients whose difference in first and second dose of vaccination is more than 30

@rabiafarooq

I used my own sample dataset and tried it. Below is data I used -

 

SamplePatients.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

After this, I created 3 columns as below -

SecondVisitDate = CALCULATE ( MAXX( FILTER(Patients,Patients[Visit#]="Second"),Patients[Date]) , ALLEXCEPT ( Patients, Patients[Name] ) )

 

FirstVisitDate = CALCULATE ( MAXX( FILTER(Patients,Patients[Visit#]="First"),Patients[Date]) , ALLEXCEPT ( Patients, Patients[Name] ) )

 

# of Days = DATEDIFF(Patients[SecondVisitDate],Patients[FirstVisitDate],DAY)

 

The last column above "# of Days" will give a number which is less than -30 in case patient had a difference of more than 30 days between first and second visit.

 

Regards

Regular Visitor
Posts: 16
Registered: ‎01-28-2018

Re: I want to show patients whose difference in first and second dose of vaccination is more than 30

[ Edited ]

Untitled.pngUntitled.png

@vmakhijaSo i am getting something like this. what does the very high negative and positve values mean?

Member
Posts: 56
Registered: ‎01-21-2018

Re: I want to show patients whose difference in first and second dose of vaccination is more than 30

@rabiafarooq

Do you mind sharing your measure?

Ideally, as SecondVisitDate is always greater than (i.e. after) FirstVisitDate, so it should not give you positive values in the measure.

 

Regards

Regular Visitor
Posts: 27
Registered: ‎03-20-2017

Re: I want to show patients whose difference in first and second dose of vaccination is more than 30

Are you interested in the difference between the first and last dates, or between the first and second dates? MIN and MAX would give you the former, not the latter. 

Regular Visitor
Posts: 16
Registered: ‎01-28-2018

Re: I want to show patients whose difference in first and second dose of vaccination is more than 30

@vmakhijaHere is the measure. Also i am getting wrong output dates. please see the 2nd image. its for the same patient.Untitled.pngUntitled2.png

Regular Visitor
Posts: 16
Registered: ‎01-28-2018

Re: I want to show patients whose difference in first and second dose of vaccination is more than 30

@corbusier difference between screening date and first date, first date and second date, second date and third date.

Regular Visitor
Posts: 16
Registered: ‎01-28-2018

Re: I want to show patients whose difference in first and second dose of vaccination is more than 30

@Saloni what if we have have four dates? and we want difference between screening to first dose, first to second dose  , second to third dose?