Counting/summing particular items from different tables in DAX

[rodwhiteley]

Another newbie question here, sorry, but I am just getting my head around this stuff, and I have a deadline to meet:

Background: In our workplace, therapists have appointments booked of different types, and patients occasionally cancel their appointments with no notice. We're trying to make a productivity measure to look at our guys accounting for this behaviour of the patients.

Briefly, the FactAppointments table format is as follows:

Appointment Date | Therapist Name | Appointment Time | Appointment Type | Patient Name | ...
<3 million rows>

In a different FactCancellations table I have cancellations that have been made of these appointments:
Appointment Date | Therapist Name | Appointment Time | Appointment Type | Patient Name |Cancellation Date|...
<250k rows>

I have made a dimension lookup table for the |Appointment Type| column so that these 47 fields are collapsed to 4 categories, and made the relationships with the Fact tables.
These Fact tables are linked by a dimension Calendar table spanning the time we've been open and into the future.

Question: I need to count up the appointments of a particular type added to the cancellations so that I can then figure how many appointments each therapist has had per day adjusted for the cancellations - ie appointments + cancellations on a given day (week, month, year) to then display in a Pivot table.
This must be simpler than I am making it, but I keep getting lost in a many-to-many relationship, and haven't figured out how to do this linking by the calendar which is the one side of the 2 fact tables.

If this is covered in any of the following books, please just point me to the appropriate sections - I'm working my way through these at different rates:
PowerPivot PowerBI 2nd Ed Collie & Singh
Learn to write DAX Rob Allington
Definitive Guide to DAX Russo & Ferrari
M is for Monkey Puls & Escobar

 

[Matt Allington]

You don't need a many to many relationship. What is important here is that you have 1 lookup table for each item you want to filter on. In your case you have time (so you have a calendar table) and you are filtering on Appointment Type. Both these tables are on the 1 side of the relationships and the other 2 fact tables are on the many side. Just make sure both fact tables are joined to both lookup tables. Then write 1 measure in the Appointment tables - could be simply Total Appointments = countrows(Appointments) and then a second measure in the Cancellations Table - could be Total Cancellations = countrows(cancellations). Finally write a measure such as Net Appointments = [Total Appointments] - [Total Cancellations]. If you need to filter on Patient, Therapist, then create lookup tables for those objects too and join them in the same way.

Then as long as you only use your lookup tables for filtering it will all work.

After your urgent deadline, you might want to see if you can put all your data in 1 data table. This may or may not make sense depending on the specific situation.
I am sorry I did not put a specific chapter in my book about multiple data tables - it seems such an obvious idea now. I will make a note and write a decent blog post on this sometime soon. ExceleratorBlog -