Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Sylvain74
Helper III
Helper III

Dealing with dates

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

1 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!

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@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?

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 

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.

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!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.