Creating a date column with a join from different tables

[meyer]

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 ?

[v-yulgu-msft]

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]

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