メジャーがうまく書けないの？ぇ？なんで？Power BI 勉強会 #19 で話したこと #PBIJP

なんでそんな難しいところから勉強始めてるの？そりゃ難しいって思っちゃうよね。先般 11/7 Power BI 勉強会 #19 というコミュニティ勉強会を開催しまして、オラオラ成分タイトルでお話したことを整理しなおしておこうかと。

12時くらいをお知らせしました⏰ https://t.co/OZASeP0Yn6 #PBIJP

— Takeshi Kagata / Power Platform 部 Power BI 科 🛏💤 (@PowerBIxyz) November 7, 2020

なぜ

とてもモヤっとするのです。なんで メジャー の記述ができないの？なにが難しいの？

問い合わせや質問の内容を見ていると...

問い合わせや質問の内容を見て感じていることは以下の通り。

• 勉強してるんすか？
• 勉強をしているようだけど、何か違ってない？

そもそも勉強していないなと思うものについては、“勉強しなよ💢” で済ませる。勉強しているのはわかった、で、なぜできるようにならないの？と思うことがほんと多いから。

じゃ、どんな勉強してるの？

Excel を使えていればそんなに難しいことはないですよ、と、しばらく発言していたのだけど、どうやらそういうことでもなさそうだなと。どのような勉強をしているのかまで細かく気にしていないけど、最初に学ぶべき ポイントを教わっていない/気付いていないのかもしれないなと思っている。独学であればある程度仕方ないにしても、教えてくれない/教わっていないというはとても残念だし、教えてくれない "先生" といわれる人に出会った人は不幸なんですねと思うしかない。
で、ほとんどの場合、基本的なことを習得する勉強ができていないということになるのだけど、そもそも "基本的なこと" ってなんだ？っていうこと知っている/知らないで大きく差がつくのではないかと思ったわけです。

基本的なこと理解できてる？

一番重要な動作、集計が始まり ビジュアル に投影されるまで仕組みを理解できているか？だと思う。

• 関数の使い方
• 目的の集計するための記述

とかを求める気持ちはわからないではない。だた、これらは基礎的なものではなく、いずれも 応用 なのだ。応用の基本という感じかな。いつかは一番大事なことひっくるめて理解ができるときが訪れるだろうけど、なんでこの タイミング でつらいことしているんだろうと思ったわけ。
たとえば、累計を求める メジャー の記述はこうですよという情報を見たり読んだりしても、なぜ？っていうこと解らないよね。得られた情報が正確なものであるのか、ベストであるかどうかも判断できないよね。真似しただけで身につくものはとても少なく、次の レベル に到達する手がかりとして充分なのだろうか？ゴール は人それぞれではあるけれども、寄せられる質問や情報を眺めている限りでは、

• 間違った結果を集計する メジャー を定義している
• 結果は期待するものではあっても パフォーマンス が悪いことがある
• 使い勝手の悪い メジャー を定義している

基本的なことを理解できていないから、

• 問題を解決できない
• どこに問題があるかわからない
• そもそも問題に気付かない

となるわけ。

まず理解すべきこととはなんなんだ

投影される データ はすべて集計された結果だ

全行を表示したいという人がたまにいるのだけど、必ず集計された結果を投影するのでこれはできない。唯一の代替手段としては、行の識別子、もしくは、同様な列も ビジュアル に追加することだけなのである。ただ、ファクト テーブル に行の識別子が存在することは、パフォーマンス に影響するのでかなり非推奨。行の識別子がどうしても必要という モデル ならやむなくなんだろうけど、まず、ない。
ビジュアル の軸や見出しに使用される列についでは、ユニーク な値セットになっているという感じだし、集計される列については、数値や テキスト など データ型に基づき集計方法を変えることができるという感じだ。

メジャーは集計の定義である

幾度となく発言する機会はあったのだけど、いまでもそれは変わっていない。集計の定義であるから、

• メジャーを記述したら即時で結果を得られるものではない
• メジャーに記述した式が評価されて初めて結果を得ることになる

ビジュアルに投影される データはすべて集計された結果になるが、その集計方法を独自に定義するということが メジャー を定義するということなのだ。

メジャーは フィルタ コンテキスト に基づき計算される

ビジュアルの動作で フィルタ コンテキスト を観察する

自動的に行われるものであるし概念でもあるから、ぼんやり考えるより見て試した方がよい
たとえば、

メジャー

受注数量 = SUM( Sales[数量] )

という メジャー を定義した。 SUM 関数 (DAX) は引数に テーブル の列ひとつを持ち、列に含まれる数値の和を返す。Excel ワークシート 関数にも同名の関数は存在していて、どのような結果を得ることができるのか想像は容易であるから、手始めに使うにはちょうどよいでしょう。引数は テーブル の列のみ受け付けることが可能ということだけ。

カード ビジュアル に記述した メジャー を配置すると Sales テーブル の [数量] 列に含まれる値すべての和が表示される。このとき、どの フィルタ も適用されていないから。カード ビジュアル に配置された メジャー は、フィルタ を含んでいない フィルタ コンテキストで評価されたということ。

カード ビジュアル に対し何らかの フィルタ が適用されたとき メジャー は再評価となるが、評価に必要な フィルタ コンテキスト に フィルタ ( Products[区分名] = "飲料" ) が追加されたと考えるとよい。スライサー など他 ビジュアル の相互作用であったり、ページ / レポート レベル フィルタ により、メジャー が評価されるときの フィルタ コンテキスト が操作される。

また、ビジュアル の機能によりフィルタ コンテキスト に フィルタ が追加されることがある。
テーブル ビジュアルに 列見出しとして Products[区分名] とメジャーを配置した。テーブル ビジュアル に適用されている フィルタ はここでは存在していない。

テーブル ビジュアル 各行では、メジャー は各行見出しに対応する集計結果を投影する。Excel ピボット テーブル を使ったことがあれば、ごく自然の動作と思えるけれども、この ポイント をよく観察するとよい。

”飲料” の行では、メジャー は Products[区分名] = "飲料" という フィルタ が含まれる フィルタ コンテキストで、"魚介類" の行では Products[区分名] = "魚介類" が含まれる フィルタ コンテキスト で評価されているということだ。合計行では Products[区分名] の フィルタ を含まない フィルタ　コンテキスト で メジャー が評価されているのであって、各行縦計をしているわけではない。
ごく自然な動作ではあるけれども、どのように動作しているかを理解しておくべきなのである。

メジャー

FilterDump1 = 
"区分名 = " & 
IF(
    ISFILTERED( Products[区分名] ), 
    CONCATENATEX(
        VALUES( Products[区分名] ),
        Products[区分名], "|"
    ),
    "(すべて)"
)

テーブル ビジュアル に限らず、チャート でも同様で、ビジュアル の用途や目的に合わせて異なる フィルタ コンテキスト で メジャー を評価し ビジュアル に投影しているのだ。マトリクス ビジュアル では行見出し/列見出しに対応する フィルタ が含まれる フィルタ コンテキスト で メジャー を評価しているということだ。

CALCULATE 関数

• メジャー は常に フィルタ コンテキスト に基づいて評価される
• 操作された フィルタ コンテキスト により、メジャー は異なる結果を返す

これらは基本的で絶対的な動作。メジャー を定義し必要な結果を得るためには、自動的に適用される フィルタ コンテキスト を任意に操作すればよいのである。フィルタ コンテキスト に含まれる フィルタ を追加したり上書きしたりすることが必要だから、まず適用されるであろう フィルタ コンテキスト がどのようなものか把握し、どのように フィルタ コンテキスト を操作するかということが メジャー をイイ感じに記述する重要な要素となる。

CALCULATE 関数 (DAX) は、フィルタ コンテキスト を操作できる唯一の関数だ。
CALCULATETABLE 関数 (DAX) ってのもありますけど、これはちょっと置いといて。
他 DAX 関数はたくさんあるけれども、一番利用することが多い関数であるし、集計する仕組み コンセプト の特徴でもある。なので、たくさんの種類の関数を勉強するより CALCULATE 関数をきちんと使えるように勉強したほうがよいのだ。

例えば、
Products[区分名] = "魚介類" を条件とし、メジャー [受注数量] を評価するとき、

メジャー

受注数量 (魚介類) = 
CALCULATE(
   [受注数量],
   Products[区分名] = "魚介類"
)

と、記述するだけ。

この カード ビジュアル に伝搬する フィルタ は存在していないから、メジャー が評価されるときの フィルタ コンテキスト には フィルタ が含まれていない。ただし、CALCULATE 関数によって メジャー [受注数量] が評価される フィルタ コンテキスト には Products[区分名] = "魚介類" という フィルタ が追加された状態になる。では、相反する フィルタ が メジャー の評価に伝搬するときどうなるか。

同じ列に適用される フィルタ は "上書き" されるのだ。異なる列に適用される フィルタ が フィルタ コンテキスト に追加されるときは当然 "上書き"とならないから "追加" という動作になる。

CALCULATE 関数の構文は、

構文

CALCULATE( <expression>[, <filter1> [, <filter2> [, …]]] )

となっていて、複雑な フィルタ コンテキスト の操作をするとき、 <filter> 引数 に多彩な工夫をこらすことになる。ただ、メジャー を勉強し始めたという場面においては難しいところから始めずに、まずは Products[区分名] = "魚介類" のような列に対する フィルタ 表す式を記述するとよい。複数の <filter> 引数が CALCULATE 関数に含まれる場合、<filter> 引数 で定義される条件は "AND (論理積)" となるから、

受注数量 (魚介類 / 2016) = 
CALCULATE(
    [受注数量],
    Products[区分名] = "魚介類",
    Date[Year] = 2016
)

は、2016年魚介類の受注数量となるわけ。"OR (論理和)" の フィルタ が必要な時、たとえば Products[区分名] が "魚介類" または "調味料" とするとき、

エレガントじゃない

受注数量 (魚介類 / 調味料) = 
CALCULATE(
    [受注数量],
    Products[区分名] = "魚介類"
    || Products[区分名] = "調味料"
)

とも記述できるけど、

こっちの方がエレガントでしょ

受注数量 (魚介類 / 調味料) = 
CALCULATE(
    [受注数量],
    Products[区分名] IN { "魚介類", "調味料" }
)

きりがないのでCALCULATE 関数の説明は別の機会にしたいのだけど、大事なことは、

• メジャー は常に フィルタ コンテキスト に基づいて評価される
• フィルタ コンテキスト を操作できる関数は CALCULATE 関数だけ。

なので、メジャー を記述できるようになりたいよというなら、この2点を重点的に、かつ、できるだけすぐに学んだ方がよいですよ。

FILTER 関数じゃなくて CALCULATE 関数

FILTER 関数じゃなくて CALCULATE 関数を学ぶべきなのである。序盤で FILTER 関数を使いこなそうというのはベスト プラクティス ではない。
で、フィルタ コンテキスト による動作や CALCULATE 関数をなぜか理解しておらず、FILTER 関数を結果的に多用していることが多い。その FILTER 関数、適切ですか？ってこと。FILTER 関数は テーブル を操作する超シンプルな関数ゆえ、使い方によっていろんな問題を引き起こす可能性があるのだ。このとき起こりえることも含めて知っておけば強力な関数なんですけどね。
冒頭に列挙したのは、

• 間違った結果を集計する メジャー を定義している
• 結果は期待するものではあっても パフォーマンス が悪いことがある
• 使い勝手の悪い メジャー を定義している

CALCULATE 関数を勉強しなかったらこうなるってことがとても多いのだ。

望ましい記述

受注数量 (魚介類) = 
CALCULATE(
   [受注数量],
   Products[区分名] = "魚介類"
)

このように、CALCULATE 関数を使うべき記述を、

誤った記述

受注数量 (魚介類)誤 = 
SUMX(
   FILTER(
       Sales,
       RELATED( Products[区分名] ) = "魚介類"
   ),
   [受注数量]
)

という、バッド プラクティス が編み出された事案があった。といっても、以前からよくある間違いなんですけど。いつかは、パッとみて問題がわかるようになるけど、学びの序盤では気付かない問題ってあるのです。
Products[区分名] = ”魚介類” という フィルタ 条件をどこかにねじ込む必要があって、ここでは ファクト テーブルである Sales への操作で FILTER 関数を選択した。これはどんどん悪い方向に流れているのだ。

正しい結果を得られない可能性があること
SUMX 関数 (DAX) は イテレータ なので、<table> 引数 のテーブル行ごと、<expression> を評価し算術和を返すわけだから、問題ないんじゃねと思い込んでしまう。だけど、集計される データ の パターン が増えてくるにつれて期待しない集計をしてしまうことがある。

これは勉強した内容に不具合があったということだ。

メジャー

受注数量 = SUM( Sales[数量] )

なので、記述しなおすと、

誤った記述1

受注数量 (魚介類)誤 = 
SUMX(
    FILTER(
        Sales,
        RELATED( Products[区分名] ) = ”魚介類”
    ),
    CALCULATE（ SUM( Sales[数量] ) )
)

はて？<filter> 引数のない CALCULATE 関数って何しているんだろうと。フィルタ コンテキスト を操作する関数ではあるけれども、どのように操作するのかという定義がない。
CALCULATE 関数 は フィルタ コンテキストを操作する ということなんだけど、多くの場合、メジャーが評価されるときの条件を変更するという使われ方をする。この理解は間違いではない。だけど、本来の動作機能としては、CALCULATE 関数は メジャー の評価に利用する フィルタ コンテキストを生成するというのが基本的な動作なのだ。これは長くなるので別の機会ね。
とはいえ、SUMX 関数 <expression> 引数の CALCULATE( SUM( Sales[数量] ) ) は何をしているかというと、

• SUMX 関数は <table> 引数の行ごとに <expression> を評価
• イテレータなので フィルタ コンテキストはなく、行 コンテキストのみ存在
• メジャーの評価には フィルタ コンテキスト が必要なので、CALCULATE 関数は 行 コンテキスト を フィルタ コンテキスト に変換(Context transition)
• CALCULATE 関数には <filter> 引数がないので、フィルタ コンテキストに含まれる フィルタ は変更なく適用

ここでなぜ集計結果が異なってしまうかについては、

• 行 コンテキスト から変換された フィルタ コンテキスト にはすべての列とその値の組合せが フィルタ 条件として含まれる
• ファクト テーブル には行の識別子を用意しないというのが モデリング の ベスト プラクティス だから 全く同一の行が存在することは普通の出来事
• イテレータ は行ごと評価を実施するから、コンテキスト トランジション で得られる フィルタ コンテキスト が 全行で ユニーク であることは保証されない
• 同じ フィルタ コンテキスト で複数回評価された メジャーが存在した

ということ。

評価コンテキストは重要な概念で "式"(メジャーだけじゃないよ)が評価されるとき

• フィルタ コンテキスト / Filter context
• 行 コンテキスト / Row context

というふたつ。この ポスト では フィルタ コンテキスト をイイ感じに使う メジャー の記述を学ぼうぜだから、行 コンテキスト についてはこれ以上述べない。ただ、CALCULATE 関数 の基本動作で 行 コンテキスト に関わる動作が FILTER 関数など イテレータ に大きく関与した結果が ピットフォール となっているので事例としてあげただけ。

結果は期待するものではあっても パフォーマンス が悪いことがある
FILTER 関数は シンプル な動作だから理解されやすいのでしょう。けど、その理解ほんの一端でしかないのだ。FILTER 関数を使うことはとても意義があること、でも、勉強のとっかかりとしてとても難解すぎる。いずれ学ぶことになるけれども、勉強始めた今、取り掛かるものではないのである。

メジャー

受注数量 (魚介類) v1 = 
CALCULATE(
    [受注数量],
    Products[区分名] = "魚介類"
)

メジャー

受注数量 (魚介類) v2 = 
SUMX(
    FILTER(
        Sales,
        RELATED( Products[区分名] ) = "魚介類"
    ),
    Sales[数量]
)

CALCULATE 関数 / FILTER 関数 それぞれで フィルタ 条件を定義し結果を得た。いずれも期待する結果を得ることはできるでしょう。実は パフォーマンス も同じなのである。※この データモデル で同じ フィルタ 条件であれば...

DAX Calculation engine (計算が得意な中の人ね) は ビジュアル を経由して得られた DAX クエリ を解釈し、必要な計算とは？とか テーブル をどんな感じに スキャン するとよいのか？そんな感じのことを考えてくれているのだ。ただ、それにも限界があって複雑な フィルタ 条件が定義されたとき、もうどうにもならないと判断されると効率的な集計を実行できず、やむを得ず パフォーマンス が低下してでも集計結果を返す。ゆえ、FILTER 関数を使用しなければならないときは特段の注意が必要なのである。

FILTER 関数を使用するときの ベスト プラクティスはそんなに複雑なものではない。

構文

FILTER( <table>, <filter> )

< table > 引数には テーブル そのままを指定するのではなく、列を指定したほうがよい。なので、FILTER 関数を用いた メジャー [受注数量 (魚介類) v2] は ベスト プラクティスとはなりえず、FILTER 関数の効果や機能を説明する目的の記述でしかないのだ。

もうひとつ、パフォーマンスが 顕著に低下するよくある パターン として、累計を取り上げる。

メジャー

RT v1 = 
VAR MaxDate = MAX( 'Date'[Date] ) 
RETURN
CALCULATE(
    SUMX(
        Sales,
        Sales[数量] * Sales[単価]
    ),
    'Dates'[Date] <= MaxDate
)

メジャー

RT v2 = 
SUMX(
    FILTER(
        ALL( Sales ),
        Sales[受注日] <= MAX( 'Dates'[Date] )
    ),
    Sales[単価] * Sales[数量]
)

いずれも正しい結果を得ることはできる。が、

いったいなんなんだっ💢ていうくらい パフォーマンスが低下する。

なぜか？ということは簡単で、FILTER 関数のよろしくない使い方により DAX Calculation engine が 素早く集計を終えるための動作をすべて否定しているから。もう少し詳しくは、以前にポストした。

• FILTER 関数なの？ CALCULATE 関数 なの？って話

使い勝手の悪い メジャー を定義している
かたくなに CALCULATE 関数 を勉強したくないという人には、使い勝手わるいんじゃない？っていう話。
前段で定義した メジャー を使った チャート に凡例を追加した。見ての通りだ。

ビジュアルへ投影されるすべての集計は フィルタ コンテキスト が効果的に使われるところ、不注意な FILTER 関数で フィルタ コンテキスト 含まれるすべてのフ ィルタ 条件を除外してしまっているから。
これどうするよ？ってことです。凡例とか追加される フィルタ 条件を踏まえた メジャー を勉強するんですか？FILTER 関数で利用すべきではない複雑な フィルタ 条件とは？ということを学ぶことより、

• フィルタ コンテキスト という概念
• CALCULATE 関数

を学んでおけばそもそも苦労することはないのだ。

思ったこと🙄

• 関数の使い方
• 目的の集計するための記述

という勉強スタイルは 基礎じゃなくて応用なのである。なので、勉強のとっかかりとしてはあまりよろしくないのだ。大事なことなので2回目。

いろんな人が こうしたらいいよとかブログ書いてくれていたり、コンテンツ を配信してくれていることはとても良いことなんだけど、ベスト プラクティスではない内容もよく見かけるのです。受け手側の勉強する人がそれでいいのか、必要としている内容にマッチしているのかを判断しなければならないから、基本的で難しくないところから勉強したらよいのにねと思った次第。

基本ができて理解してから、応用やチューニングに進みなよ。

その他

• Power BI 勉強会 - connpass
• Power BI Desktop を使い始めるには
• Excel で Power Query を使い始めるには

フィルタコンテキストのフィルタを表示

FilterDump =
CONCATENATEX(
    {
        "Year = "
            & IF(
                ISFILTERED( Dates[Year] ),
                CONCATENATEX( VALUES( Dates[Year] ), Dates[Year], "|" ),
                "すべて"
            ),
        "区分名 = "
            & IF(
                ISFILTERED( Products[区分名] ),
                CONCATENATEX( VALUES( Products[区分名] ), Products[区分名], "|" ),
                "すべて"
            ),
        "所属 = "
            & IF(
                ISFILTERED( Persons[所属] ),
                CONCATENATEX( VALUES( Persons[所属] ), Persons[所属], "|" ),
                "すべて"
            )
    },
    [Value],
    UNICHAR( 13 ) & UNICHAR( 10 ),
    [Value]
)