How to create 3 relationships between 3 tables

[IvanS]

Hello,

I am trying to create relationships between my DateTable and 2 fact tables (Drivers & Session tables).

Currently I have relation between DateTable and Session using "Date Charging From" and another relation between Session and Drives using "DriverID" column.

Now I would need to create connection between DateTable and Drivers as well to have custom date hierarchy (weeks, etc.) applied for Drivers table.

Any suggestions how to avoid ambiguity in this model?

Thank you in advance!

Ivan

[lbendlin]

General guidance is that you should avoid connecting fact tables to other fact tables. Instead, both should be controlled by the common dimension table.

[IvanS]

But how can I create visuals with the following scenarios?

- Trend line of charging sessions (using date table)

- Trend line of registered drivers (using date table)

- Charging sessions per driver

Do I need to create another table, e.g. calculated table of Drivers (with distinct values) which will be then connected separately?

[v-janeyg-msft · Accepted solution]

Hi, @IvanS 

Create the DriverID dimension table, disconnect the id column of the original two fact tables, and then connect to the two fact tables respectively. When it comes to visual, just use the columns of the dimension table.

Best Regards,
Community Support Team _ Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.