Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rabiafarooq
Helper I
Helper I

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

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

1 ACCEPTED SOLUTION
Salonic
Resolver I
Resolver I

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

9 REPLIES 9
corbusier
Advocate IV
Advocate IV

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. 

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

Salonic
Resolver I
Resolver I

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,

@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?

Worked like a charm. THANK YOU SO MUCH.

@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

Untitled.pngUntitled.png

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

@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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.