最近有人提了个问题,计算Closed Date和Due Date之间差几天。马姐说可以写在source view里面,先做fact和date dimension的关系,然后在view里面计算差值。er...虽然我们SQL水平很行,但是没有必要做两次Join,就为了一个差值,完全可以用RELATED函数在Azure Analysis Service的模型里一次搞定啊。然后我突然发现,原来这个RELATED函数也可以写个分享的。
下面举的例子是在Azure Analysis Service里面完成的。你在Power BI里面模拟也是一样的。
首先,我准备了三张数据源表:一张Fact表,包含相对应的FK,两张Date Dimension表,可以用PK和Fact表的FK进行关联。
这里我要计算的是,Event Closed Date和Due Date之间的差值。
准备工作两步:
导入源数据表到Azure Analysis Serice;
创建关系dimension 表的PK和Fact表的FK关联
之后在Fact表里面创建Calculated Column,内容如下:
datediff:=
DATEDIFF(
RELATED('Event Closed Within Date'[Event Closed Within Date]),
RELATED('Date'[Date]),DAY
)
emm...很简单啊。如果你不用RELATED,直接把两个不直接相关的字段做表达式,会报错的,也不make sense是吧。
相应的,还有一个RELATEDTABLE。怎么讲呢?一般来讲,事实表fact和维度表关联,都是多对一的关系。想想,也可以理解。维度表里的信息,都是对于一个事物的具体描述,没有多余信息。比如Date日期维度表,里面最细粒度就是到了具体的一天,不会再细,但是也是每一天都包含的。但是fact事实表是具体发生事情的每一条记录,可能2019-10-24发生了好几笔订单,那对应的,肯定是多条事实表的record记录对应到Date日期维度表的201-10-24这一天。嗯,对生活中发生的一些事情抽象化,从概念模型转化到物理模型来分析数据的,我们是认真的。挺好玩的。
继续说RELATEDTABLE。ta和RELATED有点类似,不过返回的是一个表,所以ta的参数也要求是一个表。所以是不是有点懂了,如果计算2019-10-24这一天发生了几笔订单,你就要去数一数对应这个日期,一共有多少条实际发生在fact事实表的数据了。
随便举个例子,用的是Product维度表和Sales的事实表。
SalesRecords = COUNTROWS(RELATEDTABLE(Sales))
数数,我们还是要用COUNTROWS的,这个如果不太熟悉,以后再说说。
好了好了,随便给个彩蛋吧。
最近都没有怎么研究DAX什么的。写不出来啊。
在Edit Query这个页面。新建一个New Query查询,一个空白查询。然后把一下代码贴进去。就是自动生成了一个1到9999的数字列。因为我要的结果是0000到9999,所以做了一些小的调整。下面的M Query就是生成和调整的过程。随便拿去用吧。如果想生成000000到999999的,你改改看吧,差不多的。
let
Source =
List.Generate(()=>9999,each _ > 0,each _ -1 ),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Sorted Rows" = Table.Sort(#"Converted to Table",{{"Column1", Order.Ascending}}),
#"Added Prefix" = Table.TransformColumns(#"Sorted Rows", {{"Column1", each "00000" & Text.From(_, "en-US"), type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Added Prefix", "Column1", Splitter.SplitTextByPositions({0, 4}, true), {"Column1.1", "Column1.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.1", Int64.Type}, {"Column1.2", type text}})
in
#"Changed Type"
你可以把column1.1删掉的。不算简洁的方法,但是也还是比较好理解的。
欢迎关注转发。一起成长吧。
加油加油!