日常分析用到的数据，有些时间维度，不仅仅只是日期，更细化到时间，包含时分秒，对这些数据的分析，仅仅利用日期表，就无法实现对小时、分钟、甚至是秒级粒度的分析。
如果需要按更细的时间粒度分析，直接的做法，是生成一个带有时间的日期表，一天有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的形式来表达。
-精彩推荐-
采悟 from PowerBI星球
网友评论