rodwhiteley
New Member
- Joined
- Jan 15, 2012
- Messages
- 37
Apologies for a likely noob question here, but here's the scenario:
The data model I have is relatively simple, but moderately large (about 3.5 million rows and 191k rows in the 2 fact tables). There are 2 fact tables, one with appointments our clients attended (3.5M), and another with cancellations of appointments (191k)
These tables are linked via unique identifiers for the patients (the field "MRN") and unique identifiers for the therapists ("Therapist Name"), and all tied to a calendar table since each row has a unique date:
https://1drv.ms/i/s!AgytcDfT4DRFhL5oorJP_veKiaVIjQ
To get an idea what's happening with our number of appointments over time I can of course visualise simple counts of appointments, and slice these up by type of appointment, and therapist name. This works and is verified.
The complication arises in that when patients cancel late (on the same day as their appointment) we want to count this as an appointment, so I've tried creating a measure which is the count of the appointments (from the fAppointments table), as well as the count of any appointments that are within 24 hours of the actual appointment time (a calculated column within fCancellations table which can be 1 or 0 depending on the date/time of the appointment and the date/time of the cancellation)
The measure I created seems to be ignoring the row context when I display it in a visualisation - when I have the no filtering (chiclet slicer choosing therapist name), ie all therapists in the department chosen, there's approximately 20% or so late cancellations across the years:
https://1drv.ms/i/s!AgytcDfT4DRFhL5p3Ts70OP8wt_H7A
But when I slice by any individual therapist, the ratio skyrockets, for example:
https://1drv.ms/i/s!AgytcDfT4DRFhL5q9khx-TN8NOpg0Q
My measure formula is almost certainly the source of the error given my (lack of knowledge) but here it is:
ApptLateCancel = COUNTROWS(fAppointments)+COUNTROWS(RELATEDTABLE(fCancellations))
I am clearly misunderstanding something here since I get the same results by not wrapping the fCancellations count inside a RELATEDTABLE
Or try using a Calculate:
ApptLateCancel = COUNTROWS(fAppointments)+CALCULATE(SUM(fCancellations[LateCancelPatient]))
I'm clearly not understanding something here, and also probably haven't given enough information, so apologies in advance, but would appreciate any help which could include pointing me to the correct sections in Collie & Singh or Allington's books
The data model I have is relatively simple, but moderately large (about 3.5 million rows and 191k rows in the 2 fact tables). There are 2 fact tables, one with appointments our clients attended (3.5M), and another with cancellations of appointments (191k)
These tables are linked via unique identifiers for the patients (the field "MRN") and unique identifiers for the therapists ("Therapist Name"), and all tied to a calendar table since each row has a unique date:
https://1drv.ms/i/s!AgytcDfT4DRFhL5oorJP_veKiaVIjQ
To get an idea what's happening with our number of appointments over time I can of course visualise simple counts of appointments, and slice these up by type of appointment, and therapist name. This works and is verified.
The complication arises in that when patients cancel late (on the same day as their appointment) we want to count this as an appointment, so I've tried creating a measure which is the count of the appointments (from the fAppointments table), as well as the count of any appointments that are within 24 hours of the actual appointment time (a calculated column within fCancellations table which can be 1 or 0 depending on the date/time of the appointment and the date/time of the cancellation)
The measure I created seems to be ignoring the row context when I display it in a visualisation - when I have the no filtering (chiclet slicer choosing therapist name), ie all therapists in the department chosen, there's approximately 20% or so late cancellations across the years:
https://1drv.ms/i/s!AgytcDfT4DRFhL5p3Ts70OP8wt_H7A
But when I slice by any individual therapist, the ratio skyrockets, for example:
https://1drv.ms/i/s!AgytcDfT4DRFhL5q9khx-TN8NOpg0Q
My measure formula is almost certainly the source of the error given my (lack of knowledge) but here it is:
ApptLateCancel = COUNTROWS(fAppointments)+COUNTROWS(RELATEDTABLE(fCancellations))
I am clearly misunderstanding something here since I get the same results by not wrapping the fCancellations count inside a RELATEDTABLE
Or try using a Calculate:
ApptLateCancel = COUNTROWS(fAppointments)+CALCULATE(SUM(fCancellations[LateCancelPatient]))
I'm clearly not understanding something here, and also probably haven't given enough information, so apologies in advance, but would appreciate any help which could include pointing me to the correct sections in Collie & Singh or Allington's books