Hello,
I am going to describe my power pivot problem as I can't upload the data.
I have a power pivot model that pulls fac tables from two databases
They are pretty simple, the output is just like this
Database 1: Charges, Medical Record #, Date of Service
Database 2: Medical Record #, Discharge Date, Inpatient or Outpatient Index
Dim table (Bridges the two by Medical Record #) is just a distinct list of Medical record # in either database.
I want to be able to show the Database 1 charges by Medical record number when the following conditions are true
Scenario 1: When Date of Service - Discharge Date <= 14 days and Inpatient or Outpatient Index = I then return Database 1 charges call Column 'Clinic Appt in time'
Scenario 2: When Date of Service - Discharge Date between 15 and 30 days and Inpatient or Outpatient Index = I then return Database 1 charges call Column 'Missed Opportunity'
Any other Scenario is outside of the dataset I care about which includes all outpatient visits and any time that the date of service - discharge date is > 30 days.
My problem is that a medical record # (represents one patient) could be discharged from the hospital 20 times and have 10 more service dates at a clinic. Medical record # is the only identifier in which the two databases are linked. I can modify the queries in either database but I don't see what could make the result i want feasible...
Any insights would be appreciated.
Thanks
I am going to describe my power pivot problem as I can't upload the data.
I have a power pivot model that pulls fac tables from two databases
They are pretty simple, the output is just like this
Database 1: Charges, Medical Record #, Date of Service
Database 2: Medical Record #, Discharge Date, Inpatient or Outpatient Index
Dim table (Bridges the two by Medical Record #) is just a distinct list of Medical record # in either database.
I want to be able to show the Database 1 charges by Medical record number when the following conditions are true
Scenario 1: When Date of Service - Discharge Date <= 14 days and Inpatient or Outpatient Index = I then return Database 1 charges call Column 'Clinic Appt in time'
Scenario 2: When Date of Service - Discharge Date between 15 and 30 days and Inpatient or Outpatient Index = I then return Database 1 charges call Column 'Missed Opportunity'
Any other Scenario is outside of the dataset I care about which includes all outpatient visits and any time that the date of service - discharge date is > 30 days.
My problem is that a medical record # (represents one patient) could be discharged from the hospital 20 times and have 10 more service dates at a clinic. Medical record # is the only identifier in which the two databases are linked. I can modify the queries in either database but I don't see what could make the result i want feasible...
Any insights would be appreciated.
Thanks