Help with model fix - Ambiguity Date Table

[EliasNo]

Hello all,

I have a question regarding modelling with Date Tables and Ambiguity.

I have a temporary fix but wonder if it possible to solve in a more consistent way.

The problem is as below:

I have a model with several dimension tables, let us call them Dim A and Dim B,

as well as several fact tables, Fact A and Fact B.

Dim A is related to Fact A.
Dim B is related to Fact A and Fact B.

To be able to filter these by Date I have connected Date Table (Date A) to Dim A.

Previous I only worked with the Date Table connected to Dim A and everything works well.

There are no ambiguity in the model.

But if I connect the Date Table to Dim B as well, there will be ambiguity in the model, as there are two ways to go to Fact A (Both through Dim A and Dim B).

My temporary solution is to create another Date Table (Date B) that is connected to Dim B.

The problem with this is that one cannot filter the different Fact Tables with the same Date Table, plus the model is getting bigger with an additional Date Table.

Is there any other solution for this?
(I do not want to combine the different dim-tables, as this will make the model more difficult to understand.)

(Date B Table is my current solution to the problem (Removing connection from Date A to Dim B))

But is there any other solution, that does not require the creation of the other Date Table?

//Thanks in Advance
Best Regards
Elias

[amitchandak · Accepted solution]

@EliasNo , Ideally Date should join with facts, not dimensions. Move the date column from dimension a to fact .

DAX using related(DimA[Date]) In fact A

and related(DimB[Date]) in Fact B

another option is to keep date table and independent

new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Fact1[Value]), filter('DIMA', 'DIMA'[Date] >=_min && 'DIMA'[Date] <=_max))

Same way for DIM 2