Linking date dimension to multiple tables (dates)

[Anonymous]

Hi guys,

I am having problems with my data model. 

I have a table where people book hours (The action table), date = activity date .
I have another table where those booked hours (from another system) end up: the clarity table, date = date occurred.

Then I have a date dimension which I want to use to filter both the dates from these tables at once, so I can check for mismatches between the systems across days / weeks / months etc.

However, it is impossible to link the date dimension to both the tables and keep active relationships, making it unable to filter all the data from the two tables with the "overarching" date dimension.

There is a many to many relationship between the two tables, because people book multiple times per day in both systems.
How to fix the date issue?

[Anonymous · Accepted solution]

You should NOT link dates from fact to fact (action to book).

The direction of the relationship should be monodirectional whenever possible (and I don't see a valid reason to have them bi in this model).

Remove that relationship between the Action and the Clarity OR leave it inactive and enable it when needed using USERELATIONSHIP.

Then re-think your model based on the business needs. Do you start from Clarity to filter actions? Use a metric to obtain your data and enable the relationship when needed.

Cases like this require a careful planning of your business needs or you end up in an unmanageable model (and based on what I see you started with the wrong foot).

Take a basic tutorial on data modeling and relationships in PowerBI. They're not simply arrows with filters, they're fundamental.

[Anonymous]

Created a better datamodel, now its working

[Pbrit]

Hello Chris,

I wonder how you could join more than one table to the date table without an indirect relationship. I have tried similar and I have an indirect relationship. I would appreciate your insight.

Thanks

[Anonymous]

Now this one looks much better 🙂