Date Table for multiple data tables with date fields

[sebastianfalcon]

Hello,

I created a centralize date table using Bravo for Power BI by SQLBI

I have 2 additional data tables from a CSV file, both of them have different date field columns. I wanted to create a relationship between my centralized date table which sorts months, day of the week etc. and my 2 separate data tables. My 2 data tables have a 1:1 relationship with a Unique ID . But whenever I set up the relationship for the date table to the other 2 data tables, I get 1 active and 1 inactive relationship and have to switch between both. Have anyone else come across this issue? 

[djurecicK2]

@TomMartens Agree with you on using one date table whenever possible, which is most of the time.

[TomMartens]

Hey @djurecicK2 ,

my post was not about have a single date table (even I advocate for it most of the time).
Here I was focusing on the one-to-one relationship between the two data tables. This relationship is responsible that one of the date data relationships becomes inactive.

But I do not want to discuss this before @sebastianfalcon has answered to this threat again.

Regards,
Tom

[TomMartens · Accepted solution]

Hey @sebastianfalcon ,

you end up with one-active and one-inactive relationship because otherwise there would be a circular dependency, this is not possible.

The 1-to-1 relationship is odd and should be avoided if possible, for this reason, I recommend reading this article:

• Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
• Model data in Power BI - Training | Microsoft Learn

Consider merging both of your data tables into a single table.

Before you start interfering with your measures by using USERELATIONSHIP, get your model right. Of course, there might be reasons why it's not possible to merge both tables into a single one, but for now (based on the information you provided) this is not the case.

Regards,

Tom

[sebastianfalcon]

Thanks @TomMartens I am unable to merge both tables due to the nature of the data. I will review those 2 articles you linked and make sure the model is right! Thanks again. I'm new to PowerBI! 

[djurecicK2]

Hi @sebastianfalcon ,

 You will need to use the USERELATIONSHIP function in your measures where the relationship is inactive. Here is some additional information.

https://learn.microsoft.com/en-us/dax/userelationship-function-dax

https://towardsdatascience.com/3-3-arguments-on-deciding-if-using-multiple-date-tables-is-a-good-ide...