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.
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;
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.