Forum Discussion
How to handle 2 Datedimensions
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?
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.
6 Replies
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-daxHi
the problem is that when the active relationship ist createAt and the inactive is documentdate only the filter for createAt[Date] will work.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.
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.
