DAX workout - WINDOW functions

パラメータ多いからってビビってんじゃねぇっつーの🤪

- Formula Engine(FE)が多くの役割を果たしているから、サマライズされた結果に対して利用すると吉。
- パラメータは省略しないほうがよく、明示的に記述するべきだ。
- 発展途中だから利用シーンまで深く考えない。
- Visual Calculation がそのうち明らかになる
- Excel ピボットテーブルの [Show values as] を触れておくと吉
- (2023-02) ORDERBY 関数は計算列をサポート / blanks 列挙の変更
- (2023-03) ROWNUMBER 関数 / RANK 関数の追加


# 構文
重要なパラメータ *relation* / *orderBy* / *partitionBy* は共通
- OFFSET 関数 *delta* は相対位置
- INDEX 関数 *position* は絶対位置 
- WINDOW 関数 *from*, *to* は、*from_type*, *to_type* のそれぞれを ABS(絶対) / REL(相対)で定義可能

https://learn.microsoft.com/en-us/dax/offset-function-dax
```text:OFFSET 関数
OFFSET ( <delta>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>] )
```
https://learn.microsoft.com/en-us/dax/index-function-dax
```text:INDEX 関数
INDEX(<position>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>])
```
https://learn.microsoft.com/en-us/dax/window-function-dax
```text:WINDOW 関数
WINDOW (
    from[, from_type],to[, to_type][, <relation>][, <orderBy>][, <blanks>][, <partitionBy>] )
```

## パラメータ
### *relation*
***relation* 引数(テーブル)に含まれる行はユニークであること。**
```text:DAX query
DEFINE
    VAR SourceTable =
        DATATABLE(
            "Column1", STRING,
            "Column2", INTEGER,
            "COlumn3", INTEGER,
            {
                { "A", 100, 1 },
                { "C", 10, 2 },
                { "B", 1, 3 }
            }
        )

EVALUATE
INDEX(
    1,
    SourceTable
)
```
`INDEX's Relation parameter may have duplicate rows. This is not allowed.`
 
- *relation*引数(テーブル)に含まれる行がユニークであることを保証
- リレーションシップ 1 側、もしくは、キー列が定義されていればメタデータから判断
- 独立したテーブルや計算テーブルは DISTINCT で対応可
- OFFSET / WINDOW でも同じ。
***
***orderBy* パラメータが省略されているとき、*relation* 引数(テーブル)に含まれる列の一部もしくはすべてが利用され行の特定を試みる。**
```text:DAX query
DEFINE
    VAR SourceTable =
        DATATABLE(
            "Column1", STRING,
            "Column2", INTEGER,
            "COlumn3", INTEGER,
            {
                { "A", 100, 1 },
                { "C", 10, 2 },
                { "B", 1, 3 }
            }
        )

EVALUATE
INDEX(
    1,
    DISTINCT( SourceTable )
)
```
Column1 だけで行を特定している。
|Column1|Column2|Column3|
|:--|--:|--:|
|A|100|1|

```text
DEFINE
    VAR SourceTable =
        DATATABLE(
            "Column1", STRING,
            "Column2", INTEGER,
            "COlumn3", INTEGER,
            {
                { "A", 100, 1 },
                { "A",  10, 2 },
                { "A",   1, 3 }
            }
        )

EVALUATE
INDEX(
    1,
    DISTINCT( SourceTable )
)
```
Column1 の順序では行を特定できない。
なので、Column2 の順序の行を特定している。
|Column1|Column2|Column3|
|:--|--:|--:|
|A|1|3|

### *orderBy*
***orderBy* パラメータで定義した順序を"優先"**
```text
DEFINE
    VAR SourceTable =
        DATATABLE(
            "Column1", STRING,
            "Column2", INTEGER,
            "COlumn3", INTEGER,
            {
                { "A", 100, 1 },
                { "C", 10, 2 },
                { "B", 1, 3 }
            }
        )

EVALUATE
INDEX(
    1,
    DISTINCT( SourceTable ),
    ORDERBY( [Column1] )
)
```
orderBy で定義した順序で行を特定できた。
|Column1|Column2|Column3|
|:--|--:|--:|
|A|1|3|

```text:DAX query
DEFINE
    VAR SourceTable =
        DATATABLE(
            "Column1", STRING,
            "Column2", INTEGER,
            "COlumn3", INTEGER,
            {
                { "A", 1, 2 },
                { "A", 1, 3 },
                { "A", 1, 1 }
            }
        )

EVALUATE
INDEX(
    1,
    DISTINCT( SourceTable ),
    ORDERBY( [Column1] )
)
```
Column1 の順序では行を特定できない。
Column2 の順序でも行を特定できない。
Column3 の順序で行を特定した。
|Column1|Column2|Column3|
|:--|--:|--:|
|A|1|1|

```text:DAX query
DEFINE
    VAR SourceTable =
        DATATABLE(
            "Column1", STRING,
            "Column2", INTEGER,
            "COlumn3", INTEGER,
            {
                { "C", 1, 1 },
                { "A", 1, 2 },
                { "B", 1, 3 }
            }
        )

EVALUATE
INDEX(
    -1,
    DISTINCT(
	    SELECTCOLUMNS(
		    SourceTable,
		    "Column2", [Column2],
		    "Column3", [Column3]
		)
	),
	ORDERBY( [Column2] )
)
```
Column1 は relation 引数(テーブル)に含まれていないので対象外
Column2 の順序では行を特定できない。
Column3 の順序で行を特定した。
|Column2|Column3|
|--:|--:|
|1|3|
***
***orderBy* パラメータで定義する列は *relation* 引数(テーブル)に含まれてなければならない。**
```text:DAX query
DEFINE
    VAR SourceTable =
        DATATABLE(
            "Column1", STRING,
            "Column2", INTEGER,
            "COlumn3", INTEGER,
            {
                { "C", 1, 1 },
                { "A", 1, 2 },
                { "B", 1, 3 }
            }
        )

EVALUATE
INDEX(
    -1,
    DISTINCT(
	    SELECTCOLUMNS(
		    SourceTable,
		    "Column2", [Column2],
		    "Column3", [Column3]
		)
	),
	ORDERBY( [Column1] )
)
```
`OrderBy column 'Column1' could not be found in INDEX's Relation parameter.`
***
***relation* パラメータが省略されているとき、*orderBy* 引数から *relation* 引数(テーブル) を `ALLSELECTED( ... )` と想定される。**
```text:DAX query
// Product テーブル:キー列定義済み
EVALUATE
INDEX(
    1,
    // ALLSELECTED( 'Product'[商品名] ),
	ORDERBY( 'Product'[商品名] )
)
```
***
***orderBy* 引数の定義順は有意**
```text:DAX query
// Product テーブル:キー列定義済み
EVALUATE
INDEX(
    1,
    // ALLSELECTED( 'Product'[単価], 'Product'[商品名] )
	ORDERBY( 'Product'[単価], ASC, 'Product'[商品名], ASC )
)
```
|単価|商品名|
|--:|:--|
|50|のり山椒|

```text:DAX query
// Product テーブル:キー列定義済み
EVALUATE
INDEX(
    1,
    // ALLSELECTED( 'Product'[単価], 'Product'[商品名] ),
	ORDERBY( 'Product'[商品名], ASC, 'Product'[単価], ASC )
)
```
|商品名|単価|
|:--|--:|
|あおのりあじさい|200|
***
**集計列の結果から順序を特定**
```text:DAX query
EVALUATE
INDEX(
    1,
    ADDCOLUMNS(
    	VALUES( 'Product'[区分名] ),
    	"@SalesAmount", [Sales amount]
    ),
	ORDERBY( [@SalesAmount] )
)
```
|区分名|`@SalesAmount`|
|:--|--:|
|加工食品|2238700|

```text:DAX query
// Product テーブル:キー列定義済み
EVALUATE
WINDOW(
    1, ABS, 3, ABS,
    ADDCOLUMNS(
    	VALUES( 'Product'[区分名] ),
    	"@SalesAmount", [Sales amount]
    ),
	ORDERBY( [@SalesAmount] )
)
```
|区分名|`@SalesAmount`|
|:--|--:|
|加工食品|2238700|
|乳製品|2374100|
|菓子類|2819500|

```text:DAX query
// Product テーブル:キー列定義済み
EVALUATE
OFFSET(
    1,
    ADDCOLUMNS(
    	VALUES( 'Product'[区分名] ),
    	"@SalesAmount", [Sales amount]
    ),
	ORDERBY( [@SalesAmount] )
)
```
**2023-01まで**
`Function 'ORDERBY' cannot be used with columns added by the ADDCOLUMNS or SUMMARIZE functions.`
**2023-02から**
追加された計算列もサポート

### *partitionBy*
***partitionBy* パラメータを定義するときには、少なくとも relation もしくは orderBy パラメータの定義が必要**
```text:DAX query
// Product テーブル:キー列定義済み
EVALUATE
INDEX(
	1,
	PARTITIONBY( 'Product'[区分名] )
)
```
`'INDEX' must specify the Relation or OrderBy parameter before the PartitionBy parameter.`
***
***relation* パラメータが省略されているとき、*orderBy* 引数と *partitionBy* 引数から *relation* 引数(テーブル) を ALLSELECTED( ... ) と想定される。** ただし、同一テーブルに含まれる列の組み合わせに限る。
```text:DAX query
// Product テーブル:キー列定義済み
EVALUATE
INDEX(
	1,
	// ALLSELECTED( 'Product'[商品名], 'Product'[区分名] ),
	ORDERBY( 'Product'[商品名] ),
	PARTITIONBY( 'Product'[区分名] )
)
```
```text:DAX query
EVALUATE
INDEX(
	1,
	ORDERBY( 'Product'[区分名] ),
	PARTITIONBY( Dates[Year] )
)
```
`INDEX's Relation parameter is omitted. In this case, all OrderBy and PartitionBy columns must be from the same table.`

***
***partitionBy* パラメータで定義する列は *relation* 引数(テーブル)に含まれてなければならない。**
```text:DAX query
// Product テーブル:キー列定義済み
EVALUATE
INDEX(
	1,
	ALLSELECTED( 'Product'[商品名], 'Product'[区分名] ),
	PARTITIONBY( 'Product'[単価] )
)
```
`PartitionBy column '単価' could not be found in INDEX's Relation parameter.`
***
```text:DAX query
DEFINE
    VAR SourceTable =
        DATATABLE(
            "Level 1", STRING,
            "Level 2", INTEGER,
            "Value 1", INTEGER,
            {
                { "C", 1, 1 },
                { "A", 2, 2 },
                { "B", 1, 3 },
                { "A", 0, 4 },
                { "C", 1, 5 }
            }
        )

EVALUATE
INDEX(
	1,
	DISTINCT( SourceTable ),
	PARTITIONBY( [Level 1] )
)
```
|Level 1|Level 2| Value 1|
|:--|--:|--:|
|C|1|1|
|A|0|4|
|B|1|3|

```text:DAX query
DEFINE
    VAR SourceTable =
        DATATABLE(
            "Level 1", STRING,
            "Level 2", INTEGER,
            "Value 1", INTEGER,
            {
                { "C", 1, 1 },
                { "A", 2, 2 },
                { "B", 1, 3 },
                { "A", 0, 4 },
                { "C", 1, 5 }
            }
        )

EVALUATE
INDEX(
	1,
	DISTINCT( SourceTable ),
	PARTITIONBY( [Level 2] )
)
```
|Level 1|Level 2| Value 1|
|:--|--:|--:|
|B|1|3|
|A|2|2|
|A|0|4|

```text:DAX query
DEFINE
    VAR SourceTable =
        DATATABLE(
            "Level 1", STRING,
            "Level 2", INTEGER,
            "Value 1", INTEGER,
            {
                { "C", 1, 1 },
                { "A", 2, 2 },
                { "B", 1, 3 },
                { "A", 0, 4 },
                { "C", 1, 5 }
            }
        )

EVALUATE
INDEX(
	1,
	DISTINCT( SourceTable ),
	ORDERBY( [Value 1] ),
	PARTITIONBY( [Level 1] )
)
```
|Level 1|Level 2| Value 1|
|:--|--:|--:|
|C|1|1|
|A|2|2|
|B|1|3|

```text:DAX query
DEFINE
    VAR SourceTable =
        DATATABLE(
            "Level 1", STRING,
            "Level 2", INTEGER,
            "Value 1", INTEGER,
            {
                { "C", 1, 1 },
                { "A", 2, 2 },
                { "B", 1, 3 },
                { "A", 0, 4 },
                { "C", 1, 5 }
            }
        )

EVALUATE
INDEX(
	1,
	DISTINCT( SourceTable ),
	PARTITIONBY( [Level 1], [Level 2] )
)
```
|Level 1|Level 2| Value 1|
|:--|--:|--:|
|C|1|1|
|A|2|2|
|B|1|3|
|A|0|4|

### *blanks*
順序での BLANK の扱いを決定するパラメータ
- KEEP(既定値)
- FIRST
- LAST
- ~~SKIP~~
- DEFAULT

>**2022-12**
(Optional) An enumeration that defines how to handle blank values when sorting.
This parameter is reserved for future use.
Currently, the only supported value is KEEP (default), where the behavior for numerical/date values is blank values are ordered between zero and negative values. The behavior for strings is blank values are ordered before all strings, including empty strings.

# サンプル
```text:Measure
Sales amount Top 3 = 
CALCULATE(
	[Sales amount],
	KEEPFILTERS(
		WINDOW(
			1, ABS, 3, ABS,
			ADDCOLUMNS(
				SUMMARIZE(
					ALLSELECTED( Sales ),
					'Product'[区分名], 'Product'[区分ID]
				),
				"@Sales", [Sales amount]
			),
			ORDERBY( [@Sales], DESC, 'Product'[区分ID], ASC ),
		)
	)
)
```
```text:Measure
Sales amount Other] = 
CALCULATE(
	[Sales amount],
	KEEPFILTERS(
		WINDOW(
			4, ABS, 8, ABS,
			ADDCOLUMNS(
				SUMMARIZE(
					ALLSELECTED( Sales ),
					'Product'[区分名], 'Product'[区分ID]
				),
				"@Sales", [Sales amount]
			),
			ORDERBY( [@Sales], DESC, 'Product'[区分ID], ASC )
		)
	)
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/6fcb7086-9e1e-e8ee-5064-3bfaf9ee315c.png)

```text:Measure
Sales amount Cumulated = 
CALCULATE(
    [Sales amount],
    WINDOW(
        1, ABS, 0, REL,
        SUMMARIZE(
            ALLSELECTED( Sales ),
            'Product'[区分ID], 'Product'[区分名], 'Product'[商品ID], 'Product'[商品名]
        ),
        ORDERBY( 'Product'[区分ID], ASC, 'Product'[商品ID], ASC ),
        KEEP,
        PARTITIONBY( 'Product'[区分ID] )
    )
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/b1703cb2-b978-624e-0c99-0d8c536571dc.png)

```text:Measure
Difference = 
IF(
    ISINSCOPE( 'Product'[区分名] ),
    [Sales amount]
    - CALCULATE(
        [Sales amount],
        INDEX(
            [Parameter Rank],
            ADDCOLUMNS(
                SUMMARIZE(
                    ALLSELECTED( Sales ),
                    'Product'[区分名], 'Product'[区分ID]
                ),
                "@Sales", [Sales amount]
            ),
            ORDERBY( [@Sales], DESC, 'Product'[区分ID], ASC )
        )
    )
)
```
<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">新しい関数使ってみたものの、パンチが効いたネタにならない🤪 <a href="https://twitter.com/hashtag/PBIJP?src=hash&amp;ref_src=twsrc%5Etfw">#PBIJP</a> <a href="https://t.co/vsUWOgdKkI">pic.twitter.com/vsUWOgdKkI</a></p>&mdash; Takeshi Kagata / Power Platform 部 Power BI 科 🛌💤 (@PowerBIxyz) <a href="https://twitter.com/PowerBIxyz/status/1605079245142691840?ref_src=twsrc%5Etfw">December 20, 2022</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

```text:DAX query
// 区分名ごとのパーティションで商品名ごとの受注額を降順で累積
EVALUATE
SUMMARIZECOLUMNS(
	'Product'[区分名],
	'Product'[商品名],
	"受注額", [受注額],
	"CumulativeSales",
		SUMX(
			WINDOW(
				1, ABS, 0, REL,
				ADDCOLUMNS(
					SUMMARIZE(
						ALLSELECTED( Sales ),
						'Product'[区分名], 'Product'[商品名]
					),
					"@Sales", [受注額]
				),
				ORDERBY( [@Sales], DESC, 'Product'[商品名], ASC ),
				PARTITIONBY( 'Product'[区分名] )
			),
			[@Sales]
		)
)

ORDER BY
	'Product'[区分名],
	[受注額] DESC
```

# 思ったこと🙄
今年のことは今年のうちに。

# その他
https://powerbi.connpass.com/

- [Power BI 勉強会 - connpass](https://powerbi.connpass.com/)
- [Power BI Desktop を使い始めるには](http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981)
- [Excel で Power Query を使い始めるには](http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9)