Slicer not cross filtering on a calculation using a Related table

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

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Why isn't it just:

Code:
COUNTROWS(fAppointments) + 
COUNTROWS(
    FILTER(
        fCancellations,
        fCancellations[CalculatedColumn] = 1
    )
)
 
Upvote 0
Solved - Pages 147 to 149 of Collie & Singh's book (PowerPivot 2nd Ed): I was using a field from the data table not the lookup table as the slicer, and since relationships flow downhill, not uphill, this meant the slicer wasn't aggregating correctly.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top