Forum Discussion

Niko's avatar
Niko
Frequent Visitor
9 years ago

Creating DateTime relationship, epoch time

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 ep...
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

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's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    Niko
    Frequent Visitor
    9 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        Niko
        Frequent Visitor
        9 years ago

        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

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      9 years ago

      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's avatar
        Niko
        Frequent Visitor
        9 years ago

        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