Connecting multiple date fields to date table

[Anonymous]

I've created the data model detailed below which works to my knowledge based on the time intelligence functions I've created. I wanted to get feedback on if there is a more efficient method of linking multiple date fields to a date table. 

[TomMartens · Accepted solution]

Hey,

no, there is no more efficient way to connect one date table with many facttables. This is how a dimension table (the date table) interactx wiht multiple fact tables (most often the tables on the many side of a relationship) in a star schema (the Power BI data model).

As there are many relationships with the "Cross filter direction" property set to "both", please be aware that this type of setting sometimes happens by accident and sometimes this is unwanted. Please watch your calculations closely as theses settings might interfere with the calculations in an unwanted way and create unexpeted results (speaking mildly).

Hopefully this provides some ideas / insights.

Regards,

Tom

[Anonymous]

Thank you for the insight, much appreciated!

When there is information that can/needs be filtered both ways (e.g. I want to slice payments by year to show open opportunities) how would you recommend setting up the data model to avoid the pitfalls you mention?

[TomMartens]

Hey,

I recommend watching some videos from here: https://www.sqlbi.com/tv/

Here you will find in-depth information, you just have to spend some time.

Regars,
Tom

[Anonymous]

Thanks Tom!