Date Relationships - Multiple Tables, Multiple dates.

[Anonymous]

Hi All,

I've been self thought in PowerBi for a few months now and have been starting to get to grips with the relationship model. However, I am struggling with the date table. As you can see from my testing model below, I've managed to somehow establish active date relationships with multiple tables.

Having done plenty of exploritory work and other modelling I can't replicate the same behaviour in the below model.

I have a few different asks:

• I know that both of my models are far far from ideal, the second one being the basis to clean up the first. - This  first one is pulled from SQL views that already exist in the data source.
• The data source is a SQL server DB for Connectwise Automate and they do provide a data dictionary. This is the second model, they are the direct tables from the DB.
• How is it possible for me to have multiple active relationships in the first model?
• How should I manage the date relationships in the second model, given that I have multiple tables and each table has mutliple date fields within it. - I'm familier with USERRELATIONSHIP in the context of different date fields in a single table, but what if my model has mutliple table with multiple dates.

Regards,

Daniel

[timg]

Hi danny,

Between every two tables there is one relationship allowed to be active by default. So in your first model, all date relationships are active because there is only one relationship between every table and the date table. However, you cannot have multiple active relationships between the same two tables. So for example, if you filter the year 2021 in your date table, it logically cannot simultaneously relate that 2021 filter to both the invoice date and closed date in your invoice table because they will result in different selections.

In order to join a date table to multiple date fields within the same table you have two options:

1. create a duplicate datetable (I would recommend this option if most of your tables contain the same date fields). For example, if you have a sales table and order table and returns table, and all of them contain a "shippingDate", and "OrderDate" column, you may want to create two date dimensions calles dimOrderDate and dimShippingDate. this minimizes confusion for end users. However if you have to many unique date columns in your model you would require a lot of duplicate tables which is not recommended.
2. Alternatively, when you have a lot of different date columns within the same table, you would benefit more from the USERELATIONSHIP function. This allows you to use as many date columns as you'd like with one date dimension. The drawback is that you will need to be extra precise in your measure definitions since you would need to manually assign the relationship you'd like to use for each one.

In the case of your second model I'd opt for option 2 since I see quite a lot of date columns. By adding all the required relationships in your model view (image 1), you'll be able to use them in each measure afterwards (image 2) with the USERELATIONSHIP function.

 image 1

 image 2

Hope that helps!

Regards,

Tim

[Anonymous]

Fantasic, that brings a lot of clarity to it for me around the date relationship. On the topic of model two, given that we have multiple tables that could be viewed as fact tables, is it ok to relate these considering that they are coming from Connectwise Data Warehouse.

[v-kelly-msft]

Hi  @Anonymous ,

Yes,you can ,check the reference below:

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler · Accepted solution]

@Anonymous - You can have multiple active relationships for a date table to multiple other tables as long as you don't form 2 distinct pathways between any two tables. The minute you do that, one of the relationships becomes inactive.