Forum Discussion
Creating DateTime relationship, epoch time
Hi Niko
I suggest you try a different method for creating your date-table. Try creating your date table like this:
Go to menu -> press new table -> insert this dax query: TimeDim = CALENDAR(FIRSTDATE(Table[Date]); LASTDATE(Table[Date])) -> press enter -> go to relationships tab on the left -> create the relation between your tables.
I can go into details about how it works. Let me know if you want me to elaborate on it. For now, try this method.
Best
Martin
Hi Anonymous
Thank you for reply Martin. I tried this and it comes with an error "A date column containing duplicate dates was specified in the call to function 'FIRSTDATE'. This is not supported."
I tried this with both data types date and date/time and it didn't make any difference.
Br. Niko
Hi Niko
Thank you for the reply. It doesn't matter what date-format you use. I would recommend always using Date/Time if time stamps matters.
A question: Is there any particular reason for having duplicates on dates? I might have a solution here depending on your answer.
Anonymous there is no reason to have duplicates on dates. Data just turned up this way when I formated epoch time to human readable time. Time stamp matters in this case, cause I want to drill down to minute level.
Br. Niko
Alright, I might have a solution, which will work for you. Explained below:
- Make sure the data type is date/time
- Go to the data on the left.
- Left click on the data and select edit query
- left-click on the date-column and click on "remove duplicates".
- Click close & apply
Make sure that you don't remove any rows in the process. It is easy to revert this change, so if any goes wrong you can always revert it. Then try to use the dax-code, I gave you, for a new date table and see if it works.