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

 

 

 

23 Replies

    • Niko's avatar
      Niko
      Frequent Visitor
      9 years ago

      Yes it is. I also tried to put it to date datatype for both tables to see if it made any diffence, but it didn't.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

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

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

        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.

  • 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