Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
Im wondering if anyone can recomend a 'best practice' for modeling appointments data.
Resulting model used for:
Current Table Schema:
Current Model:
Issues I'm trying to solve:
I'm sure somone has tackled this challange. Any suggestions?
Edit: Pictures of model....
Solved! Go to Solution.
@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.
@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.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |