Power BI + Analyze in Excel で使いたいのでメジャーに Detail Row Expression を定義する。

そもそも、なにを定義するのか。
Power BI service に配置されたPower BI データセットを参照する Excel ピボットテーブルがありまして、
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/c3efce8d-8726-d257-e01c-32125b3f655a.png)
Values area / 値 領域 に配置したメジャーが集計された結果をダブルクリックもしくはコンテキスト メニュー - [Show Detail / 詳細の表示] で追加される Excel テーブルをカスタマイズするということ。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/8057fa87-0de9-5157-aa2c-d221d676c67d.png)
既定で表示されるのは、メジャーのホームテーブルかつ表示可能な列のみ。リレーションシップの定義に使用されていないなど、純粋に集計対象となる値の列って感じかな。いずれにせよこのままでは役に立たないことが多いから、機能はできるだけ使えるようにしておきたいわけ。 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/b35c7da0-5f85-dab2-5650-6010af375df7.png)
使わないなら無効化しておいたらいいんじゃないかな。

# Detail Row Expression を定義しながら確かめる
Detail Row Expression には評価結果がテーブルになる DAX 式が必要。

**もっともシンプルな DAX 式**
ファクトテーブルである Sales を参照する DAX 式: Sales を設定
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/05fc6cc7-d565-b68d-f8a1-b924eaf7e76a.png)
すべての列を参照することができた。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/b6698267-5196-ca3b-b6c1-b676eeeb2e2f.png)
このとき、出力された Excel テーブルのクエリコマンドは

```text:MDX
DRILLTHROUGH MAXROWS 1000
SELECT FROM [Model]
WHERE (
    (
        (
            [Measures].[Sales quantity],
            [Dates].[Year].&[2017],
            [Dates].[Month].&[1]
        ),
        [Persons].[所属].&[東京本社]
    )
)
```
であった。先頭行1000を除き DAX Query として読み替えると

```text:DAX&#x20;query
EVALUATE
CALCULATETABLE(
    'Sales',
    'Dates'[Year] = 2018,
    'Dates'[Month] = 1,
    'Persons'[所属] = "東京本社"
)
```
と、なる。ピボットテーブルの行見出し列見出しなどが [CALCULATETABLE function (DAX) - DAX | Microsoft Docs](https://docs.microsoft.com/en-us/dax/calculatetable-function-dax) の *filter* 式に利用されるのだ。FILTER 関数じゃぁないですからね。

**よくある工夫をしてみる**
表示列を選択し、小計など追加。そして、人も読みやすいデータにする。

```text:DAX&#x20;式
SELECTCOLUMNS(
    'Sales',
    "受注ID", 'Sales'[受注ID],
    "担当者", RELATED( 'Persons'[氏名] ),
    "商品区分", RELATED( 'Products'[区分名] ),
    "商品名", RELATED( 'Products'[商品名] ),
    "数量", 'Sales'[数量],
    "小計", 'Sales'[単価] * 'Sales'[数量],
    "受注日", 'Sales'[受注日]
)

```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/96a8f61c-6904-899b-db0b-d859a28b1dbe.png)

[DETAILROWS function (DAX) - DAX | Microsoft Docs](https://docs.microsoft.com/en-us/dax/detailrows-function-dax) でメジャーに定義された Detail Row Expression を参照できるので、例えば、

```text:メジャー&#x20;&#x3a;&#x20;Sales&#x20;quantity(SPLY)
    CALCULATE(
        [Sales quantity],
        SAMEPERIODLASTYEAR( 'Dates'[Date] )
    )
```

というメジャーを定義した時、Detail Row Expression には、

```text:DAX&#x20;式
CALCULATETABLE(
    DETAILROWS( [Sales quantity] ),
    SAMEPERIODLASTYEAR( 'Dates'[Date] )
)
```
とすることができる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/215ea983-015a-1b33-a85c-ba4d2e73b84f.png)
ね、Year = 2017 の行見出しからでも year = 2016 の行を取得できてるでしょ。

# Power BI Desktop ではサポートされて~~いない~~ました
Power BI desktop に編集する機能はないし、外部ツールによる書き込み操作はサポートされ~~ない~~てます。

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-external-tools
> **Supported write operations**

>- Define and edit measures for calculations, including format string, KPI, and **detail rows** settings.
- Add calculation groups for calculation reusability in complex models.
- Create perspectives to define focused, business-domain specific views of dataset metadata.
- Apply metadata translations to support multi-language versions within a single dataset.
- Add dataset roles for row-level security (RLS) and object-level security (OLS) rules to restrict data access.

なので、Power BI service 上に配置された データセットを編集するほかない。XMLA endpoint write を使用することになるので Premium 環境のみなんだけど、

- XMLA endpoint をサポートする Power BI データセット
- XMLA endpoint を利用できるワークスペース
- XMLA endpoint を利用できるユーザアカウント
- XMLA endpoint を利用できるテナント設定

が必要。Detail Row Expression に限った話ではない。

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-connect-tools
# 思ったこと🙄
Server Analysis Service (Tabular mode) の Compatibility level 1400 からサポートされている機能なので、Power BI service でもサポートされているのだ。

XMLA endpoint を利用するツールの要件としては Analysis Services client libraries を利用していることなのでどうぞご自由になんだけど、[Tabular Editor 3](https://tabulareditor.com/)を使った。 open-source community version でも大丈夫のはず。

https://github.com/TabularEditor/TabularEditor

Tabular model script language(TMSL) を充分に扱えるなら、SSMS でも PowerShell でもなんでもよいよ。

https://docs.microsoft.com/en-us/analysis-services/tmsl/tabular-model-scripting-language-tmsl-reference

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

http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981

http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9

- [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)