USERELATIONSHIP函数其实之前在写创建Date维度表的时候提到过,也没有什么特别的东西。不过最近有点喜欢用“Performance analyzer"去研究DAX Query。所以今天再详细写一个关于这个USERELATIONSHIP函数的验证,也许比较好玩。
先做一些准备工作:
1、首先建立一张Date的维度表,DAX代码如下:
Date = ADDCOLUMNS( CALENDARAUTO(),"Year Month Order", YEAR([Date]) *100+MONTH([Date]),"Month Name",FORMAT([Date],"mmm"),"Month Number",MONTH([Date]),"Year",YEAR([Date]))
TIPS: 这里特别加了一个”Year Month Order",这个是为了对时间Date维度做排序。一般我们是用年乘以100然后再加月份得到一个数字类型的值。然后对月份或者年月的排序用这个”Year Month Order",这样就避免了Year Month的text类型按照字母顺序排序。
2、建好表后,先用Sales表的Order Date和Date维度表做关系。因为本文主要是讲USERELATIONSHIP函数,所以还建立了Sales表的Delivery Date和Date维度表的虚关系,还有一个Sales表的Due Date和Date维度表的序关系。
对于一个Date维度表和一个Sales的事实表,只能建立一个实关系。
Sales表的Order Date和Date维度表建立的是实关系,即Active的关系。
Sales表的Delivery Date和Date维度表建立的就是虚关系。没有Active。
定义计算总的Quantity的函数如下:
Delivery Quantity = CALCULATE(SUM(Sales[Quantity]),USERELATIONSHIP('Date'[Date],Sales[Delivery Date]) )
Due Quantty = CALCULATE(SUM(Sales[Quantity]),USERELATIONSHIP('Date'[Date],Sales[Due Date])
参考官网:https://docs.microsoft.com/zh-cn/dax/userelationship-function-dax可以看出,USERELATIONSHIP函数只能作用在筛选器函数里面,出了筛选器函数就不起作用了。
随便拉一张表看看。
数值是不一样的。
先研究下这个Card的5491这个结果是怎么来的。
Copy Query得到如下结果:
DEFINE VAR __DS0FilterTable = TREATAS({2007}, 'Date'[Year])EVALUATE SUMMARIZECOLUMNS( __DS0FilterTable, "SumQuantity", IGNORE(CALCULATE(SUM('Sales'[Quantity]))) )
TREATAS:用来筛选Date表的Year列,只选2017年。
参考官网:https://docs.microsoft.com/zh-cn/dax/treatas-function
其中SUMMARIZECOLUMNS 函数用来计算总计。先筛选了Date维度表,然后按照Order Date和Date维度表中的默认Active关系,完成合计计算。
SUMMARIZECOLUMNS函数可参考官网:https://docs.microsoft.com/zh-cn/dax/summarizecolumns-function-dax。
然后再看看上图中Matrix矩阵表是怎么计算的:
DEFINE VAR __DS0FilterTable = TREATAS({2007}, 'Date'[Year])EVALUATE TOPN( 502, SUMMARIZECOLUMNS( ROLLUPADDISSUBTOTAL('Date'[Year], "IsGrandTotalRowTotal", 'Date'[Date], "IsDM1Total"), __DS0FilterTable, "Delivery_Quantity", 'Sales'[Delivery Quantity], "Due_Quantity", 'Sales'[Due Quantity], "SumQuantity", CALCULATE(SUM('Sales'[Quantity])) ), [IsGrandTotalRowTotal], 0, 'Date'[Year], 1, [IsDM1Total], 0, 'Date'[Date], 1 )ORDER BY [IsGrandTotalRowTotal] DESC, 'Date'[Year], [IsDM1Total] DESC, 'Date'[Date]
根据Date维度表的Date的Year和Date的Date来得到小计行汇总。
所以会看到Delivery Date和Due Date在Order Year是2017年的日期值。不是只有Order Date有值的日期。
虽然选择的日期是2017年,但实际上Delivery Date已经跨越到了2018年。
如果添加一个关于Delivery Date的Slicer筛选。这个才是真正Delivery Date对2017年的筛选。
写了这么多,证明来证明去。其实就是下面两句话:
USERELATIONSHIP函数只在CALCULATED函数里起作用,主要虚关系还是要建立的。USERELATIONSHIP函数只是开关,你还是要线连好了,开关才起作用;
一般情况下,还是按照默认关系(Active实关系)来计算的。
“Performance analyzer"是用来查性能的。你可以监察你的visual哪个比较慢。但是在实际项目调优上,我感觉还没怎么真正用到。有待进一步观察吧。
不过可以把DAX Query拿出来,还是很方便的。比如用Microsoft Management Studio打开Analysis Server,打开DAX 编辑页,直接贴这个DAX Query就很方便啊。懒人总是有好办法吧。
最近看到一个问题:计算去年和今年的对比。挺简单的,可以用SAMEPERIODLASTYEAR。
正确的DAX写法:
LYSP_Quantity = CALCULATE(SUM(Sales[Quantity]),SAMEPERIODLASTYEAR('Date'[Date]))
有的人用Filter筛选器函数用顺手了,写出了错误的DAX写法:
wrong_LYsp = CALCULATE(SUM(Sales[Quantity]),FILTER('Date',SAMEPERIODLASTYEAR('Date'[Date]) ))
得到的结果如下:
这个wrong_LYsp其实就是Order Date2018年的结果。
为什么呢?参考官网:https://docs.microsoft.com/zh-cn/dax/filter-function-dax
Filter筛选器函数,第二个参数是一个布尔表达式。
SAMEPERIODLASTYEAR返回一个非空的表,所以布尔表达式一直都是True。然后FILTER('Date',SAMEPERIODLASTYEAR('Date'[Date])返回的就是被筛选之后Order Date范围2018年。SAMEPERIODLASTYEAR('Date'[Date]),没有意义了。就是这样了。
福利:对比两列内容是否一样。
在Power Query Editer里面加载数据源,内容如下:观察A列和B列
如果想对比A列和B列的数据差异,只能做呢?很简单的。直接创建一个条件列。
如果在Power Query中得到A列和B列相同的内容为1,不同的为0。
具体操作如下图:
也可以用DAX写啊,之前分享Text函数的时候写过Exact。
Compare = var a = MAX('Sheet1'[A列])VAR b = MAX(Sheet1[B列])REturn EXACT(a,b)
拉出来瞧瞧:
都还是比较简单的吖~~
谢谢关注转发吖!