微软最近更新了很多开窗函数,其内部参数对比以往的DAX函数来说,多了很多,这就导致学习的时间成本直线上升。
而且对于新增函数的应用场景,很多小伙伴也是一知半解的,本期我们就来聊一聊关于最近新增的开窗函数——OFFSET函数。
OFFSET函数基础语法
OFFSET ( <delta>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>] )
参数介绍:
delta:偏移量,正负数均可,可以是固定值,也可以是DAX表达式。
relation:表表达式,后续参数orderby,partitionby,matchby的内部参数,都需要来自它或相关表。
orderby:可选项,排序依据。
blanks:可选项,保留参数,可以忽略。
partitionby:可选项,分区定义,参照SQL的开窗分区即可。
matchby:可选项,定义匹配数据和标识当前行的列的语句。
PS:看到这里是不是有点晕?别慌,记住前2个参数即可,足以满足大部分应用场景。
接下来我们搭配应用场景来看一下如何使用OFFSET函数。
先来看看本期的案例数据:
案例数据就一张Sales的销售事实表,表结构也相对简单,将其导入到PowerBI中。
添加如下日期表,并建立模型关系。
Date =
GENERATE (
CALENDAR ( MIN ( 'Sales'[DATE] ), MAX ( 'Sales'[DATE] ) ),
VAR DA = [Date]
VAR YEAR =
YEAR ( DA )
VAR QUARTER =
"Q" & FORMAT ( DA, "Q" )
VAR MONTE =
FORMAT ( DA, "MM" )
VAR DAY =
DAY ( DA )
RETURN
ROW (
"Year", YEAR,
"Quarter", QUARTER,
"Month", MONTE,
"DayOfMonth", DAY,
"YearQuarter", YEAR & QUARTER,
"YearMonth", YEAR & MONTE,
"YearMonthCount",
YEAR * 12 + MONTE ----新增列
)
)
模型关系如下:
添加如下基础度量值:
销售数量:
Quantity =
SUM ( Sales[Quantity] )
环比:
Count环比% =
VAR CurrentCount =
SELECTEDVALUE ( 'Date'[YearMonthCount] )
VAR LastMonthCount = CurrentCount - 1
VAR LastMonthValue =
CALCULATE (
[Quantity],
FILTER ( ALL ( 'Date' ), 'Date'[YearMonthCount] = LastMonthCount )
)
VAR Result =
DIVIDE ( [Quantity] - LastMonthValue, LastMonthValue )
RETURN
Result
同比:
Count同比% =
VAR CurrentCount =
SELECTEDVALUE ( 'Date'[YearMonthCount] )
VAR LastYearMonthCount = CurrentCount - 12
VAR LastYearMonthValue =
CALCULATE (
[Quantity],
FILTER ( ALL ( 'Date' ), 'Date'[YearMonthCount] = LastYearMonthCount )
)
VAR Result =
DIVIDE ( [Quantity] - LastYearMonthValue, LastYearMonthValue )
RETURN
Result
结果如下:
到这里,我们的准备工作结束。
这是我们以往计算同环比时,白茶比较喜欢的写法之一。
开窗函数出来之后,我们可以换一种写法。
环比:
Offset环比% =
VAR LastMonth =
CALCULATE ( [Quantity], OFFSET ( -1, ALL ( 'Date'[Year], 'Date'[YearMonth] ) ) )
RETURN
DIVIDE ( [Quantity] - LastMonth, LastMonth )
同比:
Offset同比% =
VAR LastYear =
CALCULATE ( [Quantity], OFFSET ( -12, ALL ( 'Date'[Year], 'Date'[YearMonth] ) ) )
RETURN
DIVIDE ( [Quantity] - LastYear, LastYear )
结果如下:
从结果上看,二者是一致的,都没有太大的问题,但是从写法上看,开窗函数的写法无疑是简洁了很多。
代码解释:
1.OFFSET返回的结果为表,因此可以利用此特性,搭配CALCULATE使用;
2.利用第1参数偏移量,可以向前偏移1个月,或者多个月;
3.第2参数的设定,可以根据当前上下文设定,因为环比涉及到跨年问题,所以需要将年份字段添加进去。
我们来看一下执行性能:
从性能分析窗口,我们不难看出,开窗函数对比之前的写法,无疑是优化了很多。
除了前两个参数以外,我们来看看orderby的用法。
添加如下代码:
Orderby用法 =
CALCULATE (
[Quantity],
OFFSET (
-1,
ALL ( 'Date'[Year], 'Date'[YearMonth] ),
ORDERBY ( [Quantity], DESC )
)
)
结果如下:
从结果上我们不难看出,orderby更改了我们之前偏移的依据,原本我们是依据年月向前偏移的,orderby将其更改为参照销售数量。
blanks我们暂时忽略,那么partitionby是用来做什么的呢?
别急,我们添加如下代码查看:
Partitionby用法 =
CALCULATE (
[Quantity],
OFFSET (
-1,
ALL ( 'Date'[Year], 'Date'[YearMonth] ),
,
,
PARTITIONBY ( 'Date'[Year] )
)
)
结果如下:
partitionby的用途是为了进行分区,基本定义与SQL中是一致的,我们添加了年份字段进行分区,因为分区隔离的原因,向前偏移无法取到值,因此201801返回结果为空。
文章来源:https://www.toymoban.com/news/detail-626161.html
文章来源地址https://www.toymoban.com/news/detail-626161.html
到了这里,关于BI技巧丨利用OFFSET计算同环比的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!