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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

2 thoughts on “How to Use Time and Date Dimensions in a Power BI Model

Leave a Reply

%d bloggers like this: