在Power BI中制作时间表的两种方式

日常分析用到的数据，有些时间维度，不仅仅只是日期，更细化到时间，包含时分秒，对这些数据的分析，仅仅利用日期表，就无法实现对小时、分钟、甚至是秒级粒度的分析。

如果需要按更细的时间粒度分析，直接的做法，是生成一个带有时间的日期表，一天有86400秒，如果每一天都都带上小时、分钟和秒，这样做的结果是日期表将变的特别大，仅仅是一年的日期表，行数也将达到365*86400，也就是3153.6万行，而10年的日期时间表将达到3亿多行。

还有一种办法是，单独建时间表，并且将分析数据中的日期/时间列拆分为日期列和时间列，然后日期表、时间表，分别与数据表建立关系，比如下面这个模型：

这样做，除了可以解决日期表太大的问题，对于数据表，拆分为单独的日期列和时间列，同样可以显著降低原来日期/时间列的基数，提高PowerBI的存储和运行效率。

时间表，应包括一天从0点0分0秒到23点59分59秒的的所有数据，以及对应的小时、分钟和秒数，为了分析的方便，通常还可以添加5分钟、15分钟、30分钟、上下午等粒度。

那么如何建立时间表呢，这里提供M和DAX两种方式。

1，在PowerQuery中利用M生成时间表

将以下代码输入到高级编辑器：

letSource = Table.FromList({1..86400}, Splitter.SplitByNothing()), #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}), #"Time Column Added" = Table.AddColumn(#"Renamed Columns", "时间", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[ID]))), #"Hour Added" = Table.AddColumn(#"Time Column Added", "小时", each Time.Hour([时间])), #"Minute Added" = Table.AddColumn(#"Hour Added", "分钟", each Time.Minute([时间])), #"Second Added" = Table.AddColumn(#"Minute Added", "秒", each Time.Second([时间])), #"5 Min Band Added" = Table.AddColumn(#"Second Added", "5分钟区间", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0, Time.Hour([时间]), Number.RoundDown(Time.Minute([时间])/5) * 5, 0))), #"15 Min Band Added" = Table.AddColumn(#"5 Min Band Added", "15分钟区间", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0, Time.Hour([时间]), Number.RoundDown(Time.Minute([时间])/15) * 15, 0))), #"30 Min Band Added" = Table.AddColumn(#"15 Min Band Added", "30分钟区间", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0, Time.Hour([时间]), Number.RoundDown(Time.Minute([时间])/30) * 30, 0))), #"AM/PM" = Table.AddColumn(#"30 Min Band Added", "AM/PM", each if [小时]<12 then "AM" else "PM"), #"Removed Other Columns" = Table.SelectColumns(#"AM/PM",{"时间", "小时", "分钟","秒","5分钟区间", "15分钟区间", "30分钟区间", "AM/PM"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"时间", type time}, {"小时", Int64.Type}, {"分钟", Int64.Type},{"秒", Int64.Type}, {"5分钟区间", type time}, {"15分钟区间", type time}, {"30分钟区间", type time},{"AM/PM", type text}})in #"Changed Type"

然后就可以生成一个时间表。

2、利用DAX生成时间表

生成上述时间表，同样可以用DAX完成，点击新建表，输入DAX表达式：

时间表_DAX = SELECTCOLUMNS( GENERATESERIES(1/86400, 1, TIME(0, 0, 1)), "时间", TIMEVALUE([Value]),"小时", HOUR ( [Value] ),"分钟", MINUTE ( [Value] ),"秒", SECOND( [Value] ),"5分钟区间", TIME(HOUR([Value]), FLOOR(MINUTE([Value])/5, 1) * 5, 0),"15分钟区间", TIME(HOUR([Value]), FLOOR(MINUTE([Value])/15, 1) * 15, 0),"30分钟区间", TIME(HOUR([Value]), FLOOR(MINUTE([Value])/30, 1) * 30, 0),"AM/PM", IF( HOUR([Value])<12,"AM","PM") )

结果和上面的时间表一致。

如果需要其他的时间字段，按照逻辑添加即可。

虽然PowerBI中有时间智能函数，但只能作用于日期，严格来说应该叫日期智能函数，对于更细的时间粒度，现在是没有智能函数的，那如何按时间分析呢，其实无论是对于日期，还是时间，都可以按通用的逻辑，用CALCULATE + ALL + FILTER的形式来表达。