そもそも、なにを定義するのか。
Power BI service に配置されたPower BI データセットを参照する Excel ピボットテーブルがありまして、
Values area / 値 領域 に配置したメジャーが集計された結果をダブルクリックもしくはコンテキスト メニュー - [Show Detail / 詳細の表示] で追加される Excel テーブルをカスタマイズするということ。
既定で表示されるのは、メジャーのホームテーブルかつ表示可能な列のみ。リレーションシップの定義に使用されていないなど、純粋に集計対象となる値の列って感じかな。いずれにせよこのままでは役に立たないことが多いから、機能はできるだけ使えるようにしておきたいわけ。
使わないなら無効化しておいたらいいんじゃないかな。
Detail Row Expression を定義しながら確かめる
Detail Row Expression には評価結果がテーブルになる DAX 式が必要。
もっともシンプルな DAX 式
ファクトテーブルである Sales を参照する DAX 式: Sales を設定
すべての列を参照することができた。
このとき、出力された Excel テーブルのクエリコマンドは
DRILLTHROUGH MAXROWS 1000
SELECT FROM [Model]
WHERE (
(
(
[Measures].[Sales quantity],
[Dates].[Year].&[2017],
[Dates].[Month].&[1]
),
[Persons].[所属].&[東京本社]
)
)
であった。先頭行1000を除き DAX Query として読み替えると
EVALUATE
CALCULATETABLE(
'Sales',
'Dates'[Year] = 2018,
'Dates'[Month] = 1,
'Persons'[所属] = "東京本社"
)
と、なる。ピボットテーブルの行見出し列見出しなどが CALCULATETABLE function (DAX) - DAX | Microsoft Docs の filter 式に利用されるのだ。FILTER 関数じゃぁないですからね。
よくある工夫をしてみる
表示列を選択し、小計など追加。そして、人も読みやすいデータにする。
SELECTCOLUMNS(
'Sales',
"受注ID", 'Sales'[受注ID],
"担当者", RELATED( 'Persons'[氏名] ),
"商品区分", RELATED( 'Products'[区分名] ),
"商品名", RELATED( 'Products'[商品名] ),
"数量", 'Sales'[数量],
"小計", 'Sales'[単価] * 'Sales'[数量],
"受注日", 'Sales'[受注日]
)
DETAILROWS function (DAX) - DAX | Microsoft Docs でメジャーに定義された Detail Row Expression を参照できるので、例えば、
CALCULATE(
[Sales quantity],
SAMEPERIODLASTYEAR( 'Dates'[Date] )
)
というメジャーを定義した時、Detail Row Expression には、
CALCULATETABLE(
DETAILROWS( [Sales quantity] ),
SAMEPERIODLASTYEAR( 'Dates'[Date] )
)
とすることができる。
ね、Year = 2017 の行見出しからでも year = 2016 の行を取得できてるでしょ。
Power BI Desktop ではサポートされていないました
Power BI desktop に編集する機能はないし、外部ツールによる書き込み操作はサポートされないてます。
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 に限った話ではない。
思ったこと🙄
Server Analysis Service (Tabular mode) の Compatibility level 1400 からサポートされている機能なので、Power BI service でもサポートされているのだ。
XMLA endpoint を利用するツールの要件としては Analysis Services client libraries を利用していることなのでどうぞご自由になんだけど、Tabular Editor 3を使った。 open-source community version でも大丈夫のはず。
Tabular model script language(TMSL) を充分に扱えるなら、SSMS でも PowerShell でもなんでもよいよ。
その他