Power BIでファイルサーバーの利用状況を確認する-後編-

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

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

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

```text:容量ランキング
= RANKX(ALL('取り込んだファイルサーバーテーブル'),SUM([Size]))
```
このDAX式は、視覚化でテーブルを選んだ場合**は**正しく動作します。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/313502/07768b55-df67-d8e1-9aca-da3311551f05.png)
しかし**マトリックスの場合は正しく動作しません。**
![image.png](https://qiita-image-store.s3.amazonaws.com/0/313502/feaf420f-7d83-d5f6-44ab-ce564c509057.png)
RANKXのお話を交えながら解決策を探っていきましょう。
#ファイルサイズ等のランキングを作成するRANKX関数の挙動
##RANKXについて
RANKXの仕様については以下のサイトが詳しいです。
[RANKX - DAX Guide](https://dax.guide/rankx/)

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

```text:RANKXの構文
RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])
```
主要な部分だけ解説します。

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

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

```text:容量ランキング
= RANKX(ALL('取り込んだファイルサーバーテーブル'),SUM([Size]))
```

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

つまりこのDAX式は、**ファイルサーバーテーブルにおけるSize列の順位を返す式**になります。
これらを踏まえた上で、もう一度マトリックス上での動作を見てみましょう。
##動作の検証
###問題のおさらい
まず、これが件のマトリックスになります。
このマトリックスは**フォルダ毎にランク付けする事**を意図しています。
ファイルサイズはGBに変換しているため、同じサイズで順位が異なるのは表示されていない桁で差があるためです。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/313502/0838369a-d8b6-8f34-27d9-c2ebf0c41f69.png)
ところが見てわかる通り、以下のDAX式を使った**容量ランキング列**の動作がおかしいです。

```text:容量ランキング
= RANKX(ALL('取り込んだファイルサーバーテーブル'),SUM([Size]))
```
![image.png](https://qiita-image-store.s3.amazonaws.com/0/313502/79b46de7-eced-a7d5-79c7-f2fac2b7337c.png)
本来であれば右端の**容量ランキング完全版**のように動作させたいのですが、何が駄目だったのでしょうか？
###DAX式の挙動
先ほども説明したとおり、以下のDAX式は**ファイルサーバーテーブルにおけるSize列の順位を返す式**になります。
`容量ランキング = RANKX(ALL('取り込んだファイルサーバーテーブル'),SUM([Size]))`
このDAX式は、テーブルでは正しくファイルサイズの順位を返しましたが、マトリックスでは返しませんでした。

**本当にそうでしょうか?**

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

|テーブル|マトリックス|
|:-:|:-:|
|全ファイルのランキング|特定フォルダのランキング|
|![image.png](https://qiita-image-store.s3.amazonaws.com/0/313502/85f3852a-7094-56b8-3485-515af4b8258d.png)|![image.png](https://qiita-image-store.s3.amazonaws.com/0/313502/0838369a-d8b6-8f34-27d9-c2ebf0c41f69.png)|
テーブルのファイルサイズは**8位が0.19GB**、**9位は0.16GB。**
それに対してマトリックスの**フォルダグループ"E"は9位で0.18GB**です。

|テーブル|マトリックス|
|:-:|:-:|
|全ファイルのランキング|特定フォルダのランキング|
|![image.png](https://qiita-image-store.s3.amazonaws.com/0/313502/8e7e4da5-2078-7499-cc7b-44c6bd284c30.png)|![image.png](https://qiita-image-store.s3.amazonaws.com/0/313502/c7c3f96f-c662-6dd5-43d1-60b23e9fa566.png)|

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

つまりマトリックスでの順位は、**全ファイルのサイズ順位を基準として当てはめられた、フォルダサイズの順位**である事が分かります。
表にするとこんな感じですね。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/313502/549b6146-3cca-0097-aec3-c9670fdd7c73.png)

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

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

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

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

```text:容量ランキング変更後
= RANKX('取り込んだファイルサーバーテーブル',SUM([Size]))
```

|テーブル|マトリックス|
|:-:|:-:|
|全ファイルのランキング|特定フォルダのランキング|
|![image.png](https://qiita-image-store.s3.amazonaws.com/0/313502/5309ea64-66be-1a0f-caaa-c912b7c63bde.png)|![image.png](https://qiita-image-store.s3.amazonaws.com/0/313502/4a436493-d001-7b60-0024-0f2cd5510d34.png)|

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

何故こうなるかについては、Power BI勉強会のセッション資料で詳しく解説されたものがありますので、そちらをご一読ください。
作成者の小室さんに感謝！
[Power BI 初心者さんのDAX・メジャー「モヤモヤ」晴れるまで](https://www.slideshare.net/YokoKomuro/power-bi-dax)
###対策
では、どうすれば**フォルダ毎にランク付け**する事ができるのでしょうか？
![image.png](https://qiita-image-store.s3.amazonaws.com/0/313502/33559f55-8d97-45ae-0205-b9f3c22f0136.png)

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

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

結果がこれです。

```Text:容量ランキング完成版
RANKX(FILTER(ALL('取り込んだファイルサーバーテーブル'[フォルダグループ]),SUM([Size])),SUM([Size]))
```
このDAX式は以下の記事を参考に書きました。
[DAX RANKX function – Part II](https://ayadshammout.com/2014/02/23/dax-rankx-function-part-ii/)

評価に使うテーブルをALL('取り込んだファイルサーバーテーブルﾞ'[フォルダグループ])とする事で、フォルダグループのフィルターコンテキストを外してフォルダグループ毎の集計を出来るようにしています。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/313502/33559f55-8d97-45ae-0205-b9f3c22f0136.png)

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

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

```Text:容量ランキング完成版
容量ランキング完成版 = 
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でドリルダウンしても、容量ランキングがちゃんとフォルダ内のサイズ順位に切り替わっています。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/313502/de638802-cf06-c187-4f12-6d4eb2565a9e.png)

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

```text:容量ランキング
= RANKX(ALL('取り込んだファイルサーバーテーブル'),SUM([Size]))
```
~~誰だ完成版なんて名前付けた奴は~~
その、本当に申し訳ない…\_(:3」∠)_

#RANKX以外の気になった諸問題
##個別のファイルを識別する一意の値が存在しないとどうなるか
**泣きます。**

冗談はさておき、以下は**Name列**をマトリックスの行に、**インデックス列**をカウントに設定して値に放り込んだ物です。
見ての通り、**ファイル名が重複しています。**
![image.png](https://qiita-image-store.s3.amazonaws.com/0/313502/593c831f-7ecd-37d7-873d-0687644a737a.png)
**ファイルサーバーはフォルダー階層が異なれば、同じファイル名が許されます。**
そのため、インデックス列を追加して一意の値を作らないと同名ファイルの区別が付かず、正しく分析出来なくなります。
##テーブルの更新がファイルサーバーの性能に左右される
データの更新にかかる時間はファイルサーバーの性能にもよると思いますが、**凡そ40万ファイルで約30分**掛かっています。
Power BI Serviceでデータの更新をした方が負担にならないでしょう。

例えファイルサーバーがオンプレだったとしても、オンプレミスゲートウェイを使えばPower BI Serviceから接続できます。
[オンプレミス データ ゲートウェイ](https://docs.microsoft.com/ja-jp/power-bi/service-gateway-onprem)

#まとめ

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

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