cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
doobie Regular Visitor
Regular Visitor

Connecting multiple date fields to date table

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. 

1.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Connecting multiple date fields to date table

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Connecting multiple date fields to date table

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

doobie Regular Visitor
Regular Visitor

Re: Connecting multiple date fields to date table

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?

Super User
Super User

Re: Connecting multiple date fields to date table

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
doobie Regular Visitor
Regular Visitor

Re: Connecting multiple date fields to date table

Thanks Tom!

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 183 members 1,973 guests
Please welcome our newest community members: