Power BI窗口函数应用于图表设计

wujunmin

发布于 2023-02-28 09:08:28

1.8K0

发布于 2023-02-28 09:08:28

文章被收录于专栏：wujunmin

Power BI于2022年12月推出的窗口函数极大简化了使用SVG矢量图自定义图表的过程。OFFSET、INDEX和WINDOW函数对设计连续型图表有重大意义。（不了解窗口函数参考采总此文：Power BI本月正式推出的DAX新函数：OFFSET、INDEX、WINDOW）

什么是连续型图表？连续性图表是指当前维度图表的内容和上一维度或下一维度存在关联。条形图柱形图是非连续型图表，因为每个柱子是独立分布的。折线图属于连续型图表，例如下图的纵向折线图，本行的折线走向受上一行和下一行影响。

瀑布图当前柱子的位置受上一个柱子的位置的影响。

OFFSET、INDEX、WINDOW分别实现了单行相对定位、单行绝对定位和任意范围的相对定位及绝对定位。以下以纵向折线图为例进行讲解。

纵向折线图每一行的折线形状由上一行数据、本行数据和下一行数据共同决定。比如，上一行数据50，本行数据20，下一行数据80，我们大体可以判断本行的折线走向大致如下图所示：

如何在计算本行折线的时候，让图表度量值知道上一行数据和下一行数据分别是多少？这就需要使用OFFSET函数进行上下偏移。如下是上一行和下一行的计算结果：

上一行 = CALCULATE([Value],OFFSET(-1,ALLSELECTED('日期表'[Date]),ORDERBY('日期表'[Date])))
下一行 = CALCULATE([Value],OFFSET(1,ALLSELECTED('日期表'[Date]),ORDERBY('日期表'[Date])))

以下是纵向折线的完整度量值，新建好度量值后，标记为图像URL，拖入表格。

纵向折线图 = 
VAR OffsetNext=CALCULATE([Value],OFFSET(1,ALLSELECTED('日期表'[Date]),ORDERBY('日期表'[Date])))
VAR OffsetLast=CALCULATE([Value],OFFSET(-1,ALLSELECTED('日期表'[Date]),ORDERBY('日期表'[Date])))
VAR MaxValue=MAXX(ALLSELECTED('日期表'[Date]),[Value])
VAR MinValue=MINX(ALLSELECTED('日期表'[Date]),[Value])
VAR Gap=MaxValue-MinValue
VAR r= 5 
VAR X_Last= (50-2*r) *(OffsetLast-MinValue)/Gap +r
VAR X= (50-2*r) *([Value]-MinValue)/Gap + r
VAR X_Next= (50-2*r) * (OffsetNext-MinValue)/Gap + r
VAR Y_Last= -25
VAR Y= 25
VAR Y_Next= 75
VAR SVG = "data:image/svg+xml;utf8,
    <svg xmlns='http://www.w3.org/2000/svg' width='50' height='50'>
        <polyline id='wujunmin'
            points='"&
                IF(OffsetLast<>BLANK(),X_Last &","&Y_Last&" ",BLANK())
                & X &","&Y&" "& 
                IF(OffsetNext<>BLANK(),X_Next &","&Y_Next,BLANK())&"' " & "
            stroke-width='1' stroke='black' fill='none'/>
        <circle cx='"&X&"' cy='"&Y&"' r='"&r&"' fill='" &IF([Value]>=30,"DarkCyan","tomato")&"' />
    </svg>"
RETURN
    SVG

此时你大概率得不到正确的结果，很可能显示如下图的断裂效果。这是因为水平网格线的存在切断了连线。

将网格线的宽度调整为0之后，即可得到一条连贯的折线。另外图像高度的设置与度量值中的高度保持一致（此处为50）。

这条折线还有第二种用法，放入条件格式的图标，下图右侧是条件格式模式：

有读者可能会有疑问，日期具有连续的特性，非连续的维度是否也可以同样操作？答案是可以的。下图的店铺业绩增长率是个示例：

这条折线的圆点设置了按条件变化颜色，在这基础上还可以进行深加工，比如加上数据标签：

目前（截止2022年12月）推出的窗口函数最神通广大的是WINDOW，一定程度上，可以只用WINDOW，而不使用OFFSET和INDEX。比如上方度量值在定义上一行和下一行的值时使用了OFFSET，现在替换为WINDOW如下所示。WINDOW需要指明定位范围，例如上一行（度量值中的offsetlast）定位的起点和终点都是-1，REL表示相对偏移。

VAR OffsetNext=CALCULATE([Value],WINDOW(1,REL,1,REL,ALLSELECTED('日期表'[Date]),ORDERBY('日期表'[Date])))
VAR OffsetLast=CALCULATE([Value],WINDOW(-1,REL,-1,REL,ALLSELECTED('日期表'[Date]),ORDERBY('日期表'[Date])))

度量值同样可以放于表格矩阵列显示，也可设置为条件格式图标，以下是条件格式效果。