Forum Discussion

meyer's avatar
meyer
Regular Visitor
8 years ago

Creating a date column with a join from different tables

Hi everybody,

 

I have a fact table which contains incidents. For each incident I have a an OPEN_DATE and a CLOSED_DATE which have yyyyMMdd format in my table.

On the other side, I have my DATE dimension table which have as primary key yyyyMMdd which is name ID_DATE and an other column which contains the date with the format : dd/MM/yyyy.

 

Now, I want to create a column which will contain my dates with the dd/MM/yyyy format. (Basically, I want to join my OPEN_DATE column from my fact table with my ID_DATE column from my date dimension to get the corresponding date)

 

How can I manage to do that please ?

 

 

Need Help

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi meyer,

     

    You can create a one to many relationship from DATE dimension table to Fact table based on ID_DATE and OPEN_DATE. Then, use RELATED() function to refer to the dates with "dd/MM/yyyy" format.

     

    Regards,

    Yuliana Gu

    • meyer's avatar
      meyer
      Regular Visitor
      8 years ago

      Hi v-yulgu-msft and thanks for your response.

       

      I've already created the relationship you mentionned but could you elaborate on the use of the RELATED function please ?

       

      Here's my relationship between the two tables :

       

      I've read the power bi documentation related to this function and I can't manage to get it work. I think there's something I'm missing.

       

      Note that I want to be able to have the matching dates in a slicer.

       

      Regards,

      Meyer