"列で並び替え"すると期待する集計ができないのはなぜ？

7909 ワード
PowerBI DAX PowerBI テキストリンク

DAX で記述された メジャー / Measure がどのように評価され集計した結果が得られるのか、まずはこれを理解しないといけないと思うのです。DAX を勉強したいとか勉強しているなら特にかな。Excel の ワークシート関数なら関数など入力してタンってすれば結果は表示されるけれども、Power BI や Power Pivot for Excel では メジャーをカチャカチャタンっしても集計されてないから原因がわかりにくいということはあるけれども。

そもそも、どういうことなのか

DAX 関数の使い方などひとまず覚えてメジャーを書いてみた。エラーにならないんだけど結果が期待するものではないってことはよくあること。原因としては メジャーの記述自体に問題があることなんだけど、もうひとつ理解しておくべきことがある。それは定義したメジャーがどのように評価されて結果を導いているのかということ。
で、例として挙げるのはよくある出来事で "列で並び替え" を設定すると 全部 1 とか すべて同じ値になってしまうんですよって話。

列で並び替え とは

以前にポストしてます。
列や見出しの並び替え順序を指定するには - Qiita
ざっくりいうと、この指定がない場合 見出しなどは 照合順序 に従う。

なにがおこるのか

ALL によるフィルタの除外とか。
さほど説明は不要でしょうがこんな感じのデータモデルになってまして、

取引数
取引数 = COUNTROWS ( 'Sales' )
取引数_(全体比)
取引数 (全体比) = DIVIDE ( [取引数], CALCULATE( [取引数], ALL ( Products[区分名] ) ) )
取引数_(Rank)
取引数 (Rank) = IF( ISINSCOPE ( Products[区分名] ), RANKX ( ALL ( 'Products'[区分名] ) , [取引数] ) )

こんな感じになるわけです。

ここで、列で並び替え を設定すると、

こうなると。

まずは理由を確かめることが大切

定義したメジャーがレポート上のビジュアルに表示されるまでのプロセスもわかっているとよいかと。
自分で確かめることがとても大事。理解だけじゃなく発見できることもありますから。

メジャーは集計する定義であって結果を持っていない
メジャーを定義を終えた時点では評価(集計や計算)されていない。評価されるときとは、ビジュアルにメジャーを配置したときはじめて評価され、スライサやソート、再表示など常に再評価されると考えておいた方がよい。

ビジュアルごとに DAX クエリ
もうひとつ大切なことはビジュアルに表示される情報はどのように得ているかで、Power BI ではレポートのページ上に配置した ビジュアルごとに DAX クエリを発行しているのです。

この DAX クエリが読み解くまでに至らなくても確認してみるということが理解と問題解決にとても役立つ。

パフォーマンス アナライザーを使う

Power BI Desktop の機能になったのでだいぶ楽になった部分が多いのだけど、単に経過時間だけを見るのではなくどのような DAX クエリが利用されているのか簡単に観察できるようになっているのでとてもよい機能だと思う。記録を開始してビジュアルを更新するだけ。クエリのコピーをポチっとするとクリップボードにコピーされるので、DAX Studio が一番都合がよいはずだけれども、お好みのエディターで眺めるなどすればよい。

列で並び替えを設定したときのDAXクエリ
// 少々情報が多くなるので テーブル ビジュアルの合計は非表示とした
// DAX Query
EVALUATE
  TOPN(
    501,
    SUMMARIZECOLUMNS(
      'Products'[区分名],
      'Products'[区分ID],
      "取引数", 'Sales'[取引数],
      "取引数__全体比_", 'Sales'[取引数 (全体比)]
    ),
    'Products'[区分ID],
    1,
    'Products'[区分名],
    1
  )

ORDER BY
  'Products'[区分ID], 'Products'[区分名]

この DAX クエリは テーブル ビジュアルで利用されるパターン。マトリクス ビジュアルでも同じはずだけど、ビジュアルごとに違いはある、だって必要な情報や表示のために得たい結果はビジュアルそれぞれだから。

列で並び替えで利用した列が自動的に含まれる
ざっくり眺めるだけでも、テーブルビジュアルで使用していない列 'Products'[区分ID] が DAX クエリで評価に利用されていることがすぐにわかる。この列は 列で並び替え するために設定した列であって、自動的に準備されるものなんですね。まぁ、この列がないと見出しなどの表示順を制御することはできないですから。

ゆえに自動的に含まれる列によりフィルタの解除が充分に行われない
SUMMARIZECOLUMNS はテーブルを返すので、Power BI Desktop で テーブルとして評価してみるとこんな感じに。

テーブル ビジュアルとしてみたとき、行見出しに 'Products'[区分ID] を使用しているような状態。テーブルの各セルの値としてメジャーが評価されるとき、適用されているフィルターは 'Products'[区分名] だけではなく、'Products'[区分ID] も。なので、ALL ( Products[区分名] ) ) でフィルタを解除していても結果として期待する集計ができないのである。DIVIDE でいうといずれの引数同じ値だから結果は 1 になる。

具体的な対応とは

DAX クエリを見ていればとても簡単で、'Products'[区分ID] に適用されているフィルタも解除するだけのこと。

取引数_(全体比)
取引数 (全体比) =
    DIVIDE (
        [取引数],
        CALCULATE(
            [取引数],
            ALL ( Products[区分名], 'Products'[区分ID] )  // ここ
        )
    )

ALL 関数は テーブルもしくは列を返す関数ではあるけれども、CALCULATE 関数のフィルタ引数として使用する場合は対象のフィルタを解除する役割と考えてよい。ただ、ALL 関数でどのような結果が得られるのかは理解しておいた方がよいはず。

ALL ( <table> )
対象テーブルすべての列に適用されるフィルタを解除する。テーブルとして評価したとき、すべての行と列を取得する
ALL ( <column> [, <column> [, …]])
対象の列に適用されているフィルタを解除する。対象の列はすべて同じテーブルであることが必要。テーブルとして評価したとき、存在する列の組合せでユニーク。
ALL ( )
すべてのフィルタを解除。テーブルとして評価することはできない。

フィルタを解除する列が多いときなどは ALLEXCEPT 関数を使うと便利。

学ぶためのヒントは存在している

得たい集計のためにフィルタを解除するメジャーを定義したのだけど、例に挙げた集計は明示的に定義しなくても求めることはできる。Excel ピボットテーブル でいうところの 計算の種類。
ここでは、再び配置したメジャーの "値の表示方法" で "総計のパーセント"に変更。

これはなかなかよくできていて、列で並び替えを設定してもしていなくても正しい結果が得られるのです。このとき発行されている DAX クエリは、

列で並び替えを設定したときのDAXクエリ
// 少々情報が多くなるので テーブル ビジュアルの合計は非表示とした
// DAX Query
EVALUATE
  TOPN(
    501,
    SUMMARIZECOLUMNS(
      'Products'[区分名],
      'Products'[区分ID],
      "取引数", 'Sales'[取引数],
      "取引数__全体比_", 'Sales'[取引数 (全体比)],
      "M2", DIVIDE('Sales'[取引数], CALCULATE('Sales'[取引数], ALLSELECTED('Products'[区分名]), ALLSELECTED('Products'[区分ID])))
    ),
    'Products'[区分ID],
    1,
    'Products'[区分名],
    1
  )

ORDER BY
  'Products'[区分ID], 'Products'[区分名]
メジャー部分を抽出
DIVIDE (
    'Sales'[取引数],
    CALCULATE (
        'Sales'[取引数],
        ALLSELECTED ( 'Products'[区分名] ),
        ALLSELECTED ( 'Products'[区分ID] )  // ここ
    )
)

ALLSELECTED ( 'Products'[区分ID] ) で定義されるフィルタの解除が追加されているので期待する結果がえられるという寸法。列で並び替え の設定により、DAX クエリ全体とこの部分も自動的に更新されるのですよね。よくできてます。で、 ALLSELECTED 関数が利用されているから該当ビジュアル以外によるフィルタも適用しても期待する結果が得られるわけです。例にしたレポートでは ALL でも ALLSELECTED でも同じ結果。

思ったこと🙄

列で並び替え という機能を知らなかったってのも多いかな。で、設定したら残念な結果になるということでしょうか。
もっと勉強しようよ。

その他