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...
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

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

    • vanessafvg's avatar
      vanessafvg
      Community Champion
      9 years ago

      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

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

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

         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