Scribd
Upload
2 views13 pages

Window Function

The document provides a detailed overview of the WINDOW function, including its syntax and various parameters such as from, to, relation, and orderBy. It includes examples of how to use the WINDOW function to calculate values like growth rates and averages based on specified conditions. Additionally, it discusses partitioning and ordering data within the context of window functions.

Uploaded by

NEO SAREN
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views13 pages

Window Function

The document provides a detailed overview of the WINDOW function, including its syntax and various parameters such as from, to, relation, and orderBy. It includes examples of how to use the WINDOW function to calculate values like growth rates and averages based on specified conditions. Additionally, it discusses partitioning and ordering data within the context of window functions.

Uploaded by

NEO SAREN
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

WINDOW

–
&! "#$ % -Window !– -
( )

&89 $ 16 ( !* . , -! , . /, 0 1 - 2& 3 ,4 51 6 #! 1 7 ( Window !

( #! ( , = 6 9 1 ( #:& 1 9 & 8 $. $ 2& &8* $ %, 1 # . /, 0 #: #; < = 69 1

DE :, = 6 $ 5 BC < ? $ ( #:& , :, = 6 1 #>! . #? $ % /- @A . #

#0 1 1 . L 6 M, $= K 1 ( #:& :$ * . #? J ? 6 :? A, ! 3 :? A, 1 ( , ? FG &8

. !*

1 #>!

? -$ # , * L 1 $ ( = 5 . , 1M! !( 1 /- $ 8 ( Window ! J7?

. ? $ #; ( ; 8 $ , (B O ( 3 $= K 1 B, ( ? $ #;

WINDOW (
from[, from_type],
to[, to_type]
[, <relation> or <axis>]
[, <orderBy>]
[, <blanks>]
[, <partitionBy>]
[, <matchBy>]
[, <reset>]
)

1
&! "#$ % -Window !– -

! from A, 1 ) #? J ? S $ = 6 $ A, 2 &8 * ! , Q ( ? to from $ 8= 85*

$ 8* B , @A $ % 1 ? (? >T . , 2 UE 8 2 8 V! 0! ) * V #0 .(to A,

XG YK . FG A, :? 5 8 Z ? A, ( , * @A $ % 1 #Q . L : X G $( , U< 1 ABS (

: #? ( ?# 1 M ! ! ( != $ [ ? A, ! 2#, A,

WINDOW (3, ABS, 6, ABS …


4 5 11 . / 9 * . L: XG 5 $ A, 1 ( #:& 1 >T ( C 6 3 , "S $ = 6" ! * [& 8 8

! /, 0 = 6 9 ( L $" 1 7 1 B , . - # WINDOW ! #, *C #! 5# . , = 69 5 6 ;( ?

. & 1 #,

Simple Window 1 =
WINDOW (
3, ABS, 6, ABS,
ALL ( Alpha[Month_Number], Alpha[Month], Alpha[Session], Alpha[Cost] )
)
! 2#, $ A, 4^, & = 61 ^ 7 Alpha = 6 $5# , ( , ALL !1 B, 3 T S $ = 6 ] ! #0 (

. /2 :? #>! #, 6 ;. , ?XG [ ?

2 #>!

M!! a! YK _B` = 6M! !( * #; < = 6 $5# , ( / S $= 6 C( , FG

. WINDOW !" =# ( * b 3 ? (? 6 [Q = 69 6 ; #! ALL ! $5# , , * ?#

$ 3 :? #>! ( #, 6 ; :? 5 1 48 2 5# , 6 &8 ( , ? U A ? 1 #,

. . B! Q #?

Simple Window 2 =
WINDOW (
3, ABS, 6, ABS,
ALL (Alpha[Month], Alpha[Month_Number], Alpha[Session], Alpha[Cost] )
)

2
&! "#$ % -Window !– -

3 #>!

3 #? 2 & $5# , 2 c M ! ! ( 1 , M! M! "S $ = 6" !* . #& -6 WINDOW ! #0 * JC

* .# :? % , 1 , M! ? #6 7! # 2 3 #? M! 2 %, = 6 d#< = K

e : M! !( ( , < /BC S `) 2 M-` 1 , M! 1 48 d#< = K S $ = 6 . #? = / 5# , * ; ! 1 , M! J: C # ,

. , e : 6 ; = 6 * [ ? ! 2#, $ A, WINDOW ! . , 4 #>! ?

4 #>!

S $* [& 8 8. g G , [ ? ! 2#, $ A, 4^, #? M! :? % , S $= 6 , 21h J7 * J`

. / ? iY #, 5 . B , $5# , 1 , M! #0 * ! OrderBy !1 8* .

Simple Window 2 =
WINDOW (
3, ABS, 6, ABS,
ALL ( Alpha[Month], Alpha[Month_Number], Alpha[Session],Alpha[Cost] ),
ORDERBY(Alpha[Month_Number])
)

3
&! "#$ % -Window !– -

5 #>!

! * X j6 ! 6 $ 3 ?1# < 6 = 6 9 MC T 5 (L &89 g G, WINDOW !1 B,

1 =K * . $ !1 B," * ,=K 9 . #? #; < = k = 69 - !* ( , UJ T 1

2 8 ) $ % Z# $ 8 /- $ % ( [ ? (7 * ( * #? B, /- $%

A, :? S $ = 6 5 8 Z ? A, :? (- ) ( 5# /- ( , * cJ TMA * . #? FG REL ) (2 UE

6 A, 1 J/T A, -1 % 1 T 6 A, 1 J/T S $ = 6 ( A, ( ? YK . , . /, 0 - = 61 6

( 1 % ` 5# ! :`. ( +3 % , 6 A, 1 A, 3 ( A, ( ? M ! ! * :$ ( #? B , -2 % 1

. B, B %
I 6 A, m O ( ?

1 , -1 A, * /- % . ? ? /V J/T ( $ ) 5 A, $ ( #? (< 5# , 9 Alpha = 6 ( , T n<

. /6 :? #>! #? (< = 6( ( 6 5# , . , -1 & 8 5 8 A, % 3 #? g G , A, 9 o)< ( L&

PreMonthCost = WINDOW(-1,REL,-1,REL,ALL(Alpha[Cost]))

6 #>!

4
&! "#$ % -Window !– -

. Yq J ? ( $ J 7 ! 5# , 9 WINDOW !3 T = 6 2 UE A, A, ( A, n< ! #0 Up

9 ((6 ( * 3(7 #>!) -$ 7 O = 6 5# , * . , ? M! # .# ( , = 6 1 ( $ 5# ,

. #? /V ! A, ; ? g G , 135 ) 3 = 6 A, 1 !h A,

7 #>!

!( [ < q1 3 , J/T 1 $( $ < ; , ( $ 5# , = 6 ( , 5 =K * (7 9

* b /! #? s Z# # * 3 : & O != 6 * 4^, M! # .# ( ? g G , 5# , WINDOW

. #? J ` /? u ( < [Q ( $ 5# , ( 1 = 6 JK ) * 1 t A ? . ? , LU u ? T , (= 6

8 #>!
5
&! "#$ % -Window !– -

= 6 * b /! WINDOW !3 ? (? < $ ( $ 5# , T 3 / 1 #>! #! [Q = 6 J/T # , 6 (&

.(= , $ , < , b# =K :$) B, JK 7: 5# , 9 1 J C * :$ ( [ S $= 6

9 #>!

(/, 0 J/T ( /- ? x $ y1 ( 1 # , .[ o ?* WINDOW ! - #1 # E !J =K 9 1 B,

:? 5# , J ? A, * ( , ? B , J/T ( b# A, g G , PreMonthRow a 1 # , * * ?# . #?

* ( ) U!( , ,) , ? ;c PreMonthCost a , MAXX ! o,#! ( $ ) A, * 1 . , ( $

Cost 5# , 1 6 ( $ ) ThisMonthCost a .( #? 2 & 5 Q SUMX MINX JK #! 9: ( #! #6 A,

. #? (/, 0 ? x ? 5 ( < = 6

Growth_Rate =
VAR PreMonthRow = WINDOW(-1, REL,-1, REL, ALL( Alpha[Month_Number], Alpha[Cost]) )
VAR PreMonthCost = MAXX(PreMonthRow,[Cost])
Var ThisMonthCost = [Cost]
VAR Growth = ThisMonthCost - PreMonthCost
VAR GrowthRate = IF (PreMonthCost <> BLANK() , Growth / PreMonthCost )
RETURN
GrowthRate

6
&! "#$ % -Window !– -

10 #>!

3 #? (/, 0 ; (, ( $ * $ y1 ( n < . #: $ p0 * (/, 0 5 #! WINDOW !1

$ 1 1 # $ !* ! YK . : g G , 5 1 J/T A, 6 A, . Yq WINDOW !.# *

$ *C ( * *: .(11 :? #>!) [$ < !5 $ ( , , 57 : g G, ! ! U/

. ? $ #; B,2 = $ o)< 2 #? g G, 5 :$

11 #>!

7
&! "#$ % -Window ! – -
(, & 8 . #? ( ?# 3 , ( ( / ! ! ( M, # , % , * , 0 6 /- % , -2 J/T /- %

? (/, 0 * AVERAGEX ! o,#! & 8 * 1 Cost 5# , * , ? ;c LastThreeMonthWindow a ;

($ (, p 0 * ( $ ( $. a! 13 #>! . $ (MAverage 5# ,) # , * 6 J ` 12 :? #>! . ,

. 5 1[$ . # (

MAverage =
VAR LastThreeMonthWindow = WINDOW(-2,REL,0,REL,ALL(Alpha[Month_Number],Alpha[Cost]))
VAR MA = AVERAGEX(LastThreeMonthWindow,[Cost])
RETURN
MA

12 #>!

13 #>!

8
&! "#$ % -Window !– -

$eG 1 & 8 g G , = 6 9 9 7B! !* [ $ . #? < WINDOW ! $ 1 7 =K 9 1 B,

. , ? e : * E o; ? (/, 0 J>< 5 ( $ * J>< $ y 1 ( #: * 3 $ 1 #: . #? B, <

. , ? B, ![ $ PARTITIONBY ! 1 3J>< $ " < * (/, 0

14 #>!

1 A, 1 J>< $ & 8 % 1 #, . $ 2& = 6 $ A, , 1 J) - eG $ &8 $ % #! * 1 B,

3 ] ! -1 A, * ; :? @A $ % . , ? B , @A $ % 1 #? J ? J>< 5 :$ A, * ; !

. / 15 #>! (/, 0 * u . , ? B , 9 7B! ! 5 # ( J>< :? 5# , [ $ . , ( ` A, * ; =

Session Average =
VAR LastThreeMonthWindow =
WINDOW(1,ABS,-1,ABS,
ALL(Alpha[Month_Number],Alpha[Session],Alpha[Cost]),
ORDERBY(Alpha[Month_Number]),
DEFAULT,
PARTITIONBY(Alpha[Session])
)
VAR SA = AVERAGEX(LastThreeMonthWindow,[Cost])
RETURN
SA
* . , ? 1 89 $ e : 1 , M! 5 1 C; ) #0 3 , ? | 0C DEFAULT ) 5 ( [ $ 8*

. =#/T DEFAULT ) o)< `=`

9
&! "#$ % -Window !– -

15 #>!

FG 3 -$ 6 A, ] ! = 6 d /A pY ( L $5# , ! #? B, ! * 1 . , MATCHBY !(< T s0 # 1# $ ( !

WINDOW ! ( 6 () ? 2 J ? & 8 5# , * C # T( q;( J>< * 2 =K * L ?} . #?

,5 9 3 ? B,= 6 d /A S $ = 6 1 , M! ORDERBY !1 =K 5 . "# < <= 6 = 6 * b /!

$= 6 * d /A T 5# , 5 # ( :? 5# , 1 # , . = 6 d /A 5# , 2 [ FG MATCHBY !1 B, (

. ?(< 3 U J0 5 C; C t $ 8. , ? B,

Simple Window 3 =
WINDOW (3, ABS, 6, ABS,
ALL ( Alpha[Month], Alpha[Month_Number], Alpha[Session], Alpha[Cost] ),
, , ,
MATCHBY ( Alpha[Month_Number] )
)
, ? B , =,. B $ ] G =#>0 " < $ =K * .1 8 ( k ) (& , WINDOW ! ( ,#! ( : $ * = K * ;

. #? (/, 0 (=#>0 $ " < J `) ($ (, p 0 * ( , * ( ~- J` 1 S $ . (Sales = 6) 1 = 6 $ 1 G (

" < $ = 61 G

Year Month ‫ﻣﺎە‬ Product Income

١٤٠١ ١ ‫ﻓﺮورد ﻦ‬ Smart TV ٢١٧

١٤٠١ ١ ‫ﻓﺮورد ﻦ‬ Cellphone ٣٧٨

١٤٠١ ٢ ‫اردﯾﺒﻬﺸﺖ‬ Smart TV ٤٩٤

١٤٠١ ٢ ‫اردﯾﺒﻬﺸﺖ‬ Cellphone ٣٢٦

… … … … …

10
&! "#$ % -Window !– -

( [ -# (& , J C * :$ ( . #: (< $ = 6( #? (/, 0 5# , 9 MC T . /, 0 5 #! : ( , * J/T = K = K * . B!

* ( . (Q`Y 3 $ e : 9 7B! ( . Yq ( Table = k 1 #>! . (< " #6# = k ( 1 # . /, 0

. FG ; (, " < o,# ( #? (< #,= 6

16 #>!

S $= 6 $ A, * b /! ! . # d#< = 6 @/A $ = 6 1 , ( Y; SUMMARIZE !1 B, WINDOW !1 B,

(& . T (& , * " #0 ( . FG S $ = 6 SUMMARIZE ! 6 ; T . ? F G ! J T 4 5 1 -B = k $ A,

. , $ J T 17 :? #>! = 6 "p 0 * "2 0! (& , *

Moving Average =
VAR TargetTable =
SUMMARIZE (
ALLSELECTED ( Sales ),
Sales[Year], Sales[Month], Sales[ ],

"Total Income", SUM ( Sales[Income] )


)
VAR Win3Month =
WINDOW (-2, REL, 0, REL,
TargetTable,
ORDERBY ( Sales[Year], ASC, Sales[Month], ASC )
)
RETURN
AVERAGEX ( Win3Month, [Total Income] )

11
&! "#$ % -Window !– -

17 #>!

18 #>!

« ! "# Power_Of_BI M »

12

You might also like