Date dimension has been discussed quite a lot on the Internet and you can find lots of valuable articles around it here and there. But what if you need to analyse your data in time level? A customer has a requirement to analyse their data in Minutes level. This means that the granularity of the fact table would be at minute level. So, if they store the data in their transactional database in seconds level, then we need to aggregate that data to minutes level. I don’t want to go there, just bear in mind that the granularity of your fact table is something that you must think about at the very first steps. In most cases, if not all cases, you’d be better to have a separate Time dimension. Then you need to have a TimeID or Time column in your fact table to be able to create a relationship between the Time dimension and the fact table. In this post I show you two ways to create Time dimension in Power BI:

• Creating Time dimension with DAX
• Creating Time dimension with Power Query (M)

Alternatively, you can take care of the Time dimension in the source system like SQL Server. Continue reading and you’ll find a T-SQL codes as complementary.

The techniques that I explain here can be done in SSAS Tabular model and Azure Analysis Services as well.

Requirements:

To follow the steps of building the test model you need to have:

• Power BI Desktop: Download the latest version from here
• A sample fact table containing time or datetime. I modified FactInternetSales from AdventureWorksDW and made it available for you to download in Excel format (find the download link at the bottom of the post)

How it works

First of all, you need to have a look at the table structure of the “FactInternetSales_withTime.xlsx” file.

As you can see the table contains “OrderDateTime” column in DateTime format. What we need to do is to split that column to two columns, one holding “OrderDate” data and the other holds “OrderTime” data. Then you can create the “Time” dimension with DAX or Power Query (M), or both if you like ?. You will then create a relationship between the “Time” dimension and the fact table.

Let’s start.

• Open Power BI Desktop
• Get data from Excel and load data from “FactInternetSales_WithTime” Excel file
• Click “Edit Queries”
• In the Query Editor page click “FactInternetSales_WithTime”
• Scroll to very end of the table and find “OrderDateTime” column. As you see the data type is DateTime
• Click “Add Columns” tab then click “Custom Column” to add a new column. We are going to add “OrderDate” column
• Type “OrderDate” as “New column name”
• Type the following Power Query function to get the date part of the OrderDateTime then click OK

=Date.From([OrderDateTime])

• Now add another column using the same method and name it “OrderTime” with the following Power Query function

=Time.From([OrderDateTime])

• Now we need to convert the data types of the new columns to Date and Time respectively. To do so select both columns and click “Detect Data Type” from “Transform” tab

• Close & Apply

In the next steps we create a Time dimension using DAX and Power Query (M). Then we create a relationship between the “FactInternetSales_WithTime” and the Time dimension.

Time Dimension with DAX

If you’re willing to create the Time dimension with DAX then:

• In Power BI Desktop click “New Table” from “Modeling” tab from the ribbon
• Copy and paste the below DAX code then press Enter

Time in DAX =
SELECTCOLUMNS (
ADDCOLUMNS (
GENERATESERIES ( 1, 1440, 1 )
, "TimeValue", TIME ( 0, [Value], 0 )
)
, "ID", [Value]
, "Time", [TimeValue]
, "Hour", HOUR ( [TimeValue] )
, "Minute", MINUTE ( [TimeValue] )
, "5 Min Lower Band", FORMAT (TIME ( 0, FLOOR ( DIVIDE ( [Value], 5 ), 1 ) * 5, 0 ), "hh:mm:ss")
, "15 Min Lower Band", FORMAT (TIME ( 0, FLOOR ( DIVIDE ( [Value], 15 ), 1 ) * 15, 0 ), "hh:mm:ss")
, "30 Min Lower Band", FORMAT (TIME ( 0, FLOOR ( DIVIDE ( [Value], 30 ), 1 ) * 30, 0 ), "hh:mm:ss")
, "45 Min Lower Band", FORMAT (TIME ( 0, FLOOR ( DIVIDE ( [Value], 45 ), 1 ) * 45, 0 ), "hh:mm:ss")
, "60 Min Lower Band", FORMAT (TIME ( 0, FLOOR ( DIVIDE ( [Value], 60 ), 1 ) * 60, 0 ), "hh:mm:ss")
, "5 Min Upper Band", FORMAT (TIME ( 0, CEILING ( DIVIDE ( [Value], 5 ), 1 ) * 5, 0 ), "hh:mm:ss")
, "15 Min Upper Band", FORMAT (TIME ( 0, CEILING ( DIVIDE ( [Value], 15 ), 1 ) * 15, 0 ), "hh:mm:ss")
, "30 Min Upper Band", FORMAT (TIME ( 0, CEILING ( DIVIDE ( [Value], 30 ), 1 ) * 30, 0 ), "hh:mm:ss")
, "45 Min Upper Band", FORMAT (TIME ( 0, CEILING ( DIVIDE ( [Value], 45 ), 1 ) * 45, 0 ), "hh:mm:ss")
, "60 Min Upper Band", FORMAT (TIME ( 0, CEILING ( DIVIDE ( [Value], 60 ), 1 ) * 60, 0 ), "hh:mm:ss")
)

The code above creates a table based on a list of numbers from 1 to 1440 with interval of 1. This integer number shows the number of minutes per day. So in case you want a Time dimension in Second level then you need to create a list of seconds from 1 to 86,400.

Click on the “Data” tab to see the data. If you look at the “Lower Band” and “Upper Band” columns you’ll notice that the values of the “Lower Band” columns start from 0 while the values of the “Upper Band” columns start with the band number. I created both columns to cover different scenarios when the customer prefers to start from 0 then you just simply remove the “Upper Band” columns or the other way around.

You may already noticed that the data type of the “Time” column is DateTime which is not right. To fix this, just click the “Time” column and change the data type to “Time” from “Modeling” tab

To make sure the Time shows in the correct order when added to the visuals I change the format to “HH:mm:ss”.

You need to do the same for all other time columns. The outcome should look like the screenshot below:

Now you are good to create the relationship between the “Time” dimension and the “FactInternetSales_WithTime” by connecting “OrderTime” from the fact table to “Time” column form the Time dimension.

Create Time Dimension with Power Query (M)

You just need to create a blank query in “Query Editor” and copy/paste the following Power Query codes.

let
Source = Table.FromList({1..1440}, Splitter.SplitByNothing()),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
#"Time Column Added" = Table.AddColumn(#"Renamed Columns", "Time", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0,0,[ID],0))),
#"5 Min Lower Band Added" = Table.AddColumn(#"Time Column Added", "5 Min Lower Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundDown([ID]/5) * 5, 0))),
#"15 Min Lower Band Added" = Table.AddColumn(#"5 Min Lower Band Added", "15 Min Lower Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundDown([ID]/15) * 15, 0))),
#"30 Min Lower Band Added" = Table.AddColumn(#"15 Min Lower Band Added", "30 Min Lower Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundDown([ID]/30) * 30, 0))),
#"45 Min Lower Band Added" = Table.AddColumn(#"30 Min Lower Band Added", "45 Min Lower Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundDown([ID]/45) * 45, 0))),
#"60 Min Lower Band Added" = Table.AddColumn(#"45 Min Lower Band Added", "60 Min Lower Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundDown([ID]/60) * 60, 0))),
#"5 Min Upper Band Added" = Table.AddColumn(#"60 Min Lower Band Added", "5 Min Upper Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundUp([ID]/5) * 5, 0))),
#"15 Min Upper Band Added" = Table.AddColumn(#"5 Min Upper Band Added", "15 Min Upper Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundUp([ID]/15) * 15, 0))),
#"30 Min Upper Band Added" = Table.AddColumn(#"15 Min Upper Band Added", "30 Min Upper Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundUp([ID]/30) * 30, 0))),
#"45 Min Upper Band Added" = Table.AddColumn(#"30 Min Upper Band Added", "45 Min Upper Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundUp([ID]/45) * 45, 0))),
#"60 Min Upper Band Added" = Table.AddColumn(#"45 Min Upper Band Added", "60 Min Upper Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundUp([ID]/60) * 60, 0))),
#"Changed Type" = Table.TransformColumnTypes(#"60 Min Upper Band Added",{{"Time", type time}, {"5 Min Lower Band", type time}, {"15 Min Lower Band", type time}, {"30 Min Lower Band", type time}, {"45 Min Lower Band", type time}, {"60 Min Lower Band", type time}, {"5 Min Upper Band", type time}, {"15 Min Upper Band", type time}, {"30 Min Upper Band", type time}, {"45 Min Upper Band", type time}, {"60 Min Upper Band", type time}})
in
#"Changed Type"

Create Time Dimension with T-SQL

Copy/paste the below T-SQL in SSMS to get the Time dimension in SQL Server. You can create a DimTime table f you uncomment the commented line and run the code.

WITH cte 
AS (SELECT 0 ID 
UNION ALL 
SELECT ID + 1 
FROM cte 
WHERE ID < 1439) 
SELECT ID 
, CONVERT(CHAR(5), Dateadd(minute, ID, '1900-01-01'), 108) [Time] 
, CONVERT(CHAR(5), Dateadd(minute, ( ID / 5 ) * 5, '1900-01-01'), 108) [5 Minutes Upper Band] 
, CONVERT(CHAR(5), Dateadd(minute, ( ID / 15 ) * 15, '1900-01-01'), 108) [15 Minutes Upper Band] 
, CONVERT(CHAR(5), Dateadd(minute, ( ID / 30 ) * 30, '1900-01-01'), 108) [30 Minutes Upper Band] 
, CONVERT(CHAR(5), Dateadd(minute, ( ID / 45 ) * 45, '1900-01-01'), 108) [45 Minutes Upper Band] 
, CONVERT(CHAR(5), Dateadd(minute, ( ID / 60 ) * 60, '1900-01-01'), 108) [60 Minutes Upper Band] 
, CONVERT(CHAR(5), Dateadd(minute, Ceiling (Cast(ID AS FLOAT) / 5) * 5, '1900-01-01'), 108) [5 Minutes Lower Band] 
, CONVERT(CHAR(5), Dateadd(minute, Ceiling (Cast(ID AS FLOAT) / 15) * 15, '1900-01-01'), 108) [15 Minutes Lower Band] 
, CONVERT(CHAR(5), Dateadd(minute, Ceiling (Cast(ID AS FLOAT) / 30) * 30, '1900-01-01'), 108) [30 Minutes Lower Band] 
, CONVERT(CHAR(5), Dateadd(minute, Ceiling (Cast(ID AS FLOAT) / 45) * 45, '1900-01-01'), 108) [45 Minutes Lower Band] 
, CONVERT(CHAR(5), Dateadd(minute, Ceiling (Cast(ID AS FLOAT) / 60) * 60, '1900-01-01'), 108) [60 Minutes Lower Band] 
--INTO DimTime
FROM cte 
OPTION (maxrecursion 0)

Then you can load the DimTime to Power BI Desktop and create the necessary relationships.

Visualising Data

Now you can easily analyse and visualise your data in Power BI. As you can see in all different implementations of the Time dimension regardless of the platform, you always have different columns to support different time bands. If you want to have dynamic timeband, then you have to unpivot the time dimension. I’d like to give credit to “Patrick Leblanc” from “Guy in a Cube” who explains how you can create dynamic axis in Power BI here. This is helpful particularly in those scenarios that you like to switch between different timebands and see the results immediately. I would not explain the technique again as Patric explains it quite clear on a step-by-step basis, so I encourage you to watch his video if you’d like to learn more. I just put the DAX code together for those of you who are wondering how to unpivot the table in DAX. It would become handy if you are working on a SSAS Tabular 2016 (or earlier) or if you’re working on a pure PowerPivot model and you don’t have access to Power Query to leverage the UNPIVOT functionality in M. Here is an example of visualising data on minute level based on various timebands.

Unpivot in DAX

At the time of writing this post, there is no built-in UNPIVOT function in DAX. So we have to somehow fake it. The below DAX code creates a calculated table based on the Time dimension we created earlier. Again, the whole thing gets more clear when you download the Power BI sample and have a look at the model structure.

Time in DAX Unpivot = UNION(
          SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID], "Time Band", "Time", "Time", 'Time in DAX'[Time])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "Time", "Time", 'Time in DAX'[Time])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "5 Min Lower Band", "5 Min Lower Band", 'Time in DAX'[5 Min Lower Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "15 Min Lower Band", "15 Min Lower Band", 'Time in DAX'[15 Min Lower Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "30 Min Lower Band", "30 Min Lower Band", 'Time in DAX'[30 Min Lower Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "45 Min Lower Band", "45 Min Lower Band", 'Time in DAX'[45 Min Lower Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "60 Min Lower Band", "60 Min Lower Band", 'Time in DAX'[60 Min Lower Band])
          
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "5 Min Upper Band", "5 Min Upper Band", 'Time in DAX'[5 Min Upper Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "15 Min Upper Band", "15 Min Upper Band", 'Time in DAX'[15 Min Upper Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "30 Min Upper Band", "30 Min Upper Band", 'Time in DAX'[30 Min Upper Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "45 Min Upper Band", "45 Min Upper Band", 'Time in DAX'[45 Min Upper Band])
          , SELECTCOLUMNS('Time in DAX', "ID", 'Time in DAX'[ID],  "Time Band", "60 Min Upper Band", "60 Min Upper Band", 'Time in DAX'[60 Min Upper Band])
          
                )

Click here to download the Excel, PBIX and SQL files.