I think you should do a modelling effort before we go further … there are over 1 million rows in your [Medico] table and there is much repeating data. Your main fact table should have one row per event (visit?), and I imagine after you extract all the patient-specific info into its own [Patients] table things will look much better. Also, it seems that there are multiple records per event: for example, although I guess “Chart Number” and “Patient ID” are both meant to refer to a specific patient, for “Chart Number” = 2099 and Patient ID = 87663 there are 15 records, while my first try at putting this patient into a table and counting her/his visits gave 3 visits…
There are also many records where the visit appointment date is about 7 years before the appointment created date (I stopped counting at over 8000 with appointments in 2005 and creation dates in 2012).
So, I’d recommend a new model before proceeding. The initial tables I can see are:
- [Dates] - with 1 record per date (you’ve already got this)
- [Patients] - extract and remove as much information from the [Medico] table as you can, with 1 record per patient
- [Visits] - renamed [Medico] table, with 1 record per visit
Here are the notes I began to keep while taking my first look at your sample:
- turned off “Auto date-time” option for this report
- marked [Dates] table as a date table
- changed all date formats to “dd-mmm-yyyy”
- deleted existing bi-directional relationship between [Dates] and [Medico] tables
- created 1-to-many relationship between Dates[Date] --> Medico[Appointment Created Date]
Please post your revised model and I’ll take another look.