先般のイベント続編として Power BI 勉強会 #18 でお話したこと再生しつつ思うところを追加するなど。これから勉強するとか勉強始めた人を想定して、これだけは守った方がよいよという内容にしていたのが前回。そこで取り上げきれなかった メジャーってどうやって勉強すりゃいんだって話をしたわけである。Apocalypse に意味はなかった。
そもそも メジャーってなんなんだぜ
集計の定義ですよ
以前にポストしたことはあるのだけど、集計の定義 なのである。
メジャーを定義することなくPower BI Desktop や Power BI Service で レポートを作成することはもちろん可能ではあるけれども、メジャーが使われていないレポートって存在しないんですよね。しいて言えば、レポートのキャンバスにビジュアルが配置されていないレポートだけがメジャーを使用していないレポートくらいでしょうか。それって意味がないけれども。
特段の定義することなくても、データを集計しビジュアルに含めて投影することはできるけれども、これは暗黙的に定義されたメジャーが利用されているだけ。これは単純なものにのみ対応なので、明示的に もしくは 集計の要件にあわせてメジャーを定義しなければならないのである。
DAX っていう言語を使う
メジャーは DAX (Data Analysis Expression) という言語を用いて記述する。
メジャー以外だと 計算テーブル / Calculated table, 計算列 / Calculated column でも使うし、ビジュアルに投影されるデータを取得する DAX クエリでも使われるネイティブの関数型言語。Excel の関数に寄せていることが多く結構見慣れた命名になっているはずで、使われ方が大きく異なってたとしても、なんとなく何をする関数なのかな？ということは感じることはあるのでしょう。
DAX 関数は Excel のワークシート関数と異なり非スカラ値を取り扱うことが多いのだけど、それがむずかしいと思ってしまう理由の一つなんだろうなと。ただ、レポート上でスライサなどビジュアルを操作などの動的な集計が簡単にできる仕組みになっているのだから、Excel ワークシート関数よりシンプルな記述になって簡単なはずなんだよね。でもむずかしいと。きっと、ワークシートのように考える癖が邪魔しているんですよ。Excel ワークシート上で Dynamic array を扱う関数が出てきているのだから、セルの概念だけじゃ通用しないこともあるんじゃないかな。
工夫をしようぜ
どのあたりが難しいのか理解することはさほど重要ではないし、まぁ頑張ってというお気持ちではあるのだけど、理解する/勉強する工夫はしたほうがよいですよと思うのです。
基本に忠実で自分なりの整理
基本的なところから始めるというのは多くのことで共通なんだけど、むずかしいむずかしいっていう人ほど基本的なことから始めていない傾向があるんですよね。本を読んだり誰かから学んだりということでなくて、実際に触れてみてどのような動作になるのかじっくり観察することが大事なのではなかろうか。"思った通りにならないムキーっ！" じゃなくて、実現したいことはどのようなことだったのか、そして実際に起きたことはどのようなことなのか。理由を探しあてるのはもう少し先の話でもよくて、期待していない結果とはいったい何者？って考えるとよいのかなと思うことしばしば。
勉強用のデータモデル テンプレート
工夫の一つとしてテンプレート(Power BI Desktop から エクスポートしたテンプレート)を用意したほうがよい。なるほどなぁと学んだ記述 / そういえばと思いついたロジック などを 一定のデータモデルで再現し記録として保存するのです。同じデータモデルであるから、用途の異なる集計であれば一目瞭然で違いがわかるし、異なる記述をした場合で得られる集計が期待するものであるのかどうかなどささっと確認できるじゃないですか。まだ知りえていないことが起因する問題が教材題材に発生してしまうと、集中して勉強できないですよね。
どのようなデータモデルがよいのだろうと思ったら
- Understand star schema and the importance for Power BI - Power BI | Microsoft Docs
起きていることを観察
評価コンテキスト / Evaluation context という概念があって、フィルタ コンテキスト / Filter context, 行コンテキスト / Row context というものがあるのだけど、いきなりこれらキーワードで検索して調べても、その理解はとても大変なんですよ。なので、まずはこんな動作をするんだなということを感じておくとよいかなと思うのです。
メジャーを記述するとき、学ぶとき、どのようなことを考え整理するか。
まずは、よく知っているデータモデルが一番よいのだけど、実際の業務データって思いのほか複雑だったり、最終的に必要となるビジネス要件を満たす実力がまだ備わっていないのであれば シンプルな データモデル を別途用意したほうがよいですよ。で、こんな感じで考えた。
ファクトテーブル : Sales
ディメンジョンテーブル : Products
商品を特定するキー [商品ID] でリレーションシップが定義されているから、Products に含まれる商品の属性( 区分名 や 商品名) を使った集計ができるデータモデル
受注額 = SUMX( 'Sales', 'Sales'[数量] * 'Sales'[単価] )
メジャーはひとつだけ用意していて、Sales テーブルの行ごとに式を評価し、得られた結果の和を [受注額] と定義しているだけ。
ビジュアルの挙動を観察し理解する
いちばん理解しやすい テーブル ビジュアルで考えるとよいはず。で、そういえばこんな勉強したっけなぁというポイントを羅列しておく。
必ず集計された結果が投影されること
テーブル ビジュアル に Products[区分名] を 値 / Value として設定した。
商品テーブルの[区分名] の値は 8種類存在することはわかった。だけど、商品はたった 8つでもないし、同じ区分には複数の商品が属しているのにだ。自然な動作なんだけれども、テーブル ビジュアルに表示されている [区分名] は集計された結果を表示しているのです。
メジャーが評価されるとき フィルタ が適用されること
テーブル ビジュアルに 定義したメジャー [受注額] を 値 / Value に追加した。
これも驚くこともない自然な動作である。ただし、ビジュアル上で評価されたメジャーはひとつしかないのに、ここでは 9種類の評価結果を表示された。さて、これはどういうことなんだい🤔
キャンバスに配置された ビジュアルは データモデルのことを知らないし、データモデルも ビジュアルのことを知らない。実際にお互いのことを知らなくてもよい仕組みになっているのだけど、その詳細はいまここで必要なことではないので、ビジュアル からの観点でどんな感じに動作しているのかなと考えた。例えば、
テーブル ビジュアルの "飲料" の行を表示しようとするとき、メジャー [受注額] の評価をするけれども、[区分名] が ”飲料” である商品のみの結果が必要であるという要求をしているのであろうと。残り他の行でも同様の繰り返しが行われていると考えるとよさそうだ。
では、合計の行とはなんだろうか？
これ、思いのほか勘違いしている人多くて、[区分名] ごとの評価結果を加算したものではない。このビジュアルで例えると [区分名] についての フィルタが適用されていない評価結果なんですよね。
受注額 = SUMX( 'Sales', 'Sales'[数量] * 'Sales'[単価] ) * 0 + 1
無意味な メジャーになってしまうけどこんな結果になる。
ね、合計行の結果が 8 にならないでしょ。
メジャーを記述するとき考えること
式エディタを睨みつけてうんうん唸っていても解決しないし、やみくもにカチャカチャタンッしてもうまくいくわけなしである。で、どのような時に使う関数なのか、どのような記述をするのか、わからないままリファレンス読んでも DAX 語彙力伸びないですよね。特に手始めの段階で使用する関数はごくわずかだし、レベルアップしたからといって使う関数が大幅に増えるということはない。なので、噛まずに飲み込む丸暗記はあまり役に立たないし、丸暗記するようなことがそもそもないよね。
集計に必要なロジックとは
お題として、パレートチャートに必要な集計をメジャーに記述するにはどうするか。
パレートチャートに必要なのは値のひとつ累積構成比。このデータモデルで必要とした場合、商品ごとの [受注額] を降順で累積したときの構成比を計算したい。ただ、構成比を累積するのではなく [受注額] の累積と全体の[受注額] の比率を計算したほうがシンプルで都合がよいはず。
用意したロジックを段階的に記述
一気に記述することはどんな時でも無理なので整理しながら進めるとよいですよね。だって、勉強なんですし。勉強でなくてもこうするし。
なんなら Excel で考えるなど
いっそのこと Excel で ロジックを再現しつつ整理する。
Excel テーブル : Sales を用意した。これは ファクトテーブルの Sales テーブルをサマライズして模したもの。
必要なロジックをテーブルの列と追加していく。
ポイント
- Excel テーブルを使う
- セル参照を使わない
区分ごとの受注額が必要
だって比較するのに使いますから。
[受注額]の累計を計算
ただし、区分ごとの[受注額] 以上を対象とする。
全体の[受注額]を計算
全体の[受注額]での構成比だから。
それぞれの構成比を計算
できた
ね、かんたんでしょ
メジャーを記述してみる
Excel を使って DAX でも通用するような感じを検討したので、メジャーを記述してみる。
区分ごとの受注額が必要
メジャー =
VAR CurrentValue = [受注額]
RETURN
CurrentValue
ここは難しいポイントはないですよね、テーブルビジュアルの行ごとで自動的にフィルタが適用されているわけです。
[受注額]の累計を計算
ここからが難関ポイント。なので、さらにどういうロジックにするかさらに整理する必要がある。ひとまず結果としては期待するものではある。
メジャー =
VAR CurrentValue = [受注額]
VAR CumulatedValue =
CALCULATE(
SUMX(
FILTER(
ADDCOLUMNS(
VALUES( Products[区分名] ),
"@Value", [受注額]
),
[@Value] >= CurrentValue
),
[@Value]
),
ALL( Products )
)
RETURN
CumulatedValue
VALUES ってなんだ
なんだろうと思ったら試すこと大事。
メジャー =
VAR CurrentValue = [受注額]
VAR CumulatedValue =
VALUES( Products[区分名] )
RETURN
CumulatedValue
このメッセージが何を言っているのかなんだけど、ビジュアルの フィールドなどに投影する値が スカラ値じゃないからだってことを言ってます。Excel ワークシート上の セルで表示される値は スカラ値でしょ。それと同じで表示できない値ですよと言っている。引数に列を指定したとき VALUES 関数は重複を除外した列がひとつのテーブルを返すので、"VALUES( Products[区分名] )" は Products[区分名] のユニークな値セットがメジャーの評価結果になっているわけだ。で、このテーブル ビジュアル で何が起きていたか。実は合計行の表示ができなかったということなんだけど、実際に合計の表示をOFFにするとこうなる。
Yoooo 表示されたね。でも、ここで気付くべきポイントは テーブル ビジュアルの各行でも メジャーの評価結果はテーブルじゃないの？ってこと。これは暗黙の変換によるもので、1行1列のテーブルはスカラ値としても評価されているのです。サンプルでよくみるコードで、
IF( HASONEVALUE( ColumnName ), VALUES( ColumnName ) )
というのがあって、きちんとチェックすることで不測の問題を避けようとしていることがあるのです。SELECTEDVALUE 関数で記述してもよいですけど。
- VALUES function (DAX) - DAX | Microsoft Docs
- HASONEVALUE function (DAX) - DAX | Microsoft Docs
- SELECTEDVALUE function (DAX) - DAX | Microsoft Docs
なんとかスカラ値に変換してきちんと確認する
評価結果をスカラ値にしてきちんと確認することが大事だ。デバッグにも使えるし。
メジャー =
VAR CurrentValue = [受注額]
VAR CumulatedValue =
VALUES( Products[区分名] )
RETURN
CONCATENATEX(
CumulatedValue,
Products[区分名], "|"
)
ね、かんたんでしょ。
CONCATENATEX 関数は、デバッグっぽい使い方ができるので、すぐに使った方がよいですよ。
各行にフィルタが適用されているよね
なので、テーブル ビジュアルの各行で適用されている フィルタを変更しないと累計の計算ができない。
メジャー =
VAR CurrentValue = [受注額]
VAR CumulatedValue =
CALCULATETABLE(
VALUES( Products[区分名] ),
ALL( Products )
)
RETURN
CONCATENATEX(
CumulatedValue,
Products[区分名], "|"
)
テーブル ビジュアルの各行ですべての Products[区分名] 列の値を参照できるようになった。これで累計の計算ができそうである。
- CALCULATE function (DAX) - DAX | Microsoft Docs
- CALCULATETABLE function (DAX) - DAX | Microsoft Docs
すこし寄り道 - VAR の評価って
VAR(Variable) に格納されている値はすでに評価済みの値なので イミュータブル と考えるとよいです。
メジャー =
VAR CurrentValue = [受注額]
VAR CumulatedValue =
VALUES( Products[区分名] )
RETURN
CALCULATE(
CONCATENATEX(
CumulatedValue,
Products[区分名], "|"
),
ALL( Products )
)
独自のフィルタを適用
自動的に適用されるフィルタを除外したので、累積を集計するためのフィルタを適用してみる。
メジャー =
VAR CurrentValue = [受注額]
VAR CumulatedValue =
CALCULATETABLE(
FILTER(
VALUES( Products[区分名] ),
[受注額] >= CurrentValue
),
ALL( Products )
)
RETURN
CONCATENATEX(
CumulatedValue,
Products[区分名], "|"
)
独自のフィルタを適用 これじゃだめなの？
質問 : CALCULATETABLE 関数使わずに ALL 関数 つかったらだめなのか？例えばこんな感じ。
メジャー =
VAR CurrentValue = [受注額]
VAR CumulatedValue =
FILTER(
ALL( Products[区分名] ),
[受注額] >= CurrentValue
)
// CALCULATETABLE(
// FILTER(
// VALUES( Products[区分名] ),
// [受注額] >= CurrentValue
// ),
// ALL( Products )
// )
RETURN
CONCATENATEX(
CumulatedValue,
Products[区分名], "|"
)
答え：このデータモデルでは期待する結果を得ることができない。Products[区分名] 列は Products[区分ID] 列で 並び替えを定義しているから。"列で並び替え"すると期待する集計ができないのはなぜ？ - Qiita
できれば、"列で並び替え"をしてようがしていまいが期待する集計結果が得られるといいなぁと思うのです。
[受注額]の累計を計算
ただし、[受注額]の降順で。
メジャー =
VAR CurrentValue = [受注額]
VAR CumulatedValue =
CALCULATE(
CALCULATE(
[受注額],
FILTER(
VALUES( Products[区分名] ),
[受注額] >= CurrentValue
)
),
ALL( Products )
)
RETURN
CumulatedValue
できました。
ね、かんたんでしょ。
[受注額]の累計を計算 ロジックを見直す
今すぐにやらなくてもよいのだけれども、あまり快適な集計ロジックになっていないのではなかろうかと考えてみる。
累積を集計するためのフィルタ で [受注額] を評価して、そのフィルタを使用して再び [受注額] を評価しているのではないか？
メジャー =
VAR CurrentValue = [受注額]
VAR CumulatedValue =
CALCULATE(
SUMX(
FILTER(
ADDCOLUMNS(
VALUES( Products[区分名] ),
"@Value", [受注額]
),
[@Value] >= CurrentValue
),
[@Value]
),
ALL( Products )
)
RETURN
CumulatedValue
20~30 %ほどパフォーマンスよくなるです。
ちょっと寄り道 - なぜ ADDCOLUMNS なのか
SUMMARIZE 関数が使用されているサンプルとかよく見かけるのだけど、これは注意が必要だ。
// DAX Query
EVALUATE
ADDCOLUMNS(
VALUES( Products[区分名] ),
"@Value", [受注額]
)
-----------
区分名 @Value
"飲料" 4792750
"調味料" 4250400
"菓子類" 2819500
"乳製品" 2374100
"穀類/シリアル" 3529280
"肉類" 3449900
"加工食品" 2238700
"魚介類" 5727600
// DAX Query
EVALUATE
SUMMARIZE(
Products,
Products[区分名],
"@Value", [受注額]
)
-----------
区分名 @Value
"飲料" 4792750
"調味料" 4250400
"菓子類" 2819500
"乳製品" 2374100
"穀類/シリアル" 3529280
"肉類" 3449900
"加工食品" 2238700
"魚介類" 5727600
記述は異なるけれども、ADDCOLUMNS 関数 と SUMMARIZE 関数はおなじ結果を返す。ただし、このデータモデルでは。SUMMARIZE 関数で計算列を追加した結果を得るとき関数独自の評価コンテキストが機能するので、想像していないかった結果を返すことがある。なので、
メジャーを定義する記述のなかでは、計算列を追加する SUMMARIZE 関数を使用しないことが望ましい。 デバッグの難度が上がるだけなので、さっさとやめちゃったほうがよい。
全体の[受注額]を計算
メジャー =
VAR CurrentValue = [受注額]
VAR CumulatedValue =
CALCULATE(
SUMX(
FILTER(
ADDCOLUMNS(
VALUES( Products[区分名] ),
"@Value", [受注額]
),
[@Value] >= CurrentValue
),
[@Value]
),
ALL( Products )
)
VAR TotalValue = CALCULATE( [受注額], ALL( Products ) )
RETURN
TotalValue
ね、かんたんでしょ。
それぞれの構成比を計算
メジャー =
VAR CurrentValue = [受注額]
VAR CumulatedValue =
CALCULATE(
SUMX(
FILTER(
ADDCOLUMNS(
VALUES( Products[区分名] ),
"@Value", [受注額]
),
[@Value] >= CurrentValue
),
[@Value]
),
ALL( Products )
)
VAR TotalValue = CALCULATE( [受注額], ALL( Products ) )
VAR Result = DIVIDE( CumulatedValue, TotalValue )
RETURN
Result
Excel でロジックを検討したときと同じ結果になっているので、きっと目的の結果を得られているのでしょう。ね、かんたんでしょ
最後に調整して終了
- 区分名ごとに集計を試していたから、集計粒度を商品ごとに変更
- スライサによる絞り込みなど対応するように CALCULATE 修飾子を調整
- [受注額]が同額となるとき[商品ID]順に
Pareto % (商品) =
VAR CurrentValue = [受注額]
VAR CurrentItem = SELECTEDVALUE( Products[商品ID] )
VAR CumulatedValue =
CALCULATE(
SUMX(
FILTER(
ADDCOLUMNS(
VALUES( Products[商品ID] ),
"@Value", [受注額]
),
[@Value] > CurrentValue ||
( [@Value] = CurrentValue && Products[商品ID] <= CurrentItem )
),
[@Value]
),
ALLSELECTED( Products )
)
VAR TotalValue = CALCULATE( [受注額], ALLSELECTED( Products ) )
VAR Result = DIVIDE( CumulatedValue, TotalValue )
RETURN
Result
ね、かんたんだったでしょ。
お色直しは、Power BI パレート図・ABC分析 - Qiita / @hrkasno を読んだらいい感じになります。
思ったこと🙄
Excel ワークシートで集計する様を思い浮かべると DAX の記述ができないのでしょう。まぁ、頑張ってください。
で、何がむずかしいのか、いまだにわからないままでもある。5～6年前の自分にそう伝えたい。