More than 3 years have passed since last update.

最後の Power BI Desktop の学び 101 Apocalypse #PBIJP

PowerBI

Posted at 2020-07-24

先般のイベント続編として Power BI 勉強会 #18 でお話したこと再生しつつ思うところを追加するなど。これから勉強するとか勉強始めた人を想定して、これだけは守った方がよいよという内容にしていたのが前回。そこで取り上げきれなかったメジャーってどうやって勉強すりゃいんだって話をしたわけである。Apocalypse に意味はなかった。

そもそもメジャーってなんなんだぜ

集計の定義ですよ
以前にポストしたことはあるのだけど、集計の定義 なのである。
メジャーを定義することなくPower BI Desktop や Power BI Service でレポートを作成することはもちろん可能ではあるけれども、メジャーが使われていないレポートって存在しないんですよね。しいて言えば、レポートのキャンバスにビジュアルが配置されていないレポートだけがメジャーを使用していないレポートくらいでしょうか。それって意味がないけれども。
特段の定義することなくても、データを集計しビジュアルに含めて投影することはできるけれども、これは暗黙的に定義されたメジャーが利用されているだけ。これは単純なものにのみ対応なので、明示的にもしくは集計の要件にあわせてメジャーを定義しなければならないのである。

メジャーとはなんだろうと考える - Qiita

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 関数で記述してもよいですけど。

なんとかスカラ値に変換してきちんと確認する
評価結果をスカラ値にしてきちんと確認することが大事だ。デバッグにも使えるし。

勉強中

メジャー = 
VAR CurrentValue = [受注額]
VAR CumulatedValue =
    VALUES( Products[区分名] )
RETURN
    CONCATENATEX(
        CumulatedValue,
        Products[区分名], "|"
    )

ね、かんたんでしょ。
CONCATENATEX 関数は、デバッグっぽい使い方ができるので、すぐに使った方がよいですよ。

CONCATENATEX function (DAX) - DAX | Microsoft Docs

各行にフィルタが適用されているよね
なので、テーブルビジュアルの各行で適用されているフィルタを変更しないと累計の計算ができない。

勉強中

メジャー = 
VAR CurrentValue = [受注額]
VAR CumulatedValue =
    CALCULATETABLE(
        VALUES( Products[区分名] ),
        ALL( Products )
    )
RETURN
    CONCATENATEX(
        CumulatedValue,
        Products[区分名], "|"
    )

テーブルビジュアルの各行ですべての Products[区分名] 列の値を参照できるようになった。これで累計の計算ができそうである。

すこし寄り道 - 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[区分名], "|"
    )

FILTER function (DAX) - DAX | Microsoft Docs

独自のフィルタを適用これじゃだめなの？
質問 : 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 でロジックを検討したときと同じ結果になっているので、きっと目的の結果を得られているのでしょう。ね、かんたんでしょ

DIVIDE function (DAX) - DAX | Microsoft Docs

最後に調整して終了

区分名ごとに集計を試していたから、集計粒度を商品ごとに変更
スライサによる絞り込みなど対応するように 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年前の自分にそう伝えたい。

その他

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up