Quick Tips: Time Dimension with Granularity of Seconds in Power BI and Analysis Services Tabular Models

In an article I posted a while back I showed different methods of creating Time dimension in Power BI and Tabular models. The Time dimension I explained was in Minutes. In this post I show you simple way to create Time dimension supporting Seconds. As this is a quick tip, I only show you how to get the Time and ID columns in the Time dimension. If you need to add time bands (time buckets) check this out for more details.

Time Dimension in Seconds Grain with Power Query (M):

Copy/paste the code below in Advanced Editor to generate Time dimension in Power Query:

let
Source = Table.FromList({1..86400}, 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,0,[ID]))),
#"Changed Type" = Table.TransformColumnTypes(#"Time Column Added",{{"ID", Int64.Type}, {"Time", type time}})
in
#"Changed Type" 

Time Dimension in Seconds Grain with DAX:

Run the DAX expression below in a new calculated Table in Power BI or SSAS Tabular model:

Time in DAX = ADDCOLUMNS(
                       GENERATESERIES(1, 86400, 1)
                        , "Time", TIME(0, 0, 0) + [Value]/86400
                        )

In my previous post about Time dimension you see a different DAX expression to create Time dimension in Minutes granularity. You may ask why I used a different structure here? Well, the reason is that TIME function has limitation on accepting numbers bigger than 32,767. Therefore, if you use the below DAX expression you’ll get the “An argument of function ‘TIME’ has the wrong data type or the result is too large or too small.” error message. This limitation in inherited from TIME function in Excel.

Time in DAX with Error = ADDCOLUMNS(
                                    GENERATESERIES(1, 86400, 1)
                                    , "Time", TIME(0, 0, [Value])
                                    )

Here is another clever way of generating Time dimension in Seconds Jeffrey Wang posted on LinkedIn:

Time in DAX Jeffrey Version = GENERATESERIES(1/86400, 1, TIME(0, 0, 1))

Time Dimension In Seconds Grain with T-SQL:

Run the following T-SQL script in SSMS:

WITH cte
AS (SELECT 1 ID
UNION ALL
SELECT id + 1
FROM cte
WHERE id < 86400)
SELECT id
, CONVERT(CHAR(8), Dateadd(second, id, ‘1900-01-01’), 108) [Time]
FROM cte
OPTION (maxrecursion 0)

All done!