Dealing with dates

[Sylvain74]

Hello,

Let's say in my data model I have 1 or more tables with multiple datecode columns (yyyymmdd). I have as well one single dim_time table.

Should I add the dim_time table as many time as I have datecode columns or should I add it only once and use "Relationship" dax instruction?

Should I create multiple calculated table based on dim_time?

What is the best practices?

I don't have any calculation based on that dates, usually I use the dim_time table just to display the date in a user friendly fashion and sometime in a slicer as well (e.g. order_date to see only purchase order in period)

Thanks 

Sylvain

[amitchandak]

@Sylvain74 , Hope you are checking about date and calendar table. In your calendar table you can have date and datekey

Datekey = format([Date], "YYYYMMDD")

and you can join on this or date column

[Sylvain74]

@amitchandak : I already have the datekey in the dim_time table. The question is not here...

Actually I am wondering what is the good practice when you have multiple date columns in your tables. Should I add multiple dim_time tables and link them respectively to each date columns?

[HotChilli]

There is no best practice here. It totally depends on what the visualisations/dashboard is doing.

A relationship means filtering so you might want to 2 dimensions (each in a slicer)  to filter a fact table on, say, Txn Date and Ship Date without the 2 dates filtering each other (if that makes sense)

You can read this for an introduction radacad/role-playing 

[Sylvain74]

Hello HotChilli,

That's clear for me that if I want to filter out the Txn Date and Ship Date separately with slicers, I need to have 2 different relationships.

But for other datecodes in my fact table that I just want to display in user readable format. Should I link each of them to a specific dim_time table to get the correct format or what else?

What should I do? What is the best solution to avoid adding multiple times the dim_time table just for formating purpose?

Thanks.

[v-kelly-msft · Accepted solution]

Hi  @Sylvain74 ,

Yes,I think creating sereval dim_time tables would make your model look more clean and convenient.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!