How to Use Time and Date Dimensions in a Power BI Model

I have written a blog article about the date dimension, and also one about the time dimension. However, I got some questions in the comments about how they will be used together in a data model. In this article, I’ll explain that.

Date Dimension

The date dimension or calendar dimension is one of the most common tables in a reporting data model. this is a table with one row per date with all different date attributes. In the blog below I provided a full Power Query script to create a date dimension;

Time Dimension

Because the date and time combined with each other in a table will make it big, The time dimension is another table with one record per time span required for analysis. Below is an example of a time dimension with the Power Query script of it;

Time and Date Tables in a Data Model

The time and the date tables should not be related to each other, their relationship should be made in the fact table. in below you can see that the fact table’s data in a full date and time column (not separated);

For connecting this table to the time and date tables, you have to separate date and time, which you can do that easier in the Power Query Editor.

Separate Date and Time

Go to transform data;

In the Power Query Editor, click on Add Column, and then Under Date, Select Date Only.

Do similar thing this time with Time, and Time only;

Now that you have the date and time columns separately, you can remove the main date and time column;

now you can close and apply the Power Query Editor window, and create the relationship between this table and the Date and Time dimensions;

Having both date and time in one axis

Another question I got in the comments was that using this approach can we have the date and time in one axis? yes, you can. You just need to drag them into the axis one after each other, this forms a hierarchy which you can expand into.

In the example below, I have both date and time from the model above demonstrated in a single axis;

I hope this post helps in your Power BI data model implementation. if you have any questions, please don’t hesitate to contact me in the comments below.

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.