Creating DateTime relationship, epoch time

[Niko]

Hi,

I'm new to powerBI and I just pulled my historical weatherstation data from Azure to powerBI and now I'm trying to figure out how to make DateTime relation. Data was orginally organized by epoch timestamp. I found couple tutorials how to convert this to human readable format. After this I created a new lookup table "dates" where I created all the day of week, weeknumbers etc.

It allows me to create relationship between these two table but it is not showing weeknumbers or anything from the lookup table "dates". Only the timestamp from actual data which I formated from epoch time to human readable time. What would be the best way to get this working? In future I would like not only to drill data to day level but also on minute level. So I could later on create filters like average last 10 minutes etc.

[v-jiascu-msft]

Hi @Niko,

The reason for "A date column containing duplicate dates was specified in the call to function 'FIRSTDATE'. This is not supported." is there are dates with same date but different time.

Could you please post some snapshots of the lookup table "dates" and the relationship? Because I think it's not a good idea to establish a relationship between the columns with different time (not date).

Best Regards!

Dale

[Anonymous]

I just thought of something else, you can do. You can use this calculation to make a new time dimension: CALENDAR(DATE(DAY;MONTH;YEAR); NOW()). This way you just manually decide when the time dimension starts.

Let me know how it goes.

Best

Martin

[Niko]

Thank you for replies @v-jiascu-msft and @Anonymous

I created now this new Dates table, but I'm still having the same issue. Any suggestions how to solve this? I attached snapshots
Data table

Dates table

 

Relationship

Visualisation view

[v-jiascu-msft]

Hi @Niko,

Please try these steps.

1. Delete the relationship;

2. Add a column in "Data table":

PureDate = [Date].[date]

3. Create the relationship. 

Best Regards!

Dale

[Niko]

Hi @vanessafvg and @v-jiascu-msft I created this puredate column and a many to one relationship between them ( Dates[puredate} and Data[date] ) but it didn't solve the problem. Then I also tried to put DateTime type only to date type (on both tables) as Vanessa said, but only thing that this seemed to affect was that I can not anymore see the time in visualisation table on Data[Date] section as in snapshot below.

-Niko

[v-jiascu-msft]

Hi @Niko

As your picture showed, it seems you added the [puredate] to a wrong table. You should add it to table [data]. Please have a try.

Best Regards!

Dale

[Niko]

I've got this now working! Firstly I converted the epoch timestamp to time/date. Then I separated time and date to their own columns. After that I followed up these instructions to create dynamic date and time calendars with power query

http://databear.com/2016/11/08/power-bi-tip-dynamic-calendar-table/

https://ginameronek.com/2014/10/01/its-just-a-matter-of-time-power-bi-date-time-dimension-toolkit/#C...

All tought I'm having a bit issue with time calendar. Since I've collected the data very acurate with epoc timestamp . I might have times like

0:00:18

0:01:00

0:02:14

0:03:45
0:04:00

...

Time calendar has been created on minute level and that is the level where I wanna track also my data.

In visualisations mode it's now only showing the timestamps that have exact the same value. In this case it would show only the values behind the time 0:01:00 and 0:04:00. What would be the most convenient way to get rid off seconds and convert time to nearest minute?

Br.Niko

[v-jiascu-msft]

Hi @Niko,

Could you please tell me if your problem was resolved? Could you please share the answer or mark the proper answer as solution if it's convenient for you? That will be a help to the others.

Best Regards!
Dale

[Niko]

Hi @v-jiascu-msft

I've been busy with some other project but tried this trick today and it didn't work. I think this might work if I would do it in query mode (with M) not with DAX.  Like I did the date and time table and then they started working. Is there someone reading this post who would know how to do this (TimeNoSec = FORMAT ( [Time]; "hh:mm:00" ) in M?

-Niko

[v-jiascu-msft]

Hi @Niko,

You could add a new column with this formula:

Bridge =
FORMAT ( [Minute], "hh:mm:00" )

Best Regards!

Dale

[Niko]

 I got this working! Firtsly I created date/time from epoch timestamp and then separated them to their own columns "date" and "time" after this I followed this instructions to create dynamic date calendar and after that I followed this instructions to create time "calendar".

All tought I'm now facing a new problem with time calendar. As my data has been collected very acurate with epoch timestamp I'm now having times like

0:00:18

0:01:00

0:01:12
0:01:32

0:02:00

In visualisations it's only showing the data which has comed exactly 0:01:00 and 0:02:00 but not the results between them. What would be the best way to aggregate/summarize/round down the data to minute level? I tried MROUND(Data[Time];00;01) but didn't work ^^

Br.Niko 

[vanessafvg]

@Niko at this point i think it woudl be best to share your pbix, if you want to send it privately feel free to send it to me in private message or dump it here so one of us can assist

[Anonymous]

I suspect that it is the time stamp, which is not matching, that are causing the issue, but I actually don't know. I've tried this before where it wasn't an issue. Unfortunatly, I have no access to that report anymore. Could you possibly either share the data or the report?

Thanks in advance,

Martin

[Niko]

Unfortunately I can't share the data nor the report. But the code behind the epoch timestamp  is  
= Table.AddColumn(#"Removed Columns", "Date", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [TimeStamp])) to get it to human readable mode. I think there shouldn't be anything wrong in this and it should match 100% to DateTime

[Anonymous]

That was my thought as well. Should probably contact Microsoft about this. I can't re-create your issue. 

[vanessafvg]

@Niko is your datetime in the date table at at time level?

its better practise to separate out the date and time fields if you using them for analysis?

almost positive that date and time are not matching, even if you do want to keep your date time column create an additional date column without time and see what happens on both sides  to test the match to see if it is the datetime causing the issue

[Anonymous]

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

[Niko]

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

[Anonymous]

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.

[Niko]

@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