Best practice for date table when dealing with a fact table with more than one date?

[tomi5]

Suppose I have a fact table ORDERS - perhaps it looks a little something like this:

ORDER ID	CUSTOMER ID	ORDER DATE	SHIPPING DATE	DELIVERY DATE
1	4	1st Jan 2022	3rd Jan 2022	6th Jan 2022
2	7	10th Feb 2022	10th Feb 2022	11th Feb 2022

I also have a Date table that looks something like this

DATE	Quater	Year	Week No.
1st Jan 2022
2nd Jan 2022

From these tables (and perhaps some extra dimension tables) I may want to ask questions like:

How many items were delivered in Jan 2022?

How many items were dispatched in Jan 2022?

How many total orders in Jan 2022?

The problem is that for each of these questions they rely on different dates in the ORDERS table. So creating a relationship between the Date table and ORDERS would require us to pick one specific date in the ORDERS table thus not allowing to filter on the other two dates.

What is in general the best way to handle such a scenario. I can think of a few solutions:

1) Create 3 relationships between ORDERS and Date table then in any visuals simply change the relationship that is being used with DAX USERELATIONSHIP function.

2) Split the fact table into 3 fact tables (ORDERS, SHIPPING, DELIVERY) based on each date and then have three active relationships between the date table and the new tables. This seems like a bad idea as we would have to also create more relationships for the other dimension tables also.

What would be a good solution in this case?

[Mikelytics · Accepted solution]

Hi tomi5,

when there are multiple date columns you should think about what is the most important date column for the range of your usecases. For this you make the relationship active. For the other columns you also create also relationships. But since only one relationship can be active the other ones are inactive.

When you then want to make calculations with the other relationships you should take a look on the formula USERELATIONSHIPS() 

USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

For example:

Base formula for active relationship (delivery date)

Items delivered =
SUM('Fact Table'[Item Amount Column])

Formula with reference to the dispatch date

Items dispatched =
CALCULATE(
   [Items delivered],
   USERELATIONSHIP('Fact Table'[Dispatch Date Column], 'Dim Date Table'[Date])
)

Using USERELATIONSHIP in Combination with CALCULATE you can do all kinds of calculations covering your specific case.

Best regards

Michael

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.

[VijayP]

@tomi5 

I suggest the 1st option as better one!