Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pe2950
Helper I
Helper I

Suggested model setup for appointments analysis?

Hello,

 

Im wondering if anyone can recomend a 'best practice' for modeling appointments data. 

 

Resulting model used for:

  • Analysis of kept, missed, cancelled appointments by either created date, or appointment date
  • Analysis of resources attached to appointments, counts of types of appointments by resources, etc
  • Analysis of appointment created by particular users

Current Table Schema:

  • Appointments table, that acts as the hub, each appointment is represented by 1 (or more rows)
  • If the appointment is rescheduled, a new row is created and the previous (cancelled appointment) is marked with a cancelled flag. 
  • GUIDs/refrences to other tables containing resources, users, event types, etc. 
  • Create date as timestamp, appointment date stored as 2 columns (string date) & (string start time) example( mmddyyy) / (hhmm)

Current Model:

  • Appointments serves as hub, with lookup tables for the resources, users, events as spokes. 
  • Transform on the appointment date to a standard date/time column
  • Standard date table linked to the appointments table.

Issues I'm trying to solve:

  • I can't easily gain many insights from the data without complex DAX for EVERYTHING. For example counts of appointments created this month.
  • Because the date table can only have 1 active relationship with appointments, again i have to use DAX to specify if it should use the relationship to create date, or appointment date. 
  • Generating simple outputs, of say appointments kept by month, or appointments scheduled by month are seemingly more complex then I feel they could be with a better model. 
    • Example: I'll have to set the active date relationship, filter the table by expression for rows i need, count rows + 0 to generate usable data. 
  • Because of all the DAX and switching relationships, most drill-down, or interactions between visuals just wont work. 

I'm sure somone has tackled this challange. Any suggestions? 

Edit: Pictures of model....

model.png

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@pe2950 first and foremost, don;t have both direction cross filter between tables, it has big impact on the performance is not recommended as a best practice.

 

Having multiple relationships with the date dimension on multiple columns is a very common model practice and yes you need to write explicit measures to make an inactive relationship to active.

 

Think about, a sales order has order date, ship date, delivery date, due date, etc. and it is very common to have same date table with 4 relationships with 3 as inactive relationship and then there are measures like

 

# order due this month = use relationship on the due date

# order shipped this month = use relationship on ship date

# orders received this month = use order date relationship and so forth so on.

 

At end of the day, you want to thrive for star schema, and here is a blog post on it.

 

Regarding your drill-down etc, it is not very clear. I worked with many complex models with many inactive relationships and never ran into any issues. I think it is to lay down what business question you need to answer and then create measures and visualization based on it.

 

Good luck!

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

1 REPLY 1
parry2k
Super User
Super User

@pe2950 first and foremost, don;t have both direction cross filter between tables, it has big impact on the performance is not recommended as a best practice.

 

Having multiple relationships with the date dimension on multiple columns is a very common model practice and yes you need to write explicit measures to make an inactive relationship to active.

 

Think about, a sales order has order date, ship date, delivery date, due date, etc. and it is very common to have same date table with 4 relationships with 3 as inactive relationship and then there are measures like

 

# order due this month = use relationship on the due date

# order shipped this month = use relationship on ship date

# orders received this month = use order date relationship and so forth so on.

 

At end of the day, you want to thrive for star schema, and here is a blog post on it.

 

Regarding your drill-down etc, it is not very clear. I worked with many complex models with many inactive relationships and never ran into any issues. I think it is to lay down what business question you need to answer and then create measures and visualization based on it.

 

Good luck!

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.