How to handle 2 Datedimensions

[showy]

Hi

I have the following question and would like to know how to edit the best practice.

I have data that has a creation date and a document date. Now there are evaluations that refer to the creation date and others that refer to the document date.

I would like to use the date values from the date dimension in the filters.

However, as the slicer only works with active relationships, I have made a copy of the date dimension and named one in DatedimensionCreateDate and one in DatedimensionDocumentDate and linked them to the fields in the fact table.

I then dragged the DatedimensionCreateDate [Date] into the filters for evaluations based on Creationdate and the field DatedimensionDocumentDate[Date] into the evaluations based on Documentdate.

Is this the right way or how do you deal with it if you have several dates in the fact tables?

[v-pagayam-msft]

Hi @showy ,
I just wanted to kindly follow up to see if you had a chance to review the previous response provided by us. I hope it was helpful. If yes, please Accept the answer so that it will be helpful to others to find it quickly.
Thank you.

[mh2587]

You can do this without duplicating the Date table by Using the USERELATIONSHIP() function to activate the inactive relationship
for more details follow the following article:
https://learn.microsoft.com/en-us/dax/userelationship-function-dax

[showy]

Hi 
the problem is that when the active relationship ist createAt and the inactive is documentdate only the filter for createAt[Date] will work.

[v-pagayam-msft · Accepted solution]

Hi @showy ,
Thank you @m4ni  and @mh2587  for the insightful responses!

Your current method of utilizing two separate date dimension tables one for CreateDate and another for DocumentDate is both valid and recommended when needed slicers to filter visuals based on different dates.

Because slicers work only with active relationships, having distinct date tables ensures visuals are filtered correctly according to the relevant date field.

While the USERELATIONSHIP() function is useful for measures that require calculations using an inactive relationship, it does not impact slicer behavior or filtering at the visual level.

Hope this helps.

Thank you.

[m4ni]

hi @showy 

You can activate the relationships in DAX code using the USERELATIONSHIP function.

For example, if you are evaluating a measure using createddate then that is the active date so you dont need to do anything.

At the same time you can evaluate a measure on document date (without changing the phisical relationships) by using the USERELATIONSHIP function.  Both will work at the same time.

Example on document date:

YouMeasureName = CALCULATE(YourMeasure, USERELATIONSHIP(Date'Date, YourFactTable'DocumentDate)

This will enable the relationship in code. HTH

[showy]

hi @m4ni 

this post is not about to create a measure or evaluate any values. Its about to controll the visuals.

On the one hand some visuals are based on creation date on the other hand other visuals are controlled by the document date.

The question is how to handle this topic with datetabel(s).