社内ファイルサーバーをPower BIで確認する話をTwitterで呟いた結果、どう作ったかを書く事にしました。

後編はDAXのRANKX関数と、その他気になった点について書いていきます。
かなりややこしい上、筆者も完全には理解出来ていません。

前編のおさらい

ファイルサイズのランキングを作ったらマトリックスで正常に動作しない。

容量ランキング

= RANKX(ALL('取り込んだファイルサーバーテーブル'),SUM([Size]))

このDAX式は、視覚化でテーブルを選んだ場合は正しく動作します。

しかしマトリックスの場合は正しく動作しません。

RANKXのお話を交えながら解決策を探っていきましょう。

ファイルサイズ等のランキングを作成するRANKX関数の挙動

RANKXについて

RANKXの仕様については以下のサイトが詳しいです。
RANKX - DAX Guide

まず、RANKXの構文は以下になります。

RANKXの構文

RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])

主要な部分だけ解説します。

table
- 評価に使うテーブル
expression
- テーブルを評価する式

これを先ほど上手く動作しなかったDAX式に当てはめると以下の通りです。

容量ランキング

= RANKX(ALL('取り込んだファイルサーバーテーブル'),SUM([Size]))

table
- 取り込んだファイルサーバーテーブル全て
expression
- Size列の合計

つまりこのDAX式は、ファイルサーバーテーブルにおけるSize列の順位を返す式になります。
これらを踏まえた上で、もう一度マトリックス上での動作を見てみましょう。

動作の検証

問題のおさらい

まず、これが件のマトリックスになります。
このマトリックスはフォルダ毎にランク付けする事を意図しています。
ファイルサイズはGBに変換しているため、同じサイズで順位が異なるのは表示されていない桁で差があるためです。

ところが見てわかる通り、以下のDAX式を使った容量ランキング列の動作がおかしいです。

容量ランキング

= RANKX(ALL('取り込んだファイルサーバーテーブル'),SUM([Size]))

本来であれば右端の容量ランキング完全版のように動作させたいのですが、何が駄目だったのでしょうか？

DAX式の挙動

先ほども説明したとおり、以下のDAX式はファイルサーバーテーブルにおけるSize列の順位を返す式になります。
容量ランキング = RANKX(ALL('取り込んだファイルサーバーテーブル'),SUM([Size]))
このDAX式は、テーブルでは正しくファイルサイズの順位を返しましたが、マトリックスでは返しませんでした。

本当にそうでしょうか?

テーブルとマトリックスを並べて見てみましょう。
何か気づきませんか?

テーブル	マトリックス
全ファイルのランキング	特定フォルダのランキング

テーブルのファイルサイズは8位が0.19GB、9位は0.16GB。
それに対してマトリックスのフォルダグループ"E"は9位で0.18GBです。

テーブル	マトリックス
全ファイルのランキング	特定フォルダのランキング

更によく見ると、マトリックスのフォルダグループ"F"と"G"がファイルサイズが、テーブル側で見ても其々15位と22位に当てはまります。
そして、テーブル側1位のファイルサイズ1.45GBを上回ったマトリックス側フォルダグループは全て1位です。

つまりマトリックスでの順位は、全ファイルのサイズ順位を基準として当てはめられた、フォルダサイズの順位である事が分かります。
表にするとこんな感じですね。

改めてRANKXのDAX式を見てみましょう。
RANKX(評価に使うテーブル,テーブルを評価する式)
そして現在のDAX式がこれ。
RANKX(ALL('取り込んだファイルサーバーテーブル'),SUM([Size]))

評価に使うテーブルがALL('取り込んだファイルサーバーテーブル')、つまり**「取り込んだファイルサーバーテーブル全体」**です。
そのため視覚化がテーブルでもマトリックスでも、評価に使うテーブルがどちらも同じ「取り込んだファイルサーバーテーブル全体」になってしまい、正しい結果が得られませんでした。

これでRANKXの挙動が大体分かってきたと思います。

因みにDAX式を以下のように変えるとこうなります。

容量ランキング変更後

= RANKX('取り込んだファイルサーバーテーブル',SUM([Size]))

テーブル	マトリックス
全ファイルのランキング	特定フォルダのランキング

評価に使うテーブルがALL('取り込んだファイルサーバーテーブル')、つまり取り込んだファイルサーバーテーブル全体ではなくなりました。
結果、一行ごとのファイルサイズしか評価されず、ランキングが全て1位になっています。

何故こうなるかについては、Power BI勉強会のセッション資料で詳しく解説されたものがありますので、そちらをご一読ください。
作成者の小室さんに感謝！
Power BI 初心者さんのDAX・メジャー「モヤモヤ」晴れるまで

対策

では、どうすればフォルダ毎にランク付けする事ができるのでしょうか？

先ほどの問題は視覚化の種類にかかわらず、評価に使うテーブルがテーブル全体から変わらなかった為に起きました。
RANKX(評価に使うテーブル,テーブルを評価する式)

ならば、評価に使うテーブルを視覚化に合わせて変えてしまえばいいのです。

結果がこれです。

容量ランキング完成版

RANKX(FILTER(ALL('取り込んだファイルサーバーテーブル'[フォルダグループ]),SUM([Size])),SUM([Size]))

このDAX式は以下の記事を参考に書きました。
DAX RANKX function – Part II

評価に使うテーブルをALL('取り込んだファイルサーバーテーブルﾞ'[フォルダグループ])とする事で、フォルダグループのフィルターコンテキストを外してフォルダグループ毎の集計を出来るようにしています。

細かい解説を書いていくと何時まで経っても記事が完成しそうにないので、今回は割愛させてください。

因みに上記の記事を参考にマトリックスのドリルダウンに対応すると、以下のような式になります。
SUM([Size])は[ファイルサイズ]というメジャーにしてあります。

容量ランキング完成版

容量ランキング完成版 = 
SWITCH(
    COUNTROWS(
        '取り込んだファイルサーバーテーブル'
    ),
    CALCULATE(
        COUNTROWS(
            '取り込んだファイルサーバーテーブル'
        ),
        ALL(
            '取り込んだファイルサーバーテーブル'
        ),
        VALUES(
            '取り込んだファイルサーバーテーブル'[フォルダグループ]
        )
    ),
    RANKX(
        FILTER(
            ALL(
                '取り込んだファイルサーバーテーブル'[フォルダグループ]
            ),
            [ファイルサイズ]
        ),
        [ファイルサイズ]
    ),
    CALCULATE(
        COUNTROWS(
            '取り込んだファイルサーバーテーブル'
        ),
        ALL(
            '取り込んだファイルサーバーテーブル'
        ),
        VALUES(
            '取り込んだファイルサーバーテーブル'[Folder Path]
        )
    ),
    RANKX(
        FILTER(
            ALL(
                '取り込んだファイルサーバーテーブル'[Folder Path]
            ),
            [ファイルサイズ]
        ),
        [ファイルサイズ]
    ),
    RANKX(
        FILTER(
            ALL(
                '取り込んだファイルサーバーテーブル'
            ),
            [ファイルサイズ]
        ),
        [ファイルサイズ]
    )
)

このDAX式だと、フォルダをFolder Pathでドリルダウンしても、容量ランキングがちゃんとフォルダ内のサイズ順位に切り替わっています。

実はこれでも不完全なので、テーブルでランキングを使う際はこっちのDAX式を使ってください。

容量ランキング

= RANKX(ALL('取り込んだファイルサーバーテーブル'),SUM([Size]))

~~誰だ完成版なんて名前付けた奴は~~
その、本当に申し訳ない…_(:3」∠)_

RANKX以外の気になった諸問題

個別のファイルを識別する一意の値が存在しないとどうなるか

泣きます。

冗談はさておき、以下はName列をマトリックスの行に、インデックス列をカウントに設定して値に放り込んだ物です。
見ての通り、ファイル名が重複しています。

ファイルサーバーはフォルダー階層が異なれば、同じファイル名が許されます。
そのため、インデックス列を追加して一意の値を作らないと同名ファイルの区別が付かず、正しく分析出来なくなります。

テーブルの更新がファイルサーバーの性能に左右される

データの更新にかかる時間はファイルサーバーの性能にもよると思いますが、凡そ40万ファイルで約30分掛かっています。
Power BI Serviceでデータの更新をした方が負担にならないでしょう。

例えファイルサーバーがオンプレだったとしても、オンプレミスゲートウェイを使えばPower BI Serviceから接続できます。
オンプレミスデータゲートウェイ

まとめ

RANKXは集計したい項目に合わせて評価に使うテーブルを設定しよう
- ALL('テーブル名'[集計したい列名])
- ALL関数の挙動を覚えるのに丁度いいです。
ファイルサイズのキロ、メガ、ギガ、テラ換算は用意しておこう
- これが無いと本当に桁数が大きくなる上、正確なファイルサイズの把握が出来なくなります。
フォルダパスは適切に分割しよう
- 分割しなくても問題ありませんが、特定のフォルダ階層以下を分析する場合は必須です。
インデックス列は必ず用意しよう
- ファイルという最小単位で分析するには必須です。
- 「無ければ作る」を出来るのがPower QueryとPower BIの良い所。

RANKXの事を調べて検証してたら延々完成しそうになかったので、現段階で一旦公開します。
分からないからこそ、これからも勉強勉強。(´・ω・｀)