Time Dimension
By Dani
Today I’m showing you my Dim Time table with flexible minute granularity. I have both DAX and Power Query versions.
Best Practices for Power BI dictate that dates and times be in separate columns. This lowers the cardinality of both the dates and times and makes the model more efficient.
I prefer my Time dimension as a DAX-generated table, but having the Power Query version is handy if you need to mash it up with other data. Both versions allow you to create whatever minute-based increment table you need such as every minute or every 15 minutes. For an hourly table, set every_n_minutes
to 60. Include a column that will match timekey
, (HHMM integer column) on your Fact table for the relationship.
DAX Version
DAX Time =
var every_n_minutes = 15 /* between 0 and 60; remainders in last hourly slice */
/* DO NOT CHANGE BELOW THIS LINE */
var slice_per_hour = trunc(DIVIDE(60,every_n_minutes),0)
var rtn =
ADDCOLUMNS(
SELECTCOLUMNS(
GENERATESERIES(0, 24 * slice_per_hour - 1, 1),
"hour24", TRUNC(DIVIDE([Value], slice_per_hour), 0),
"mins", MOD([Value], slice_per_hour) * every_n_minutes
),
"hour12", MOD([hour24] + 11,12) + 1,
"asTime", TIME([hour24], [mins],0),
"timekey", [hour24] * 100 + [mins],
"Hour", FORMAT(TIME([hour24], [mins],0), "H AM/PM")
)
return rtn
Power Query Version
let
every_n_minutes = 15,
/* DO NOT CHANGE BELOW THIS LINE */
slice_per_hour = Number.IntegerDivide(60, every_n_minutes),
hour_range = List.Generate(
() => 0,
each _ < 24 * slice_per_hour,
each _ + 1
),
Time = List.Transform(hour_range, each [
hour24 = Number.IntegerDivide(_, slice_per_hour),
mins = Number.Mod(_, slice_per_hour) * every_n_minutes,
hour12 = Number.Mod(hour24 + 11, 12) + 1,
asTime = #time(hour24, mins, 0),
Hour = Time.ToText(asTime, "h tt"),
timekey = hour24 * 100 + mins
]),
TimeTable = Table.FromRecords(Time),
#"Changed Type" = Table.TransformColumnTypes(TimeTable,{{"hour24", Int64.Type}, {"mins", Int64.Type}, {"hour12", Int64.Type}, {"asTime", type time}, {"Hour", type text}, {"timekey", Int64.Type}})
in
#"Changed Type"
Results
Here’s an example of the resulting table when every_n_minutes = 15. One thing to note is the time columns require formatting. The Power Query asTime column will come through formatted as Long Time, which may be what you want. The DAX asTime column will be formatted by default as General Date, showing a Date of 12/30/1899. If you use this column like a date in any calculations, that’s the date you’ll be calculating as. I reformat both these columns as Short Time. They are the main columns I display in visuals since they sort properly and look nice.